Details
-
Type:
Technical task
-
Status: Closed
-
Priority:
Major
-
Resolution: Duplicate
-
Affects Version/s: None
-
Fix Version/s: 10.0.1
-
Component/s: None
-
Labels: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 )
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Duplicate of
MDEV-3879.