Lightning: parse bit string exported as Parquet from Aurora error

[buchuitoudegou]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. export table with schema from Aurora:

CREATE TABLE jxtest(
    `id` char(36) NOT NULL,
    `a` bigint unsigned NOT NULL,
    `aa` bigint signed NOT NULL,
    `b` int(11) unsigned NOT NULL,
    `bb` int(11) signed NOT NULL,
    `c` smallint signed NOT NULL,
    `cc` smallint unsigned NOT NULL,
    `d` tinyint signed NOT NULL,
    `dd` tinyint unsigned NOT NULL,
    `e` float unsigned NOT NULL,
    `ee` float signed NOT NULL,
    `f` VARCHAR(30) NOT NULL,
    `ff` TEXT NOT NULL,
    `h` MEDIUMTEXT NOT NULL,
    `hh` LONGTEXT NOT NULL,
    `ii` TINYTEXT NOT NULL,
    `j` DECIMAL NOT NULL,
    `jj` DECIMAL(8,0) NOT NULL,
    `k` DECIMAL(8,8) NOT NULL,
    `kk` DECIMAL(20,0) NOT NULL,
    `l` DECIMAL(20,8) NOT NULL,
    `ll` DECIMAL(36,0) NOT NULL,
    `m` DECIMAL(36,8) NOT NULL,
    `mm` DATE NOT NULL,
    `n` TIME NOT NULL,
    `nn` YEAR NOT NULL,
    `o` DATETIME NOT NULL,
    `oo` BINARY NOT NULL,
    `p` BLOB NOT NULL,
    `pp` LONGBLOB NOT NULL,
    `q` MEDIUMBLOB NOT NULL,
    `qq` TINYBLOB NOT NULL,
    `rr` BIT NOT NULL,
    `s` BOOLEAN NOT NULL,
    `ss` DOUBLE signed NOT NULL,
    `t` DOUBLE unsigned NOT NULL,
    PRIMARY KEY ( `id` ),
    KEY `index_a` (`a`) );

2. import parquet using lightning

2. What did you expect to see? (Required)

All data the same as that in Aurora

3. What did you see instead (Required)

diff:
for binary type,
In Aurora: b'111111111'
In TiDB: 0x31313131313131313131

4. What is your TiDB version? (Required)

TiDB: v6.2.0
Lighting: v6.2.0

[buchuitoudegou]

/component lightning

[buchuitoudegou]

parse parquet using pyarrow:

In TiDB:

mysql> select p,pp,q from jxtest;
+--------------------------------------------------------------------------+------------------+----------------------------------------------------------------+
| p                                                                        | pp               | q                                                              |
+--------------------------------------------------------------------------+------------------+----------------------------------------------------------------+
| 0x313131313131313131                                                     | 0x31313131313131 | 0x313131313131313131                                           |
| 0x3131313131313131313131313131313131313131313131313131313131313131313131 | 0x31313131313131 | 0x313131313131313131313131313131313131313131313131313131313131 |
| 0x30                                                                     | 0x31313131313131 | 0x30                                                           |
+--------------------------------------------------------------------------+------------------+----------------------------------------------------------------+

[buchuitoudegou]

The root cause is: parquet-go parses bit string as plain text string, i.e. b'1111111' => '1111111'

When convert it to string, we will see the result is correct:

mysql> select cast(p as char) from jxtest;
+-------------------------------------+
| cast(p as char)                     |
+-------------------------------------+
| 111111111                           |
| 11111111111111111111111111111111111 |
| 0                                   |
+-------------------------------------+
3 rows in set (0.01 sec)

The output of parquet-go:

schema element: SchemaElement({Type:BYTE_ARRAY TypeLength:<nil> RepetitionType:OPTIONAL Name:P NumChildren:<nil> ConvertedType:<nil> Scale:<nil> Precision:<nil> FieldID:<nil> LogicalType:<nil>}), string: 111111111
schema element: SchemaElement({Type:BYTE_ARRAY TypeLength:<nil> RepetitionType:OPTIONAL Name:Pp NumChildren:<nil> ConvertedType:<nil> Scale:<nil> Precision:<nil> FieldID:<nil> LogicalType:<nil>}), string: 1111111111
schema element: SchemaElement({Type:BYTE_ARRAY TypeLength:<nil> RepetitionType:OPTIONAL Name:Q NumChildren:<nil> ConvertedType:<nil> Scale:<nil> Precision:<nil> FieldID:<nil> LogicalType:<nil>}), string: 111111111

[lance6716]

should we fix it in parquet-go instead?

[buchuitoudegou]

should we fix it in parquet-go instead?

File an issue in parquet-go: xitongsys/parquet-go#496
But I don't have much time helping fixing it in the source repo😭