set @save_optimizer_switch=@@optimizer_switch; ------------------------------- Part 1: Feature tests. ------------------------------- Default for all tests. set @@optimizer_switch="materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; Schema requires partial matching, but data analysis discoveres there is no need. This is possible only if all outer columns are not NULL. create table t1 (a1 char(8) not null, a2 char(8) not null); create table t2 (b1 char(8), b2 char(8)); insert into t1 values ('1 - 00', '2 - 00'); insert into t1 values ('1 - 01', '2 - 01'); insert into t2 values ('1 - 00', '2 - 00'); insert into t2 values ('1 - 01', NULL ); insert into t2 values (NULL , '2 - 02'); insert into t2 values (NULL , NULL ); insert into t2 values ('1 - 02', '2 - 02'); select * from t1 where (a1, a2) not in (select * from t2 where b1 is not null and b2 is not null); a1 a2 1 - 01 2 - 01 select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; a1 a2 in_res 1 - 00 2 - 00 0 1 - 01 2 - 01 NULL drop table t1, t2; NULLs in the outer columns, no NULLs in the suqbuery create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8) not null, b2 char(8) not null); insert into t1 values (NULL , '2 - 00'); insert into t1 values ('1 - 01', '2 - 01'); insert into t1 values (NULL , NULL ); insert into t2 values ('1 - 00', '2 - 00'); insert into t2 values ('1 - 01', '2 - 01'); insert into t2 values ('1 - 02', '2 - 00'); select * from t1 where (a1, a2) not in (select * from t2 where b1 is not null and b2 is not null); a1 a2 select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; a1 a2 in_res NULL 2 - 00 NULL 1 - 01 2 - 01 0 NULL NULL NULL select * from t1 where (a1, a2) in (select * from t2 where b1 is not null and b2 is not null); a1 a2 1 - 01 2 - 01 select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; a1 a2 in_res NULL 2 - 00 NULL 1 - 01 2 - 01 1 NULL NULL NULL drop table t1, t2; All columns require partial matching (no non-null columns) TODO Both non-NULL columns and columns with NULLs TODO Covering NULL rows create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8), b2 char(8)); insert into t1 values ('1 - 00', '2 - 00'); insert into t1 values ('1 - 01', '2 - 01'); insert into t2 values ('1 - 01', NULL ); insert into t2 values (NULL , '2 - 02'); insert into t2 values (NULL , NULL ); insert into t2 values ('1 - 02', '2 - 02'); select * from t1 where (a1, a2) not in (select * from t2); a1 a2 select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; a1 a2 in_res 1 - 00 2 - 00 NULL 1 - 01 2 - 01 NULL insert into t2 values ('1 - 01', '2 - 01'); select * from t1 where (a1, a2) not in (select * from t2); a1 a2 select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; a1 a2 in_res 1 - 00 2 - 00 NULL 1 - 01 2 - 01 0 select * from t1 where (a1, a2) in (select * from t2); a1 a2 1 - 01 2 - 01 select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; a1 a2 in_res 1 - 00 2 - 00 NULL 1 - 01 2 - 01 1 drop table t1, t2; Covering NULL columns this case affects only the rowid-merge algorithm set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off"; create table t1 (a1 char(8) not null, a2 char(8), a3 char(8) not null); create table t2 (b1 char(8) not null, b2 char(8), b3 char(8) not null); insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); insert into t2 values ('1 - 01', NULL, '3 - x1'); insert into t2 values ('1 - 02', NULL, '3 - 02'); insert into t2 values ('1 - 00', NULL, '3 - 00'); select * from t1 where (a1, a2, a3) not in (select * from t2); a1 a2 a3 1 - 01 2 - 01 3 - 01 select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 1 select * from t1 where (a1, a2, a3) in (select * from t2); a1 a2 a3 select *, (a1, a2, a3) in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 0 drop table t1, t2; create table t1 (a1 char(8), a2 char(8), a3 char(8) not null); create table t2 (b1 char(8), b2 char(8), b3 char(8) not null); insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); insert into t2 values (NULL, NULL, '3 - x1'); insert into t2 values (NULL, NULL, '3 - 02'); insert into t2 values (NULL, NULL, '3 - 00'); select * from t1 where (a1, a2, a3) not in (select * from t2); a1 a2 a3 1 - 01 2 - 01 3 - 01 select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 1 select * from t1 where (a1, a2, a3) in (select * from t2); a1 a2 a3 select *, (a1, a2, a3) in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 0 drop table t1, t2; Covering NULL row, and a NULL column create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); insert into t2 values ('1 - 01', NULL, '3 - x1'); insert into t2 values (NULL , NULL, NULL ); insert into t2 values ('1 - 00', NULL, '3 - 00'); select * from t1 where (a1, a2, a3) not in (select * from t2); a1 a2 a3 select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 NULL select * from t1 where (a1, a2, a3) in (select * from t2); a1 a2 a3 select *, (a1, a2, a3) in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 NULL drop table t1, t2; Covering NULL row, and covering NULL columns create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); insert into t1 values ('1 - 01', '2 - 01', '3 - 01'); insert into t2 values (NULL, NULL, NULL); insert into t2 values (NULL, NULL, NULL); select * from t1 where (a1, a2, a3) not in (select * from t2); a1 a2 a3 select *, (a1, a2, a3) not in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 NULL select * from t1 where (a1, a2, a3) in (select * from t2); a1 a2 a3 select *, (a1, a2, a3) in (select * from t2) as in_res from t1; a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 NULL drop table t1, t2; ------------------------------- Part 2: Test cases for bugs. ------------------------------- drop table if exists t1, t2; # # LP BUG#608744 # set @@optimizer_switch="materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off"; create table t1 (a1 char(1), a2 char(1)); insert into t1 values (NULL, 'b'); create table t2 (b1 char(1), b2 char(2)); insert into t2 values ('a','b'), ('c', 'd'); select * from t1 where (a1, a2) NOT IN (select b1, b2 from t2); a1 a2 drop table t1,t2; # # LP BUG#601156 # CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL); INSERT INTO t1 VALUES (NULL,2); INSERT INTO t1 VALUES (4,NULL); CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL); INSERT INTO t2 VALUES (6,NULL); INSERT INTO t2 VALUES (NULL,0); set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using where 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `table1`.`a1` AS `a1`,`table1`.`a2` AS `a2` from (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery3>`.`b2`)))))))) `table1` DROP TABLE t1, t2; # # LP BUG#613009 Crash in Ordered_key::get_field_idx # 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'); explain select * from t1 where (a1, a2) not in (select a1, a2 from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 select * from t1 where (a1, a2) not in (select a1, a2 from t1); a1 a2 drop table t1; # # LP BUG#680058 void Ordered_key::add_key(rownum_t): # Assertion `key_buff_elements && cur_key_idx < key_buff_elements' failed # create table t1 (f1 char(1), f2 char(1)); insert into t1 values ('t', '0'), ('0', 't'); create table t2 (f3 char(1), f4 char(1)); insert into t2 values ('t', NULL), ('t', NULL), ('d', 'y'); set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,semijoin=off'; select * from t1 where (f1, f2) not in (select * from t2); f1 f2 0 t drop table t1, t2; # # LP BUG#809245 Second assertion `bit < (map)->n_bits' with partial_match_merge # CREATE TABLE t1 (d varchar(32)) ; INSERT INTO t1 VALUES ('r'); CREATE TABLE t2 ( a int, c varchar(32)) ; INSERT INTO t2 VALUES (5,'r'); CREATE TABLE t3 ( a int NOT NULL , d varchar(32)) ; INSERT INTO t3 VALUES (10,'g'); set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off'; EXPLAIN SELECT * FROM t1 WHERE (t1.d , t1.d) NOT IN ( SELECT t3.d , t2.c FROM t3 LEFT JOIN t2 ON t3.a = t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY t3 system NULL NULL NULL NULL 1 2 SUBQUERY t2 system NULL NULL NULL NULL 1 SELECT * FROM t1 WHERE (t1.d , t1.d) NOT IN ( SELECT t3.d , t2.c FROM t3 LEFT JOIN t2 ON t3.a = t2.a); d r set @@optimizer_switch='materialization=off,in_to_exists=on'; EXPLAIN SELECT * FROM t1 WHERE (t1.d , t1.d) NOT IN ( SELECT t3.d , t2.c FROM t3 LEFT JOIN t2 ON t3.a = t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 SELECT * FROM t1 WHERE (t1.d , t1.d) NOT IN ( SELECT t3.d , t2.c FROM t3 LEFT JOIN t2 ON t3.a = t2.a); d r drop table t1, t2, t3; # # LP BUG#809266 Diverging results with partial_match_rowid_merge=on # CREATE TABLE t1 (c int) ; INSERT INTO t1 VALUES (0),(0); CREATE TABLE t2 (a int, b int) ; INSERT INTO t2 VALUES (6,3), (9,NULL); set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off'; EXPLAIN SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); c 0 0 EXPLAIN SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); c 0 0 set @@optimizer_switch='materialization=off,in_to_exists=on'; EXPLAIN SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT b, a FROM t2); c 0 0 EXPLAIN SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM t1 WHERE (6, 4 ) NOT IN (SELECT a, b FROM t2); c 0 0 drop table t1, t2; # # LP BUG#856152 Wrong result with NOT IN subquery and partial_match_rowid_merge # CREATE TABLE t1 ( f1 integer NOT NULL , f2 integer) ; INSERT INTO t1 VALUES (3,3),(48,NULL),(49,1); CREATE TABLE t2 ( f3 int) ; INSERT INTO t2 VALUES (5); set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); f3 5 set @@optimizer_switch='in_to_exists=on,materialization=off'; EXPLAIN SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); f3 5 drop table t1, t2; set @@optimizer_switch=@save_optimizer_switch;