diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/join_cache.result | 19 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 33 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 33 | ||||
| -rw-r--r-- | mysql-test/t/join_cache.test | 20 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 21 |
5 files changed, 126 insertions, 0 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 618ce7e540b..b3ca6dc0df2 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5637,4 +5637,23 @@ c set join_buffer_size=default; set optimizer_switch=@tmp_optimizer_switch; DROP table t1,t2,t3; +set join_buffer_size= default; +set @@optimizer_switch=@save_optimizer_switch; +# +# MDEV-5293: outer join, join buffering, and order by - invalid query plan +# +create table t0 (a int primary key) engine=myisam; +insert into t0 values (1); +create table t1(a int) engine=myisam; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +alter table t1 add b int; +create table t2 like t1; +insert into t2 select * from t1; +#The following must use "Using temporary; Using filesort" and not just "Using filesort": +explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 system NULL NULL NULL NULL 1 Using temporary; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; 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; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index a0ebbb3305d..ca903de4e80 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2934,6 +2934,39 @@ 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=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=off,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=off +select @@join_cache_level; +@@join_cache_level +6 +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 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (incremental, BNL join) +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; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 2d06c3e2a30..585ef677492 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3635,6 +3635,26 @@ set join_buffer_size=default; set optimizer_switch=@tmp_optimizer_switch; DROP table t1,t2,t3; +set join_buffer_size= default; +set @@optimizer_switch=@save_optimizer_switch; + + +--echo # +--echo # MDEV-5293: outer join, join buffering, and order by - invalid query plan +--echo # +create table t0 (a int primary key) engine=myisam; +insert into t0 values (1); + +create table t1(a int) engine=myisam; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +alter table t1 add b int; + +create table t2 like t1; +insert into t2 select * from t1; +--echo #The following must use "Using temporary; Using filesort" and not just "Using filesort": +explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a; + +drop table t0,t1,t2; # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index cde8d5d0e19..3b5f4bb08b2 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2622,5 +2622,26 @@ DROP TABLE t1, t2, t3; set join_buffer_size = @tmp_join_buffer_size; set max_heap_table_size = @tmp_max_heap_table_size; +--echo # +--echo # MDEV-5161: Wrong result (missing rows) with semijoin, LEFT JOIN, ORDER BY, constant table +--echo # +select @@optimizer_switch; +select @@join_cache_level; +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; + +--echo # 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; + +DROP TABLE t1,t2,t3; # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; |