From c4defdc8d971cdcc186de549bae9ac4351c7aade Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 21 Nov 2013 11:19:01 +0400 Subject: MDEV-5161: Wrong result (missing rows) with semijoin, LEFT JOIN, ORDER BY, constant table - Don't pull out a table out of a semi-join if it is on the inner side of an outer join. - Make join->sort_by_table= get_sort_by_table(...) call after const table detection is done. That way, the value of join->sort_by_table will match the actual execution. Which will allow the code in setup_semijoin_dups_elimination() (search for "Make sure that possible sorting of rows from the head table is not to be employed." to see that "Using filesort" is going to be used together with Duplicate Elimination ( and change it to Using temporary + Using filesort) --- mysql-test/r/subselect_sj.result | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/r/subselect_sj.result') diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 2d229db9ec5..babcbaf34d1 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2920,4 +2920,37 @@ COUNT(*) DROP TABLE t1, t2, t3; set join_buffer_size = @tmp_join_buffer_size; set max_heap_table_size = @tmp_max_heap_table_size; +# +# MDEV-5161: Wrong result (missing rows) with semijoin, LEFT JOIN, ORDER BY, constant table +# +select @@optimizer_switch; +@@optimizer_switch +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=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off +select @@join_cache_level; +@@join_cache_level +1 +CREATE TABLE t1 (pk INT PRIMARY KEY, c1 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'v'),(2,'v'),(3,'c'),(4,NULL),(5,'x'); +CREATE TABLE t2 (c2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('x'); +CREATE TABLE t3 (c3 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('x'),('d'); +SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1; +pk c1 c2 +4 NULL x +3 c x +1 v x +2 v x +5 x x +# This should show that "t1 left join t3" is still in the semi-join nest: +EXPLAIN EXTENDED +SELECT * FROM t1, t2 WHERE pk IN ( SELECT pk FROM t1 LEFT JOIN t3 ON (c1 = c3 ) ) ORDER BY c2, c1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 Using temporary; Using filesort +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 100.00 Start temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t1.pk 1 100.00 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary +Warnings: +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1`,'x' AS `c2` from `test`.`t1` semi join (`test`.`t1` left join `test`.`t3` on((`test`.`t1`.`c1` = `test`.`t3`.`c3`))) where (`test`.`t1`.`pk` = `test`.`t1`.`pk`) order by 'x',`test`.`t1`.`c1` +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_sj_tmp; -- cgit v1.2.1