Skip to content

Index or partition filter doesn't work on where clause like (index_key, partition_key)=(v1,v2) #29269

@lingtaolf

Description

@lingtaolf

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=3

Insert 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.

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions