Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
-
None
-
None
Description
The following test case
SET optimizer_switch = 'exists_to_in=on'; |
|
|
CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (4,'b'),(5,'y'); |
|
|
CREATE TABLE t2 (b2 CHAR(1)) ENGINE=MyISAM; |
INSERT INTO t2 VALUES ('z'),('b'); |
|
|
CREATE TABLE t3 (a3 INT, b3 CHAR(1)) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (4,'j'),(6,'v'); |
|
|
SELECT * FROM t1 LEFT JOIN t2 ON ( b2 = b1 ) |
WHERE NOT EXISTS ( SELECT * FROM t3 WHERE b3 = b2 AND a3 = a1 ) ; |
|
|
returns one row:
a1 b1 b2
|
------------------
|
4 b b
|
while it should return two:
a1 b1 b2
|
------------------
|
4 b b
|
5 y NULL
|
and it does so without exists_to_in.
branch: 10.0-exists2in
|
revision-id: psergey@askmonty.org-20121123063639-1jqbkjuxevqe1vb6
|
date: 2012-11-23 10:36:39 +0400
|
revno: 3482
|
Minimal optimizer_switch: exists_to_in=on,in_to_exists=on or exists_to_in=on,materialization=on
Full optimizer_switch (default + 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
|
Reproducible with MyISAM, Aria, InnoDB.
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 t1 ALL NULL NULL NULL NULL 2 100.00
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1276 Field or reference 'test.t2.b2' of SELECT #2 was resolved in SELECT #1
|
Note 1276 Field or reference 'test.t1.a1' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b2` = `test`.`t1`.`b1`)) where (not((<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>((`test`.`t2`.`b2`,`test`.`t1`.`a1`),(`test`.`t2`.`b2`,`test`.`t1`.`a1`) in ( <materialize> (select `test`.`t3`.`b3`,`test`.`t3`.`a3` from `test`.`t3` where 1 ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery2>`.`b3`) and (`test`.`t1`.`a1` = `<subquery2>`.`a3`)))))) and `test`.`t1`.`a1` and `test`.`t2`.`b2`)))
|
SELECT * FROM t1 LEFT JOIN t2 ON ( b2 = b1 )
|
|
|