Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.0
-
None
Description
Queries having the form
SELECT * FROM t1 WHERE COL IN (item0, item1, ..., itemN) AND COL = item1;
where item0, ..., itemN are text type are not simplified to
SELECT * FROM t1 WHERE COL = item1;.
Queries like WHERE ... IN are semantically equivalent to a set of disjunctions which, as shown below, are optimized as expected.
Setup:
create table t1 (id int primary key, name text);
|
insert into t1 (id, name) values (1, 'kajfwke'),(2, 'ajewajiflewaj'),(3, 'fawiojewa'),(4, 'fawijoia'),(5,'sfajawkl'),(6,'afeiaoewj'),(7,'awifhewoiaf'),(8,'eiwoahfewa'),(9,'aifewojai'),(10,'fewkajfkelw');
|
Reproduction:
analyze format=json select * from t1 where name in ('kajfwke', 'ajewajiflewaj', 'fawiojewa') and name = 'ajewajiflewaj';
|
-- "attached_condition": "t1.`name` in ('kajfwke','ajewajiflewaj','fawiojewa') and t1.`name` = 'ajewajiflewaj'",
|
analyze format=json select * from t1 where (name = 'kajfwke' or name = 'ajewajiflewaj' or name = 'fawiojewa') and name = 'ajewajiflewaj';
|
-- "attached_condition": "t1.`name` = 'ajewajiflewaj'",
|