diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/join_cache.test | 20 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 21 |
2 files changed, 41 insertions, 0 deletions
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; |