Negative values for UInt32 even though Icerberg table created by ice converts to Int64

[vzakaznikov]

Test case source: https://github.com/Altinity/antalya-hackathon/blob/29b90dbcfb14bece99e2fb179f86a40c367a6c24/run.py

vector :) SHOW TABLE mt_table

SHOW CREATE TABLE mt_table

Query id: 2f4ed043-f025-43d2-a146-4746f78a6472

   ┌─statement─────────────────────────┐
1. │ CREATE TABLE default.mt_table    ↴│
   │↳(                                ↴│
   │↳    `id` UInt32,                 ↴│
   │↳    `name` String                ↴│
   │↳)                                ↴│
   │↳ENGINE = MergeTree               ↴│
   │↳PARTITION BY id                  ↴│
   │↳ORDER BY name                    ↴│
   │↳SETTINGS index_granularity = 8192 │
   └───────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

vector :) SHOW TABLE s3_table

SHOW CREATE TABLE s3_table

Query id: 9c93b86d-e6e2-4150-b7f4-328a14e48e80

   ┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE default.s3_table                                                                                 ↴│
   │↳(                                                                                                             ↴│
   │↳    `id` UInt32,                                                                                              ↴│
   │↳    `name` String                                                                                             ↴│
   │↳)                                                                                                             ↴│
   │↳ENGINE = S3(s3_conn, filename = 'namespace/iceberg_table/data', format = Parquet, partition_strategy = 'hive')↴│
   │↳PARTITION BY id                                                                                               ↴│
   │↳ORDER BY name                                                                                                  │
   └────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

vector :) SHOW TABLE iceberg_db.`namespace.iceberg_table`

SHOW CREATE TABLE iceberg_db.`namespace.iceberg_table`

Query id: 6c9b135b-733c-46c4-b0b9-e73b72b75a65

   ┌─statement─────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE iceberg_db.`namespace.iceberg_table`                                            ↴│
   │↳(                                                                                            ↴│
   │↳    `id` Int64,                                                                              ↴│
   │↳    `name` String                                                                            ↴│
   │↳)                                                                                            ↴│
   │↳ENGINE = Iceberg('http://minio:9000/warehouse/namespace/iceberg_table/', 'minio', '[HIDDEN]') │
   └───────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.013 sec. 

vector :) SELECT * FROM mt_table

SELECT *
FROM mt_table

Query id: 331131a1-e708-4ce2-b7ab-3c6e97245624

   ┌─────────id─┬─name─┐
1. │ 4294967295 │ John │
2. │ 4294967294 │ Jane │
3. │ 4294967293 │ Jim  │
   └────────────┴──────┘

3 rows in set. Elapsed: 0.004 sec. 

vector :) SELECT * FROM s3_table

SELECT *
FROM s3_table

Query id: e5150b8a-2d07-47dd-a608-8c68e442531d

   ┌─────────id─┬─name─┐
1. │ 4294967293 │ Jim  │
2. │ 4294967295 │ John │
3. │ 4294967294 │ Jane │
   └────────────┴──────┘

3 rows in set. Elapsed: 0.012 sec. 

vector :) SELECT * FROM iceberg_db.`namespace.iceberg_table`

SELECT *
FROM iceberg_db.`namespace.iceberg_table`

Query id: 8117f22f-edee-4ee9-a3b4-2ba70f64fdc4

   ┌─id─┬─name─┐
1. │ -2 │ Jane │
2. │ -1 │ John │
3. │ -3 │ Jim  │
   └────┴──────┘

3 rows in set. Elapsed: 0.021 sec. 

vector :) 

[vzakaznikov]

Here is reading directly from the Parquet file:

vector :) SELECT * FROM s3('http://minio:9000/warehouse/namespace/iceberg_table/data/id=4294967293/4294967293_3_3_0_09AEF128AFB31E8097925326DCB44E17.parquet')

SELECT *
FROM s3('http://minio:9000/warehouse/namespace/iceberg_table/data/id=4294967293/4294967293_3_3_0_09AEF128AFB31E8097925326DCB44E17.parquet')

Query id: c6773a46-06fa-46ae-b7c3-2dd22b4ebdf0

   ┌─────────id─┬─name─┐
1. │ 4294967293 │ Jim  │
   └────────────┴──────┘

1 row in set. Elapsed: 0.011 sec. 

vector :) SELECT * FROM s3('http://minio:9000/warehouse/namespace/iceberg_table/data/id=4294967293/4294967293_3_3_0_09AEF128AFB31E8097925326DCB44E17.parquet', ParquetMetadata)

SELECT *
FROM s3('http://minio:9000/warehouse/namespace/iceberg_table/data/id=4294967293/4294967293_3_3_0_09AEF128AFB31E8097925326DCB44E17.parquet', ParquetMetadata)

Query id: 35de099a-3e09-4ec3-95e3-921b7bf2891f

Row 1:
──────
num_columns:             2
num_rows:                1
num_row_groups:          1
format_version:          2
metadata_size:           248
total_uncompressed_size: 107
total_compressed_size:   171
columns:                 [('id','id',0,0,'INT32','Int(bitWidth=32, isSigned=false)','ZSTD',53,85,'-60.38%',['PLAIN','RLE_DICTIONARY']),('name','name',0,0,'BYTE_ARRAY','String','ZSTD',54,86,'-59.26%',['PLAIN','RLE_DICTIONARY'])]
row_groups:              [(4,2,1,107,171,[('id','id',85,53,true,(1,NULL,NULL,'-3','-3'),47),('name','name',86,54,true,(1,NULL,NULL,'Jim','Jim'),47)])]
id:                      4294967293 -- 4.29 billion

1 row in set. Elapsed: 0.008 sec. 

vector :) 

[vzakaznikov]

The bug seems to be that toInt64() cast still result in a negative number:

vector :) SELECT toInt64(id) FROM iceberg_db.`namespace.iceberg_table`

SELECT toInt64(id)
FROM iceberg_db.`namespace.iceberg_table`

Query id: 7d276348-aaef-4512-a53b-e894c109b91c

   ┌─toInt64(id)─┐
1. │          -1 │
2. │          -2 │
3. │          -3 │
   └─────────────┘

3 rows in set. Elapsed: 0.015 sec. 

vector :) 

[vzakaznikov]

Seems like there is a toInt32 conversion somewhere?

vector :) SELECT toInt64(toUInt32(4294967295))

SELECT toInt64(toUInt32(4294967295))

Query id: 49b25153-7973-459f-bb02-1b66c4fe6f51

   ┌─toInt64(toUI⋯294967295))─┐
1. │               4294967295 │ -- 4.29 billion
   └──────────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

vector :) SELECT toInt64(toInt32(toUInt32(4294967295)))

SELECT toInt64(toInt32(toUInt32(4294967295)))

Query id: b4337399-da45-4477-ac25-0e9c9eb30350

   ┌─toInt64(toIn⋯94967295)))─┐
1. │                       -1 │
   └──────────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

vector :) 
vector :) SELECT toInt64(toInt32(4294967295))

SELECT toInt64(toInt32(4294967295))

Query id: b4e2813d-803c-46d0-b65a-e450afcee411

   ┌─toInt64(toInt32(4294967295))─┐
1. │                           -1 │
   └──────────────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

vector :) 

[vzakaznikov]

So in ClickHouse, as expected, once a number is converted to a signed datatype which results in a negative value you can't just go back to a positive value even if you do toInt64.