Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.0
-
Component/s: None
-
Labels:None
Description
The following query
SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a )
on the test data returns 3 rows if executed with exists_to_in=on, materialization=off, and 2 rows otherwise. The correct result is 2 rows.
bzr version-info
revision-id: sanja@montyprogram.com-20120430093355-4yrpta86g97g6rhb date: 2012-04-30 12:33:55 +0300 build-date: 2012-05-02 22:58:36 +0400 revno: 3390
optimizer_switch (default + exists_to_in=on + materialization=off):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=on
EXPLAIN with optimizer_switch = default + exists_to_in=on + materialization=off (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(((`test`.`t1`.`a` is not null) and <exists>(select `test`.`t2`.`b` from `test`.`t2` where ((`test`.`t2`.`b` is not null) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`))))))
EXPLAIN with optimizer_switch = default + exists_to_in=off + materialization=off (correct result):
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(exists(select 1 from `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`a`)))))
Test case:
SET optimizer_switch='materialization=off'; CREATE TABLE t1 ( a INT ) ; INSERT INTO t1 VALUES (0),(8),(1); CREATE TABLE t2 ( b INT ) ; INSERT INTO t2 VALUES (1),(2),(3); SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b = a );
Expected result:
# a # --- # 0 # 8
Actual result:
# a # --- # 0 # 8 # 1
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-38 NOT EXISTS to IN (part of exists2in transformation for 10.0)
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The problem is that cache object from outer query firld was not updated.
select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(((`test`.`t1`.`a` is not null) and <exists>(select `test`.`t2`.`b` from `test`.`t2` where ((`test`.`t2`.`b` is not null) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`b`))))))
<cache>(`test`.`t1`.`a`) - this one
The problem is that Item_in_optimizer excluded from execution chain...
fixed
SET should be:
SET optimizer_switch='materialization=off,exists_to_in=on';