[Bug]: LOAD DATA parquet fails on optional STRING to DATETIME NULL conversion

[Ariznawlll]

Bug Report

Reproduce steps

Loading a concrete parquet object from COS reaches parquet type conversion, but fails when loading an optional string parquet column into a nullable DATETIME column.

Target table includes:

create table parquet_type_conversion_1y.all_type_parquet_1y (
  row_id bigint,
  c_tinyint tinyint,
  c_smallint smallint,
  c_int int,
  c_bigint bigint,
  c_uint8 tinyint unsigned,
  c_uint16 smallint unsigned,
  c_uint32 int unsigned,
  c_uint64 bigint unsigned,
  c_float32 float,
  c_float64 double,
  c_float_text float,
  c_double_text double,
  c_decimal decimal(38,10),
  c_decimal_sci decimal(38,10),
  c_bool bool,
  c_char char(32),
  c_varchar varchar(255),
  c_text text,
  c_json json,
  c_blob blob,
  c_binary binary(16),
  c_varbinary varbinary(32),
  c_date date,
  c_datetime datetime,
  c_timestamp timestamp,
  c_time time,
  c_uuid uuid,
  c_enum enum('red','green','blue','yellow','purple'),
  c_vecf32 vecf32(3),
  c_vecf64 vecf64(3)
);

Load SQL:

load data url s3option {
  'endpoint'='http://cos.ap-guangzhou.myqcloud.com',
  'access_key_id'='***',
  'secret_access_key'='***',
  'bucket'='mo-load-guangzhou-1308875761',
  'filepath'='mo-big-data/parquet_type_conversion_100M/part-00000.parquet',
  'format'='parquet'
}
into table parquet_type_conversion_1y.all_type_parquet_1y
parallel 'true';

Actual behavior

ERROR 20102 (HY000): load STRING(optional) to DATETIME NULL is not yet implemented

Expected behavior

LOAD DATA ... format='parquet' should support loading valid datetime string values from an optional parquet STRING column into a nullable MO DATETIME column, or the limitation should be documented clearly if this conversion is intentionally unsupported.

Notes

• The same COS endpoint/bucket can load an existing small parquet object successfully.
• Using the concrete object path reaches conversion, so this is not a file-not-found or credential issue.
• This was found while preparing a larger parquet type-conversion regression dataset related to [Feature Request]: Automatic Type Conversions for Parquet File Loading #22748.

please contact me for parquet file

[Ariznawlll]

Additional finding from checking the current parquet type-conversion dataset and the parquet reader mapping code:

The first failure currently exposed is:

STRING(optional) -> DATETIME NULL
ERROR 20102 (HY000): load STRING(optional) to DATETIME NULL is not yet implemented

After this is fixed or bypassed, the same dataset is expected to hit two more unsupported conversions:

STRING(optional) -> VECF32(3) NULL
STRING(optional) -> VECF64(3) NULL

Reason:

• DATE, TIMESTAMP, and TIME already have string conversion paths in the parquet load mapper.
• DATETIME currently only maps parquet logical DATE or logical TIMESTAMP; plain parquet STRING / ByteArray is not handled.
• Vector columns are only mapped from parquet LIST types: LIST<FLOAT> for vecf32 and LIST<DOUBLE> for vecf64. String values like [0.1,0.2,0.3] are not parsed into vector columns.

So for the current generated dataset, the unsupported conversion set appears to be:

STRING -> DATETIME
STRING -> VECF32
STRING -> VECF64

Other columns in the dataset appear to have existing mapper support from static code inspection, including string to integer/unsigned/float/double/decimal/date/time/timestamp/json/uuid/enum and binary to blob/binary/varbinary.

For the regression dataset we can work around this by generating c_datetime as parquet logical timestamp and vector columns as parquet LIST values, but the above STRING conversion gaps should still be tracked here.

[Ariznawlll]

I added a temporary unit-test probe against the parquet load mapper layer (ParquetHandler.getMapper / columnMapper.mapping) to verify additional unsupported conversions, instead of relying only on static code inspection.

The probe constructs in-memory parquet files/pages with parquet-go, then checks whether MO can create a mapper for a given target type and whether mapping into an MO vector succeeds. This does not require starting an MO cluster; it verifies the parquet conversion layer where the current load ... is not yet implemented error is raised.

Verified unsupported at mapper creation time (getMapper / getNestedListMapper returns nil):

STRING -> BOOL
STRING -> BIT
STRING -> DATETIME
STRING -> VECF32
STRING -> VECF64

