ST_Read fails on non-UTF-8 Shapefile (macOS only?)

[yutannihilation]

I found ENCODING= GDAL option doesn't work. I don't remember if this used to work on macOS, but at least this works on Windows. So, maybe something is wrong with macOS build?

FROM ST_Read('/path/to/data.shp', open_options = ["ENCODING=CP932"])

The above doesn't fail and silently returns the results like below.

┌────────────┬────────────┬────────────┐
│ A22_000001 │ A22_000002 │ A22_000003 │
│  varchar   │  varchar   │  varchar   │
├────────────┼────────────┼────────────┤
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
│ ????INVA…  │ ????INVA…  │ ????INVA…  │
├────────────┴────────────┴────────────┤
│ 10 rows                    3 columns │
└──────────────────────────────────────┘

Also, COPY TO fails if we specify the option.

COPY (
  SELECT ST_Point(1, 1), 'あああ' as text
) TO 'tmp'
WITH (FORMAT GDAL, DRIVER 'ESRI Shapefile', LAYER_CREATION_OPTIONS 'ENCODING=CP932');

IO Error:
GDAL Error (1): Failed to create field name 'text': cannot convert to CP932

Note that, COPY TO's case isn't serious as we rarely output non-UTF-8 files. However, I really wish ST_Read works because, unfortunately, there are a lot of non-UTF-8 geospatial data.

[yutannihilation]

With DuckDB v1.4.4, ST_Read() fails rather than shows us invalid strings. I hope the stack trace might help.

D FROM ST_Read('path/to/data.shp', open_options = ["ENCODING=CP932"]) LIMIT 10;
INTERNAL Error: invalid code point detected in Utf8Proc::UTF8ToCodepoint, likely due to invalid UTF-8
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/stable/dev/internal_errors

Stack Trace:

0        duckdb::Exception::Exception(duckdb::ExceptionType, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) + 64
1        duckdb::InternalException::InternalException(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) + 20
2        duckdb::Utf8Proc::UTF8ToCodepoint(char const*, int&) + 400
3        duckdb::Utf8Proc::RenderWidth(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) + 48
4        duckdb::BoxRenderer::ComputeRenderWidths(duckdb::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, true> const&, duckdb::vector<duckdb::LogicalType, true> const&, std::__1::list<duckdb::ColumnDataCollection, std::__1::allocator<duckdb::ColumnDataCollection>>&, unsigned long long, unsigned long long, duckdb::vector<unsigned long long, true>&, unsigned long long&) + 1304
5        duckdb::BoxRenderer::Render(duckdb::ClientContext&, duckdb::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>, true> const&, duckdb::ColumnDataCollection const&, duckdb::BaseResultRenderer&) + 1616
6        duckdb_shell_sqlite3_print_duckbox + 1504
7        duckdb_shell::ShellState::ExecutePreparedStatement(sqlite3_stmt*) + 588
8        duckdb_shell::ShellState::ExecuteSQL(char const*, char**) + 388
9        duckdb_shell::ShellState::RunOneSqlLine(duckdb_shell::InputMode, char*) + 256
10       duckdb_shell::ShellState::ProcessInput(duckdb_shell::InputMode) + 952
11       main + 4036
12       start + 6076

[Maxxen]

Hello!

Spontaneously it seems unlikely we will be able to fix this easily. I don't think we can really influence what character conversions are available to GDAL on different platforms.

DuckDB has its own system to register character conversion methods through the auto-loadable encodings extension which the CSV reader uses to be able to parse differently encoded CSV files. We could in theory do something similar but it would be hard to adapt to ST_Read as we feed the gdal-produced arrow arrays directly into DuckDB internals.

I don't know if the text encoding is communicated somehow. Like, as far as I know all arrow strings are required to be UTF-8 and there is no special metadata to distinguish anything else, and any other encoding should emit BINARY/BLOB. So I think this is a GDAL problem?

I think the actual way to solve the underlying problem here is to finish up the built-in shapefile reader (ST_ReadSHP) which we could adapt to use the duckdb-provided character conversion functions (through encodings) to read non-utf8 shapefile data.

[yutannihilation]

Thanks for the details! I just found your comment in the old issue and was wondering if this issue is what can be fixed on DuckDB's side.

#394 (comment)

as far as I know all arrow strings are required to be UTF-8
So I think this is a GDAL problem?

I guess so. Considering this works fine on Windows, GDAL probably attempts to translate if the conversion is available (Windows doesn't rely on iconv), but gives up silently if not. Maybe GDAL should raise an error when it cannot convert the characters to UTF-8 while the output is required to be UTF-8...?

I guess the short term solution is to document your comment properly. I wish I knew this earlier...

So spatial has its own experimental shape file reader, st_readshp where you should be able to pass an extra encoding := 'blob' optional argument which will read any string fields as DuckDB BLOB's which you can then decode() into VARCHAR if they are valid utf8.

[yutannihilation]

Now that my extension, st_read_muti, can read non-UTF-8 Shapefiles, personally this is no longer a serious problem to me.

https://github.com/yutannihilation/duckdb-ext-st-read-multi?tab=readme-ov-file#shapefile

I think this is besically the same issue as #394, so I can close this issue as duplicated.