ClickHouse / ClickHouse/ClickHouse
Filtering conditions on partitioned tables not working as expected
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
### Company or project name
_No response_
### Describe what's wrong
I am currently debugging a rather puzzling issue. Creating a table with a projection and then filtering by it doesn't seem to work well. I've tried a couple of versions on fiddle (which is great btw, thanks a lot for this tool!) and it seems to work well there? I am unsure what's going off. Docker images seem to work fine so perhaps that's why fiddle works well too?
### Does it reproduce on the most recent release?
Yes
### How to reproduce
Clickhouse version
==============
From https://github.com/ClickHouse/ClickHouse/releases/download/v25.1.7.20-stable/clickhouse-common-static-25.1.7.20-amd64.tgz, Running the binary with `--version` yields `ClickHouse local version 25.1.7.20 (official build`.
The server is ran with `clickhouse-common-static-25.1.7.20/usr/bin/clickhouse server --config-file=clickhouse_config.xml` with this config:
```xml
information
true
/tmp/chdata
8123
9000
true
::/0
default
default
1
1
```
I've used the provided client (`clickhouse-common-static-25.1.7.20/usr/bin/clickhouse client`).
1. Create a table with a partition that we'll filter by later.
```sql
CREATE TABLE IF NOT EXISTS timing_info(
created_at DateTime64(3)
)
ENGINE = MergeTree()
PARTITION BY toDate(created_at)
ORDER BY toUnixTimestamp(created_at);
```
2. Insert some data
```sql
INSERT INTO timing_info select now();
```
3. Confirm we can read the data
```sql
SELECT * FROM timing_info;
SELECT *
FROM timing_info
Query id: d3af8ea2-adb5-4898-be64-3ce111a07529
┌──────────────created_at─┐
1. │ 2025-03-05 14:27:19.000 │
└─────────────────────────┘
1 row in set. Elapsed: 0.002 sec.
```
4. Apply a projection that is true for the data we have
```sql
SELECT created_at, toDateTime64(0, 3), created_at >= toDateTime64(0, 3) FROM timing_info;
SELECT
created_at,
toDateTime64(0, 3),
created_at >= toDateTime64(0, 3)
FROM timing_info
Query id: c520b900-3f65-4952-bf46-f9de1ff6b3a3
┌──────────────created_at─┬──────toDateTime64(0, 3)─┬─greaterOrEqu⋯me64(0, 3))─┐
1. │ 2025-03-05 14:27:19.000 │ 1969-12-31 19:00:00.000 │ 1 │
└─────────────────────────┴─────────────────────────┴──────────────────────────┘
1 row in set. Elapsed: 0.002 sec.
```
5. All good so far, but what if we want to filter by said predicate?
```sql
SELECT created_at, toDateTime64(0, 3), created_at >= toDateTime64(0, 3) FROM timing_info WHERE created_at >= toDateTime64(0, 3);
SELECT
created_at,
toDateTime64(0, 3),
created_at >= toDateTime64(0, 3)
FROM timing_info
WHERE created_at >= toDateTime64(0, 3)
Query id: c7ffae53-6f11-40f5-ad7b-469aaabbcd8a
Ok.
0 rows in set. Elapsed: 0.002 sec.
```
### Expected behavior
This makes no sense to me as I would expect the last query to return the same results as the unfiltered version. Note that if we create the table without a partition, everything works as expected.
### Error message and/or stacktrace
_No response_
### Additional context
The quite odd thing is that I tried a couple of docker images and everything seemed to work fine. Is it possible they are being built in a different way? Fiddle doesn't work either.
Contributor guide
Assessment
This issue has not been assessed yet.