ClickHouse / ClickHouse/ClickHouse

Unexpected exception behavior with brace expression and s3 and S3Cluster table function

Open
#89,606 0 comments 0 reactions 1 assignee Claimed by @vitlibar View on GitHub
comp-object-storage unexpected behaviour
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.