ClickHouse / ClickHouse/ClickHouse

Filtering conditions on partitioned tables not working as expected

Open
#77,189 11 comments 0 reactions 0 assignees View on GitHub
comp-query-optimizer external potential bug
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

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.