ClickHouse / ClickHouse/ClickHouse
Unexpected exception behavior with brace expression and s3 and S3Cluster table function
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
### Company or project name
_No response_
### Describe the unexpected behaviour
Similarly to what was described [here](https://github.com/ClickHouse/ClickHouse/issues/88920), ClickHouse behaves differently when importing a single file or the same file twice using brace expression.
ClickHouse throws a parsing exception when importing a single file, but not with multiple files with brace expression.
### Which ClickHouse versions are affected?
Reproduced on `25.10.1.3832`
### How to reproduce
Create a parquet file as follows:
```python
import pandas as pd
data_1 = {
"ts" : ["2020-01-01 14:00:00+00:00"]
}
df_1 = pd.DataFrame(data_1)
df_1.to_parquet("output.parquet", index=False)
```
Check the file:
```
DESCRIBE TABLE s3('http://minio:9000/test/output.parquet', 'minio', 'minio123', 'Parquet')
Query id: ad53ca7a-0e83-4250-a607-d2d956223c2c
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
1. │ ts │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SELECT version()
┌─version()────┐
1. │ 25.10.1.3832 │
└──────────────┘
CREATE TABLE test
(
`ts` DateTime64(9, 'UTC')
)
ORDER BY tuple();
```
# S3
## Insert nr.1 - With a single file ClickHouse throws a parsing exception
```sql
INSERT INTO test (ts) SELECT ts
FROM s3('http://minio:9000/test/output.parquet', 'minio', 'minio123')
Query id: c6a148a0-e470-4d0e-9110-5e6f41d5486e
Elapsed: 0.022 sec.
Received exception from server (version 25.10.1):
Code: 6. DB::Exception: Received from localhost:9000.
DB::Exception: Cannot parse string '2020-01-01 14:00:00+00:00' as DateTime64(9, 'UTC'): syntax error at position 19 (parsed just '2020-01-01 14:00:00'):
while converting column `ts` from type String to type DateTime64(9, 'UTC'): (in file/uri test/output.parquet):
While executing ParquetBlockInputFormat: While executing ReadFromObjectStorage. (CANNOT_PARSE_TEXT)
```
## Insert nr.2 - With brace expression ClickHouse doesn't throw the exception
```sql
INSERT INTO test (ts) SELECT ts
FROM s3('http://minio:9000/test/{output.parquet,output.parquet}', 'minio', 'minio123')
Query id: 261c2c71-c128-4016-9929-7893586938ee
Ok.
0 rows in set. Elapsed: 0.031 sec.
SELECT *
FROM test
Query id: 8a147f2f-6d4a-4bd5-9a99-bd33a7d3332d
┌────────────────────────────ts─┐
1. │ 1970-01-01 00:00:00.000000000 │
2. │ 1970-01-01 00:00:00.000000000 │
└───────────────────────────────┘
```
## Workaround - nr.1
```sql
INSERT INTO test (ts) SELECT ts
FROM s3('http://minio:9000/test/{output,output}.parquet', 'minio', 'minio123')
Query id: 252f66bc-eb1f-4978-941c-39237dae17ff
Elapsed: 0.019 sec.
Received exception from server (version 25.10.1):
Code: 6. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot parse string '2020-01-01 14:00:00+00:00' as DateTime64(9, 'UTC'): syntax error at position 19 (parsed just '2020-01-01 14:00:00'):
while converting column `ts` from type String to type DateTime64(9, 'UTC'): (in file/uri test/output.parquet):
While executing ParquetBlockInputFormat: While executing ReadFromObjectStorage. (CANNOT_PARSE_TEXT)
```
## Workaround - nr.2
```sql
INSERT INTO test (ts) SELECT ts
FROM s3('http://minio:9000/test/{output.parquet,output.parquet}', 'minio', 'minio123', Parquet)
Query id: e2fb4704-268c-495e-9210-63fae9fbf5ab
Elapsed: 0.017 sec.
Received exception from server (version 25.10.1):
Code: 6. DB::Exception: Received from localhost:9000.
DB::Exception: Cannot parse string '2020-01-01 14:00:00+00:00' as DateTime64(9, 'UTC'): syntax error at position 19 (parsed just '2020-01-01 14:00:00'):
while converting column `ts` from type String to type DateTime64(9, 'UTC'): (in file/uri test/output.parquet): While executing ParquetBlockInputFormat:
While executing ReadFromObjectStorage. (CANNOT_PARSE_TEXT)
```
# S3Cluster
## Insert nr.1 - With a single file ClickHouse throws a parsing exception
```sql
INSERT INTO test (ts) SELECT ts
FROM s3Cluster('default', 'http://minio:9000/test/output.parquet', 'minio', 'minio123')
Query id: 701c47cd-4e1a-44d3-9720-3d631bd1e19b
Elapsed: 0.031 sec.
Received exception from server (version 25.10.1):
Code: 6. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse2:9000. DB::Exception: Cannot parse string '2020-01-01 14:00:00+00:00' as DateTime64(9, 'UTC'): syntax error at position 19 (parsed just '2020-01-01 14:00:00'): while converting column `ts` from type String to type DateTime64(9, 'UTC'): (in file/uri test/output.parquet): While executing ParquetBlockInputFormat: While executing ReadFromObjectStorage. (CANNOT_PARSE_TEXT)
```
## Insert nr.2 - With brace expression ClickHouse doesn't throw the exception
```sql
INSERT INTO test (ts) SELECT ts
FROM s3Cluster(default, 'http://minio:9000/test/{output.parquet,output.parquet}', 'minio', 'minio123')
Query id: 97924a68-ff55-4f64-907c-cb6b21445c6e
Ok.
0 rows in set. Elapsed: 0.021 sec.
SELECT *
FROM test
Query id: 706cf703-1a99-4bec-ab43-9b582ffcca4b
┌────────────────────────────ts─┐
1. │ 1970-01-01 00:00:00.000000000 │
2. │ 1970-01-01 00:00:00.000000000 │
└───────────────────────────────┘
```
## Workaround - nr.1
```sql
INSERT INTO test (ts) SELECT ts
FROM s3Cluster(default, 'http://minio:9000/test/{output,output}.parquet', 'minio', 'minio123')
Query id: b0706372-9d75-4d96-89f1-58c19be8eee4
Elapsed: 0.017 sec.
Received exception from server (version 25.10.1):
Code: 6. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse1:9000. DB::Exception: Cannot parse string '2020-01-01 14:00:00+00:00' as DateTime64(9, 'UTC'): syntax error at position 19 (parsed just '2020-01-01 14:00:00'): while converting column `ts` from type String to type DateTime64(9, 'UTC'): (in file/uri test/output.parquet): While executing ParquetBlockInputFormat: While executing ReadFromObjectStorage. (CANNOT_PARSE_TEXT)
```
## Workaround - nr.2
```sql
INSERT INTO test (ts) SELECT ts
FROM s3Cluster(default, 'http://minio:9000/test/{output.parquet,output.parquet}', 'minio', 'minio123', Parquet)
Query id: b0613416-aba7-4773-9998-66c76ea0dcaa
Elapsed: 0.015 sec.
Received exception from server (version 25.10.1):
Code: 6. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse2:9000. DB::Exception: Cannot parse string '2020-01-01 14:00:00+00:00' as DateTime64(9, 'UTC'): syntax error at position 19 (parsed just '2020-01-01 14:00:00'): while converting column `ts` from type String to type DateTime64(9, 'UTC'): (in file/uri test/output.parquet): While executing ParquetBlockInputFormat: While executing ReadFromObjectStorage. (CANNOT_PARSE_TEXT)
```
### Expected behavior
Always throw the exception.
### Error message and/or stacktrace
_No response_
### Additional context
_No response_
Contributor guide
Assessment
This issue has not been assessed yet.