ClickHouse / ClickHouse/ClickHouse

Full scan for a query with filtering by toUnixTimestamp() for DateTime64 key

Open
#79,977 0 comments 0 reactions 0 assignees View on GitHub
comp-query-analyzer external 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

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

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.