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;