BOOL -> FLOAT64

FLOAT -> INT64
DOUBLE -> INT64

plain INT32 -> DATE
parquet logical DATE -> TIMESTAMP
parquet logical TIMESTAMP -> DATE

BOOL -> JSON
INT32 -> JSON

LIST<FLOAT> -> VECF64
LIST<DOUBLE> -> VECF32

Verified mapper exists, but mapping fails at runtime:

BOOL -> DECIMAL128
FLOAT -> DECIMAL128
DOUBLE -> DECIMAL128

Also verified correction from the earlier static inspection: STRING -> DECIMAL256 is not in this unsupported list. It creates a mapper and mapping succeeds in the probe.

For the current regression dataset, the directly blocking conversions remain:

STRING -> DATETIME
STRING -> VECF32
STRING -> VECF64

The temporary probe test file was removed after verification; no product code change was made for this check.

[Ariznawlll]

I re-ran this as a broader conversion matrix probe to avoid relying on static inference.

Verification method

Temporary Go probe test in pkg/sql/colexec/external:

• Constructed in-memory parquet pages with parquet-go.
• Called the parquet load conversion layer directly: ParquetHandler.getMapper / getNestedListMapper and columnMapper.mapping.
• Covered these parquet sources:

BOOL
INT32 / INT64
UINT32 logical / UINT64 logical
FLOAT / DOUBLE
BYTE_ARRAY / FIXED_LEN_BYTE_ARRAY / STRING
DATE logical
TIME millis/micros/nanos
TIMESTAMP millis/micros/nanos
DECIMAL stored as INT32 / INT64 / FIXED_LEN_BYTE_ARRAY
LIST<FLOAT> / LIST<DOUBLE>

• Covered these MO target types:

BOOL, BIT, INT8/16/32/64, UINT8/16/32/64, FLOAT32/64,
DATE, TIME, DATETIME, TIMESTAMP,
CHAR, VARCHAR, TEXT, BINARY, VARBINARY, BLOB,
DECIMAL64/128/256, JSON, UUID, ENUM, VECF32, VECF64

I excluded pure value errors from the unsupported list, e.g. 42 -> BIT(1) overflow or invalid fixed-length byte padding. The list below is limited to either no mapper or mapper exists but fails with unsupported parquet physical type.

Verified unsupported conversions

From BOOL

BOOL -> FLOAT32/FLOAT64
BOOL -> DATE/TIME/DATETIME/TIMESTAMP
BOOL -> JSON/UUID/ENUM
BOOL -> VECF32/VECF64
BOOL -> DECIMAL64/DECIMAL128/DECIMAL256  -- mapper exists, runtime fails: unsupported parquet physical type BOOLEAN

From INT32 / INT64 / UINT32 logical / UINT64 logical

INT/UINT -> BOOL
INT/UINT -> DATE/DATETIME/TIMESTAMP
INT/UINT -> JSON/UUID/ENUM
INT/UINT -> VECF32/VECF64

Notes:

INT/UINT -> TIME is supported.
INT/UINT -> BIT has a mapper; it only fails if the value overflows BIT(N).

From FLOAT / DOUBLE

FLOAT/DOUBLE -> BOOL
FLOAT/DOUBLE -> BIT
FLOAT/DOUBLE -> INT8/INT16/INT32/INT64
FLOAT/DOUBLE -> UINT8/UINT16/UINT32/UINT64
FLOAT/DOUBLE -> DATE/TIME/DATETIME/TIMESTAMP
FLOAT/DOUBLE -> JSON/UUID/ENUM
FLOAT/DOUBLE -> VECF32/VECF64
FLOAT/DOUBLE -> DECIMAL64/DECIMAL128/DECIMAL256  -- mapper exists, runtime fails: unsupported parquet physical type FLOAT/DOUBLE

From BYTE_ARRAY / STRING

BYTE_ARRAY/STRING -> BOOL
BYTE_ARRAY/STRING -> BIT
BYTE_ARRAY/STRING -> DATETIME
BYTE_ARRAY/STRING -> VECF32/VECF64

Notes:

BYTE_ARRAY/STRING -> DATE/TIME/TIMESTAMP is supported with valid textual values.
BYTE_ARRAY/STRING -> DECIMAL/JSON/UUID/ENUM is supported with valid textual values.

From parquet logical DATE

