|
Test case:
SET optimizer_switch = 'materialization=on,semijoin=on';
|
|
CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(2);
|
|
CREATE TABLE t2 (i2 INT, c2 CHAR(1), KEY(c2,i2)) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (8,'c'),(6,'c');
|
|
CREATE TABLE t3 (i3 INT, c3 CHAR(1)) ENGINE=MyISAM;
|
INSERT INTO t3 VALUES (1,'c'),(4,'r');
|
|
SELECT * FROM t1, t2
|
WHERE i1 IN ( SELECT i3 FROM t3, t2 WHERE c2 = c3 AND i3 < ALL ( SELECT 4 UNION SELECT 8 ) );
|
Expected result:
Actual result: empty set
Reproducible starting from revno: 3546 on 10.0-mdev83 (timour@askmonty.org-20130401103605-2cbjiqkw8j1hd4sk).
Minimal optimizer_switch: materialization=on,semijoin=on
Full optimizer_switch (default):
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=off,expensive_pred_static_pushdown=off
|
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
1 PRIMARY t2 index NULL c2 7 NULL 2 100.00 Using where; Subqueries: 3; Using index; Using join buffer (flat, BNL join)
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Subqueries: 3
|
2 MATERIALIZED t2 ref c2 c2 2 test.t3.c3 1 100.00 Using index
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL NULL
|
Warnings:
|
Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`c2` = `test`.`t3`.`c3`) and <not>(<in_optimizer>(`test`.`t3`.`i3`,(<min>(select 4 union select 8) <= <cache>(`test`.`t3`.`i3`)))))
|
|