|
The following query
SELECT * FROM t1 AS alias
|
WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );
|
on the test data returns 1 row with exists_to_in=off, 2 rows with exists_to_in=on + materialization=on + semijoin=on, and 0 rows with exists_in=on + materialization=on + semijon=off. The correct result is 1 row.
bzr version-info
|
revision-id: sanja@montyprogram.com-20120430093355-4yrpta86g97g6rhb
|
date: 2012-04-30 12:33:55 +0300
|
build-date: 2012-05-02 17:50:48 +0400
|
revno: 3390
|
Default optimizer_switch + exists_to_in=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
|
EXPLAINs:
With exists_to_in=ON, materialization=ON, everything else OFF (empty result set instead of 1 row):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00 Using where
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
Warnings:
|
Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` where <in_optimizer>(`test`.`alias`.`b`,`test`.`alias`.`b` in ( <materialize> (select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` > `test`.`alias`.`a`) ), <primary_index_lookup>(`test`.`alias`.`b` in <temporary table> on distinct_key where ((`test`.`alias`.`b` = `<subquery2>`.`a`)))))
|
With exists_to_in=ON, materialization=ON, semijoin=ON, everything else OFF (2 rows instead of 1 row):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where ((`test`.`t1`.`a` > `test`.`alias`.`a`))
|
With default optimizer_switch + exists_to_in=ON (2 rows instead of 1 row):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY alias ALL NULL NULL NULL NULL 2 100.00
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1276 Field or reference 'test.alias.a' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.alias.b' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`alias`.`a` AS `a`,`test`.`alias`.`b` AS `b` from `test`.`t1` `alias` semi join (`test`.`t1`) where ((`test`.`t1`.`a` > `test`.`alias`.`a`))
|
Test case:
SET optimizer_switch='exists_to_in=on';
|
|
CREATE TABLE t1 ( a VARCHAR(1), b VARCHAR(1) );
|
INSERT INTO t1 VALUES ('v','v'),('s','v');
|
|
SELECT * FROM t1 AS alias
|
WHERE EXISTS ( SELECT * FROM t1 WHERE a > alias.a AND a = alias.b );
|
Expected result:
Actual result differs depending on the optimizer_switch (see above).
|