DATE logical -> BOOL
DATE logical -> BIT
DATE logical -> INT8/INT16/INT32/INT64
DATE logical -> UINT8/UINT16/UINT32/UINT64
DATE logical -> FLOAT32/FLOAT64
DATE logical -> TIME
DATE logical -> TIMESTAMP
DATE logical -> JSON/UUID/ENUM
DATE logical -> VECF32/VECF64

Notes:

DATE logical -> DATE is supported.
DATE logical -> DATETIME is supported.

From parquet logical TIME millis/micros/nanos

TIME logical -> BOOL
TIME logical -> BIT
TIME logical -> INT8/INT16/INT32/INT64
TIME logical -> UINT8/UINT16/UINT32/UINT64
TIME logical -> FLOAT32/FLOAT64
TIME logical -> DATE
TIME logical -> DATETIME
TIME logical -> TIMESTAMP
TIME logical -> JSON/UUID/ENUM
TIME logical -> VECF32/VECF64

Notes:

TIME logical -> TIME is supported.

From parquet logical TIMESTAMP millis/micros/nanos

TIMESTAMP logical -> BOOL
TIMESTAMP logical -> BIT
TIMESTAMP logical -> INT8/INT16/INT32/INT64
TIMESTAMP logical -> UINT8/UINT16/UINT32/UINT64
TIMESTAMP logical -> FLOAT32/FLOAT64
TIMESTAMP logical -> DATE
TIMESTAMP logical -> TIME
TIMESTAMP logical -> JSON/UUID/ENUM
TIMESTAMP logical -> VECF32/VECF64

Notes:

TIMESTAMP logical -> DATETIME is supported.
TIMESTAMP logical -> TIMESTAMP is supported.

From parquet DECIMAL logical stored as INT32/INT64/FIXED_LEN_BYTE_ARRAY

DECIMAL logical -> BOOL
DECIMAL logical -> BIT
DECIMAL logical -> INT8/INT16/INT32/INT64
DECIMAL logical -> UINT8/UINT16/UINT32/UINT64
DECIMAL logical -> DATE/TIME/DATETIME/TIMESTAMP
DECIMAL logical -> JSON/UUID/ENUM
DECIMAL logical -> VECF32/VECF64

Notes:

DECIMAL logical -> FLOAT32/FLOAT64 is supported.
DECIMAL logical -> DECIMAL64/128/256 is supported.
DECIMAL logical -> CHAR/VARCHAR/TEXT/BINARY/VARBINARY/BLOB is supported.

From parquet LIST

LIST<FLOAT> -> VECF64
LIST<DOUBLE> -> VECF32
LIST<FLOAT/DOUBLE> -> scalar targets are not supported as direct vector-list mapping

Notes:

LIST<FLOAT> -> VECF32 is supported.
LIST<DOUBLE> -> VECF64 is supported.
General nested/group parquet can be mapped through row mode to JSON/TEXT/VARCHAR/CHAR, but not to ordinary scalar numeric/date/vector targets.

Current regression dataset blockers

For the current generated dataset, the directly blocking conversions are still:

STRING -> DATETIME
STRING -> VECF32
STRING -> VECF64

The temporary probe test was removed after verification.

[Ariznawlll]

Verified against fresh local builds of the latest remote branches using a minimal nullable parquet STRING repro.

Repro parquet

/tmp/mo-issue-24914/string_optional_datetime.parquet
schema: c_datetime: optional string
values:
  2024-01-01 12:30:45
  NULL
  2024-12-31 23:59:59.123456

SQL used for each branch

drop database if exists issue24914_verify;
create database issue24914_verify;
use issue24914_verify;
create table t(c_datetime datetime(6) null);
load data infile {'filepath'='/tmp/mo-issue-24914/string_optional_datetime.parquet', 'format'='parquet'} into table t;
select count(*) as row_count, count(c_datetime) as non_null_count from t;
select c_datetime from t order by c_datetime is null, c_datetime;

Results

Branch	Commit	Result
3.0-dev	43e871c1b	PASS: row_count=3, non_null_count=2, NULL preserved
4.0-dev	3757dacf3	PASS: row_count=3, non_null_count=2, NULL preserved
main	001dd8f91	PASS: row_count=3, non_null_count=2, NULL preserved

Observed values

2024-01-01 12:30:45[.000000]
2024-12-31 23:59:59.123456
NULL

Conclusion: the original issue was valid, but the latest 3.0-dev, 4.0-dev, and main branches all now support loading optional parquet STRING values into nullable DATETIME(6) for this repro.