diff options
Diffstat (limited to 'mysql-test/main/subselect_partial_match.test')
-rw-r--r-- | mysql-test/main/subselect_partial_match.test | 14 |
1 files changed, 8 insertions, 6 deletions
diff --git a/mysql-test/main/subselect_partial_match.test b/mysql-test/main/subselect_partial_match.test index fd1e6de7..9a32ef54 100644 --- a/mysql-test/main/subselect_partial_match.test +++ b/mysql-test/main/subselect_partial_match.test @@ -2,6 +2,7 @@ # Tests for # MWL#68: Subquery optimization: Efficient NOT IN execution with NULLs # +--source include/have_sequence.inc set @save_optimizer_switch=@@optimizer_switch; @@ -614,11 +615,11 @@ drop table t1,t2; --echo # CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL); -INSERT INTO t1 VALUES (NULL,2); -INSERT INTO t1 VALUES (4,NULL); +INSERT INTO t1 VALUES (NULL,2), (4,NULL),(100,100); CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL); -INSERT INTO t2 VALUES (6,NULL); -INSERT INTO t2 VALUES (NULL,0); +INSERT INTO t2 VALUES (6,NULL), (NULL,0),(1000,1000); + +insert into t2 select seq,seq from seq_2000_to_2100; set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; @@ -636,8 +637,9 @@ DROP TABLE t1, t2; set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off'; -create table t1 (a1 char(3) DEFAULT NULL, a2 char(3) DEFAULT NULL); -insert into t1 values (NULL, 'a21'), (NULL, 'a22'); +create table t1 (a1 char(4) DEFAULT NULL, a2 char(4) DEFAULT NULL); +insert into t1 values (NULL, 'a21'), (NULL, 'a22'), ('xxx','xxx'); +insert into t1 select seq,seq from seq_2000_to_2100; explain select * from t1 where (a1, a2) not in (select a1, a2 from t1); select * from t1 where (a1, a2) not in (select a1, a2 from t1); drop table t1; |