ClickHouse / ClickHouse/ClickHouse
Full scan for a query with filtering by toUnixTimestamp() for DateTime64 key
- 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
Some change between 22.12.6.22 and 23.1.1.3077 made this query scanning full table. (It remains for master).
### How to reproduce
```
CREATE TABLE test_table
(
ts DateTime64(3) DEFAULT CAST(timestamp, 'Datetime64(3)'),
timestamp UInt32,
value UInt32
)
ENGINE = MergeTree
PARTITION BY formatDateTime(ts, '%Y-%m-%d-%H')
ORDER BY ts;
INSERT INTO test_table (timestamp, value) VALUES (toUnixTimestamp('2025-10-11 10:00:00'), 1);
INSERT INTO test_table (timestamp, value) VALUES (toUnixTimestamp('2025-10-12 10:00:00'), 1);
INSERT INTO test_table (timestamp, value) VALUES (toUnixTimestamp('2025-10-13 10:00:00'), 1);
explain indexes=1
SELECT * FROM test_table where toUnixTimestamp(ts) >= toUnixTimestamp('2025-10-13 10:00:00')
```
```
Expression ((Projection + Before ORDER BY))
ReadFromMergeTree (default.test_table)
Indexes:
MinMax
Keys:
ts
Condition: (toUnixTimestamp(ts) in [1760349600, +Inf))
Parts: 3/3
Granules: 3/3
Partition
Condition: true
Parts: 3/3
Granules: 3/3
PrimaryKey
Keys:
ts
Condition: (toUnixTimestamp(ts) in [1760349600, +Inf))
Parts: 3/3
Granules: 3/3
```
https://fiddle.clickhouse.com/d96ea0c8-b3bf-4875-bb8a-66bb40a41444
### Expected behavior
For version 22.12.6.22
```
Expression ((Projection + Before ORDER BY))
ReadFromMergeTree (default.test_table)
Indexes:
MinMax
Keys:
ts
Condition: (toUnixTimestamp(ts) in [1760349600, +Inf))
Parts: 1/3
Granules: 1/3
Partition
Condition: true
Parts: 1/1
Granules: 1/1
PrimaryKey
Keys:
ts
Condition: (toUnixTimestamp(ts) in [1760349600, +Inf))
Parts: 1/1
Granules: 1/1
```
### Error message and/or stacktrace
_No response_
### Additional context
_No response_
Contributor guide
Assessment
This issue has not been assessed yet.