diff options
Diffstat (limited to 'mysql-test/main/join_cache.test')
| -rw-r--r-- | mysql-test/main/join_cache.test | 36 |
1 files changed, 36 insertions, 0 deletions
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index ce84ccb56fc..506e613887b 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4071,6 +4071,41 @@ set join_buffer_size=@save_join_buffer_size; --echo # +--echo # MDEV-21104: BNLH used for multi-join query with embedded outer join +--echo # and possible 'not exists' optimization +--echo # + +set join_cache_level=4; + +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int, c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2),(2,4); +CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (e int primary key) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1),(2); +ANALYZE TABLE t1,t2,t3,t4; + +SELECT * FROM t2 LEFT JOIN t3 ON c = d; +SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4; + +let $q1= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +eval EXPLAIN $q1; +eval $q1; + +let $q2= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e + WHERE e IS NULL; +eval EXPLAIN $q2; +eval $q2; + +DROP TABLE t1,t2,t3,t4; + +set join_cache_level=@save_join_cache_level; + +--echo # --echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access --echo # create table t1(a int primary key); @@ -4109,6 +4144,7 @@ where drop table t1,t2,t3; +--echo # End of 10.3 tests # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; |