|
The following query
SELECT * FROM t1 AS alias1, t2 AS alias2
|
WHERE EXISTS (
|
SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b
|
) OR a = 5
|
on the test data returns an empty result set if it's run with exists_to_in=on, and 1 row otherwise. 1 row is the correct result.
bzr version-info
revision-id: sanja@askmonty.org-20120718105902-imt07qp0v913sqa4
|
date: 2012-07-18 13:59:02 +0300
|
build-date: 2012-07-19 19:49:58 +0400
|
revno: 3466
|
Reproducible with any of MyISAM, Aria, InnoDB.
Minimal optimizer_switch:
exists_to_in=on,in_to_exists=on
Full optimizer_switch (default + in_to_exists=on):
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=on,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 the minimal switch (exists_to_in=on,in_to_exists=on, everything else is OFF):
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
|
Warnings:
|
Note 1276 Field or reference 'test.alias1.a' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.alias2.b' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`alias1`.`a` AS `a`,`test`.`alias2`.`b` AS `b` from `test`.`t1` `alias1` join `test`.`t2` `alias2` where (<in_optimizer>(`test`.`alias1`.`a`,<exists>(select `test`.`t2`.`b` from `test`.`t2` where ((`test`.`t2`.`b` > `test`.`alias2`.`b`) and (<cache>(`test`.`alias1`.`a`) = `test`.`t2`.`b`)))) or (`test`.`alias1`.`a` = 5))
|
EXPLAIN with the default optimizer switch + exists_to_in=on:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
|
Warnings:
|
Note 1276 Field or reference 'test.alias1.a' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.alias2.b' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`alias1`.`a` AS `a`,`test`.`alias2`.`b` AS `b` from `test`.`t1` `alias1` join `test`.`t2` `alias2` where (<expr_cache><`test`.`alias1`.`a`,`test`.`alias2`.`b`>(<in_optimizer>(`test`.`alias1`.`a`,<exists>(select `test`.`t2`.`b` from `test`.`t2` where ((`test`.`t2`.`b` > `test`.`alias2`.`b`) and (<cache>(`test`.`alias1`.`a`) = `test`.`t2`.`b`))))) or (`test`.`alias1`.`a` = 5))
|
Test case:
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (2),(3);
|
|
CREATE TABLE t2 (b INT);
|
INSERT INTO t2 VALUES (1),(3);
|
|
SET optimizer_switch = 'exists_to_in=on,in_to_exists=on';
|
|
SELECT * FROM t1 AS alias1, t2 AS alias2
|
WHERE EXISTS (
|
SELECT 1 FROM t2 WHERE b = alias1.a AND b > alias2.b
|
) OR a = 5;
|
|
Expected result:
Actual result:
|