-
Notifications
You must be signed in to change notification settings - Fork 8.3k
Index or partition filter doesn't work on where clause like (index_key, partition_key)=(v1,v2) #29269
Description
Describe what's wrong
When we put the primary key and another column in one tuple to filter data, the primary key index wouldn't work and partition filter is as same.
The sql which index or partition filter wouldn't work is like below:
SELECT * FROM t WHERE (primary_key, other_column) = (v1, v2);How to reproduce
Create table
create table test.test (id UInt32, value String, log_date Date) Engine=MergeTree() order by id partition by log_date settings index_granularity=3Insert data
insert into test.test values (1, 'A','2021-01-01'),(2,'B','2021-01-01'),(3,'C','2021-01-01'),(4,'D','2021-01-02'),(5,'E','2021-01-02')Query
select * from test.test where (id, value) = (1, 'A');
OR
select * from test.test where (log_date, value) = ('2021-01-01', 'A');
After querying, we can get the read_rows by querying table system.query_log with the query_id .
select query, read_rows from system.query_log where query_id='<query_id>' and type=2;
We can find the query select * from test.test where (id, value) = (1, 'A') will read all 5 rows, but select * from test.test where id = 1 and value = 'A' will only read 3 rows.
Expected behavior
┌─query─────────────────────────────────────────────────┬─read_rows─┐
│ select * from test.test where (id, value) = (1, 'A'); │ 3 │
└───────────────────────────────────────────────────────┴───────────┘
Actual get
┌─query─────────────────────────────────────────────────┬─read_rows─┐
│ select * from test.test where (id, value) = (1, 'A'); │ 5 │
└───────────────────────────────────────────────────────┴───────────┘
Additional context
We found this problem while using clickhouse-copier. It will check partition data on shard use query like select 1 from t where (k1, k2,...) = (v1,v2,...) and the progress will take a long time to get the result ,even if we just copy one partition.