Merge MWL#89 into 5.3 main.

6 files changed, 4191 insertions, 28 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 78a75776573..dbfb035eadc 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -841,11 +841,16 @@ x	ROW(11, 12) = (SELECT MAX(x), 22)	ROW(11, 12) IN (SELECT MAX(x), 22)
 1	0	0
 2	0	0
 11	0	0
-# 2nd and 3rd columns should be same for x == 11 only
+# 2nd and 3rd columns should be same
+EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
 x	ROW(11, 12) = (SELECT MAX(x), 12)	ROW(11, 12) IN (SELECT MAX(x), 12)
-1	0	1
-2	0	1
+1	0	0
+2	0	0
 11	1	1
 DROP TABLE t1;
 # both columns should be same
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index f866f1c2aa9..ba7e6871a93 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -848,11 +848,16 @@ x	ROW(11, 12) = (SELECT MAX(x), 22)	ROW(11, 12) IN (SELECT MAX(x), 22)
 1	0	0
 2	0	0
 11	0	0
-# 2nd and 3rd columns should be same for x == 11 only
+# 2nd and 3rd columns should be same
+EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
 x	ROW(11, 12) = (SELECT MAX(x), 12)	ROW(11, 12) IN (SELECT MAX(x), 12)
-1	0	1
-2	0	1
+1	0	0
+2	0	0
 11	1	1
 DROP TABLE t1;
 # both columns should be same
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 8547a180d49..4f90045e60a 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -483,3 +483,266 @@ pk
 # Restore old value for Index condition pushdown
 SET SESSION engine_condition_pushdown=@old_icp;
 DROP TABLE t1,t2;
+#
+# LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
+#
+CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
+CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t2 VALUES ('k'), ('d');
+CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t3 VALUES ('a'), ('b'), ('c');
+CREATE TABLE t4 (c1 varchar(1) primary key);
+INSERT INTO t4 VALUES ('k'), ('d');
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1
+EXPLAIN
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1	c1
+EXPLAIN
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1
+drop table t1, t2, t3, t4;
+#
+# LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache()
+#               on EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f2 int) ;
+INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);
+CREATE TABLE t2 (f1 int, f5 int) ;
+INSERT IGNORE INTO t2 VALUES (1,0);
+CREATE TABLE t3 (f4 int) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+set @@optimizer_switch='in_to_exists=on,materialization=off,semijoin=off';
+EXPLAIN
+SELECT * FROM t2
+WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4);
+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	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
+drop table t1, t2, t3;
+#
+# LP BUG#680005 Second assertion `cache != __null' failed in
+#                sub_select_cache() on EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ;
+INSERT IGNORE INTO t1 VALUES
+('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'),
+('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'),
+('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0');
+CREATE TABLE t2 (f3 int) ;
+INSERT IGNORE INTO t2 VALUES ('7');
+CREATE TABLE t3 (f3 int) ;
+INSERT IGNORE INTO t3 VALUES ('2');
+EXPLAIN
+SELECT t1.f4
+FROM t2 JOIN t1 ON t1.f6
+WHERE
+( t1.f2 ) IN (SELECT SUBQUERY2_t1.f3
+FROM t3 AS SUBQUERY2_t1
+JOIN
+(t1 AS SUBQUERY2_t2
+JOIN
+t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1)
+ON SUBQUERY2_t3.f2)
+GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	PRIMARY	t1	index	NULL	f4	5	NULL	10	Using where
+2	DEPENDENT SUBQUERY	SUBQUERY2_t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	SUBQUERY2_t2	index	NULL	f4	5	NULL	11	Using where; Using index
+2	DEPENDENT SUBQUERY	SUBQUERY2_t3	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer
+drop table t1, t2, t3;
+#
+# LP BUG#680038 bool close_thread_table(THD*, TABLE**):
+#               Assertion `table->key_read == 0' failed in EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f3 int,f4 int) ;
+INSERT IGNORE INTO t1 VALUES (NULL,1,0);
+CREATE TABLE t2 (f2 int,f4 int,f5 int) ;
+INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0);
+CREATE TABLE t3 (f4 int,KEY (f4)) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+set @@optimizer_switch='semijoin=off';
+EXPLAIN
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+WHERE f4 <= ALL
+(SELECT SQ1_t1.f4
+FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+GROUP BY SQ1_t1.f4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	SUBQUERY	SQ1_t1	index	NULL	f4	5	NULL	2	Using index; Using temporary; Using filesort
+3	SUBQUERY	SQ1_t3	index	NULL	f4	5	NULL	2	Using where; Using index; Using join buffer
+drop table t1, t2, t3;
+#
+# BUG#52317: Assertion failing in Field_varstring::store() 
+# 	    at field.cc:6833
+#
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i INTEGER, KEY k(i));
+INSERT INTO t2 VALUES (1), (2);
+EXPLAIN  
+SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t2	index_subquery	k	k	5	const	2	Using index
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# LP BUG#680846: Crash in clear_tables() with subqueries
+#
+CREATE TABLE t1 (f3 int) ;
+INSERT IGNORE INTO t1 VALUES (0),(0);
+CREATE TABLE t2 (f1 int,f3 int,f4 varchar(32)) ;
+INSERT IGNORE INTO t2 VALUES (1,0,'f');
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+COUNT(t2.f3)	f9
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+COUNT(t2.f3)	f9
+0	2
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+COUNT(t2.f3)	f9
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+COUNT(t2.f3)	f9
+0	NULL
+drop table t1,t2;
+#
+# LP BUG#682683 Crash in create_tmp_table called from
+#               JOIN::init_execution
+#
+CREATE TABLE t2 (f1 int) ;
+INSERT INTO t2 VALUES (1),(2);
+CREATE TABLE t1 (f1 int) ;
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+field1
+NULL
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+field1
+NULL
+NULL
+INSERT INTO t1 VALUES (1),(2);
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+ERROR 21000: Subquery returns more than 1 row
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+ERROR 21000: Subquery returns more than 1 row
+drop table t1,t2;
+#
+# LP BUG#680943 Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))' failed with subquery
+#
+CREATE TABLE t1 (f1 int,f3 int) ;
+INSERT IGNORE INTO t1 VALUES ('6','0'),('4','0');
+CREATE TABLE t2 (f1 int,f2 int,f3 int) ;
+INSERT IGNORE INTO t2 VALUES ('6','0','0'),('2','0','0');
+SELECT f2
+FROM (SELECT * FROM t2) AS alias1
+WHERE (SELECT SQ2_t2.f1
+FROM t1 JOIN t1 AS SQ2_t2 ON SQ2_t2.f3
+WHERE SQ2_t2.f3 AND alias1.f1)
+ORDER BY f3 ;
+f2
+drop table t1,t2;
diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result
index 7b9dc3c943f..115a80af55d 100644
--- a/mysql-test/r/subselect_cache.result
+++ b/mysql-test/r/subselect_cache.result
@@ -897,7 +897,7 @@ a	b	SUBS
 5	6	1
 4	5	1
 7	8	NULL
-9	NULL	NULL
+9	NULL	1
 show status like "subquery_cache%";
 Variable_name	Value
 Subquery_cache_hit	0
@@ -916,7 +916,7 @@ a	b	SUBS
 5	6	1
 4	5	1
 7	8	NULL
-9	NULL	NULL
+9	NULL	1
 show status like "subquery_cache%";
 Variable_name	Value
 Subquery_cache_hit	6
@@ -977,7 +977,7 @@ a	b	SUBS
 5	6	0
 4	5	0
 7	8	NULL
-9	NULL	NULL
+9	NULL	0
 show status like "subquery_cache%";
 Variable_name	Value
 Subquery_cache_hit	0
@@ -996,7 +996,7 @@ a	b	SUBS
 5	6	0
 4	5	0
 7	8	NULL
-9	NULL	NULL
+9	NULL	0
 show status like "subquery_cache%";
 Variable_name	Value
 Subquery_cache_hit	6
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index dab00e3be19..ae23302b368 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -30,7 +30,7 @@ create index it3i3 on t3i (c1, c2);
 insert into t1i select * from t1;
 insert into t2i select * from t2;
 insert into t3i select * from t3;
-set @@optimizer_switch='semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 /******************************************************************************
 * Simple tests.
 ******************************************************************************/
@@ -176,33 +176,33 @@ a1	a2
 1 - 02	2 - 02
 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
 ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=on';
 prepare st1 from
 "select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=on';
 execute st1;
 a1	a2
 1 - 01	2 - 01
 1 - 02	2 - 02
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=on';
 execute st1;
 a1	a2
 1 - 01	2 - 01
 1 - 02	2 - 02
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=on';
 prepare st1 from
 "select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 execute st1;
 a1	a2
 1 - 01	2 - 01
 1 - 02	2 - 02
-set @@optimizer_switch='default,materialization=off';
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=on';
 execute st1;
 a1	a2
 1 - 01	2 - 01
 1 - 02	2 - 02
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 explain extended
 select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -549,7 +549,7 @@ a1	a2
 Test that BLOBs are not materialized (except when arguments of some functions).
 */
 # force materialization to be always considered
-set @@optimizer_switch='semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 set @prefix_len = 6;
 set @blob_len = 16;
 set @suffix_len = @blob_len - @prefix_len;
@@ -951,7 +951,7 @@ insert into t1bit values (b'010', b'110');
 insert into t2bit values (b'001', b'101');
 insert into t2bit values (b'010', b'110');
 insert into t2bit values (b'110', b'111');
-set @@optimizer_switch='semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 explain extended select bin(a1), bin(a2)
 from t1bit
 where (a1, a2) in (select b1, b2 from t2bit);
@@ -994,7 +994,7 @@ drop table t1, t2, t3, t1i, t2i, t3i, columns;
 /******************************************************************************
 * Test the cache of the left operand of IN.
 ******************************************************************************/
-set @@optimizer_switch='semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 create table t1 (s1 int);
 create table t2 (s2 int);
 insert into t1 values (5),(1),(0);
@@ -1136,27 +1136,40 @@ drop table t2;
 create table t1 (a1 int key);
 create table t2 (b1 int);
 insert into t1 values (5);
+Only the last query returns correct result. Filed as BUG#40037.
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 min(a1)
-set @@optimizer_switch='default,materialization=off';
+NULL
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
 explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 min(a1)
-set @@optimizer_switch='default,semijoin=off';
+NULL
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 select min(a1) from t1 where 7 in (select b1 from t2);
 min(a1)
-set @@optimizer_switch='default,materialization=off';
+NULL
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select min(a1) from t1 where 7 in (select b1 from t2);
+min(a1)
+NULL
+set @@optimizer_switch='materialization=off,in_to_exists=off,semijoin=on';
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
@@ -1167,7 +1180,7 @@ drop table t1,t2;
 create table t1 (a char(2), b varchar(10));
 insert into t1 values ('a',  'aaa');
 insert into t1 values ('aa', 'aaaa');
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 explain select a,b from t1 where b in (select a 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
@@ -1187,7 +1200,7 @@ INSERT INTO t1 (f1, f2) VALUES (10, 1.668);
 CREATE TABLE t2 LIKE t1;
 INSERT INTO t2 VALUES (1, 1.789);
 INSERT INTO t2 VALUES (13, 1.454);
-SET @@optimizer_switch='default,semijoin=on,materialization=on';
+SET @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=on';
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	2	
@@ -1208,7 +1221,7 @@ PRIMARY KEY (pk)
 INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
 CREATE TABLE t2 LIKE t1;
 INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
-SET @@optimizer_switch='default,semijoin=on,materialization=on';
+SET @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=on';
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
@@ -1237,7 +1250,7 @@ i
 3
 4
 set @save_optimizer_switch=@@optimizer_switch;
-set session optimizer_switch='materialization=off';
+set session optimizer_switch='materialization=off,in_to_exists=on';
 select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
 i
 1
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
new file mode 100644
index 00000000000..d11e2dd2ffc
--- /dev/null
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -0,0 +1,3877 @@
+drop table if exists t1, t2, t1_1024, t2_1024;
+drop procedure if exists make_t1_indexes;
+drop procedure if exists make_t2_indexes;
+drop procedure if exists remove_t1_indexes;
+drop procedure if exists remove_t2_indexes;
+drop procedure if exists add_materialization_data;
+drop procedure if exists delete_materialization_data;
+drop procedure if exists set_all_columns_not_null;
+drop procedure if exists set_all_columns_nullable;
+create table t1 (a1 char(8), a2 char(8), a3 char(8), a4 int);
+insert into t1 values ('1 - 00', '2 - 00', '3 - 00', 0);
+insert into t1 values ('1 - 01', '2 - 01', '3 - 01', 1);
+insert into t1 values ('1 - 02', '2 - 02', '3 - 02', 2);
+create table t2 (b1 char(8), b2 char(8), b3 char(8), b4 int);
+insert into t2 values ('1 - 01', '2 - 01', '3 - 01', 1);
+insert into t2 values ('1 - 01', '2 - 01', '3 - 02', 2);
+insert into t2 values ('1 - 02', '2 - 02', '3 - 03', 3);
+insert into t2 values ('1 - 02', '2 - 02', '3 - 04', 4);
+insert into t2 values ('1 - 03', '2 - 03', '3 - 05', 5);
+create table t1_1024 (a1 blob(1024), a2 blob(1024));
+insert into t1_1024 values (concat('1 - 00', repeat('x', 1018)), concat('2 - 00', repeat('x', 1018)));
+insert into t1_1024 values (concat('1 - 01', repeat('x', 1018)), concat('2 - 01', repeat('x', 1018)));
+create table t2_1024 (b1 blob(1024), b2 blob(1024));
+insert into t2_1024 values (concat('1 - 01', repeat('x', 1018)), concat('2 - 01', repeat('x', 1018)));
+insert into t2_1024 values (concat('1 - 02', repeat('x', 1018)), concat('2 - 02', repeat('x', 1018)));
+insert into t2_1024 values (concat('1 - 03', repeat('x', 1018)), concat('2 - 03', repeat('x', 1018)));
+insert into t2_1024 values (concat('1 - 04', repeat('x', 1018)), concat('2 - 04', repeat('x', 1018)));
+create procedure make_t1_indexes()
+begin
+create index it1i1 on t1 (a1);
+create index it1i2 on t1 (a2);
+create index it1i3 on t1 (a1, a2);
+create index it1_1024i1 on t1_1024 (a1(6));
+create index it1_1024i2 on t1_1024 (a2(6));
+create index it1_1024i3 on t1_1024 (a1(6), a2(6));
+end|
+create procedure make_t2_indexes()
+begin
+create index it2i1 on t2 (b1);
+create index it2i2 on t2 (b2);
+create index it2i3 on t2 (b1, b2);
+create unique index it2i4 on t2 (b1, b2, b3);
+create index it2_1024i1 on t2_1024 (b1(6));
+create index it2_1024i2 on t2_1024 (b2(6));
+create index it2_1024i3 on t2_1024 (b1(6), b2(6));
+end|
+create procedure remove_t1_indexes()
+begin
+drop index it1i1 on t1;
+drop index it1i2 on t1;
+drop index it1i3 on t1;
+drop index it1_1024i1 on t1_1024;
+drop index it1_1024i2 on t1_1024;
+drop index it1_1024i3 on t1_1024;
+end|
+create procedure remove_t2_indexes()
+begin
+drop index it2i1 on t2;
+drop index it2i2 on t2;
+drop index it2i3 on t2;
+drop index it2i4 on t2;
+drop index it2_1024i1 on t2_1024;
+drop index it2_1024i2 on t2_1024;
+drop index it2_1024i3 on t2_1024;
+end|
+create procedure add_materialization_data()
+begin
+insert into t1 values ('1 - 03', '2 - 03', '3 - 03', 3);
+insert into t1 values ('1 - 04', '2 - 04', '3 - 04', 4);
+insert into t1 values ('1 - 05', '2 - 05', '3 - 05', 5);
+insert into t1 values ('1 - 06', '2 - 06', '3 - 06', 6);
+insert into t1 values ('1 - 07', '2 - 07', '3 - 07', 7);
+insert into t1_1024 values (concat('1 - 03', repeat('x', 1018)), concat('2 - 03', repeat('x', 1018)));
+end|
+create procedure delete_materialization_data()
+begin
+delete from t1 where a1 >= '1 - 03';
+delete from t1_1024 where a1 >= '1 - 03';
+end|
+create procedure set_all_columns_not_null()
+begin
+alter table t1 modify a1 char(8) not null, modify a2 char(8) not null, modify a3 char(8) not null;
+alter table t2 modify b1 char(8) not null, modify b2 char(8) not null, modify b3 char(8) not null;
+end|
+create procedure set_all_columns_nullable()
+begin
+alter table t1 modify a1 char(8) null, modify a2 char(8) null, modify a3 char(8) null;
+alter table t2 modify b1 char(8) null, modify b2 char(8) null, modify b3 char(8) null;
+end|
+
+/******************************************************************************
+1. Both materialization and in-to-exists are ON, make a cost-based choice.
+******************************************************************************/
+set @@optimizer_switch='materialization=on,in_to_exists=on';
+
+/* 1.1 In-to-exists is cheaper */
+call make_t1_indexes();
+/* 1.1.1 non-indexed table access */
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i2	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+3	DEPENDENT UNION	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	it1i1,it1i3	it1i1	9	const	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	Using where; Using index
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 1.1.2 indexed table access, nullabale columns. */
+call make_t2_indexes();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	9	func	2	Using index; Using where; Full scan on NULL key
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i3	9	func	2	Using index; Using where; Full scan on NULL key
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i3	9	func	2	Using index; Using where; Full scan on NULL key
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i2	it2i2	9	func	2	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	18	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i2	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	9	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	27	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	it1i1,it1i3	it1i1	9	const	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	9	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	18	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	9	NULL	1	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	27	NULL	1	Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	27	NULL	1	Using where; Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	27	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	9	const	2	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	Using where; Using index
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 1.1.3 indexed table access, non-nullabale columns. */
+call set_all_columns_not_null();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i4	8	func	1	Using index; Using where
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	16	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i4,it2i1,it2i2,it2i3	it2i4	8	func	2	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	16	NULL	3	Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i4,it2i1,it2i2,it2i3	it2i4	8	func	2	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	16	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i2	it2i2	8	func	2	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	16	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	16	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i2	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	8	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	24	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	it1i1,it1i3	it1i1	8	const	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	8	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	16	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	16	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	8	NULL	1	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	24	NULL	1	Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	24	NULL	1	Using where; Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	24	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	8	const	5	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i1,it1i2,it1i3	it1i3	16	NULL	3	Using where; Using index
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	16	NULL	3	Using where; Using index
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+call set_all_columns_nullable();
+
+/* 1.2 Materialization is cheaper */
+call add_materialization_data();
+call remove_t1_indexes();
+/* 1.2.1 non-indexed table access */
+call remove_t2_indexes();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+1 - 03	1
+1 - 04	0
+1 - 05	0
+1 - 06	0
+1 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+1 - 04	2 - 04
+1 - 05	2 - 05
+1 - 06	2 - 06
+1 - 07	2 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+3	DEPENDENT UNION	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+2 - 03
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+1 - 03
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+1 - 03
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 1.2.2 indexed table access, nullabale columns. */
+call make_t2_indexes();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	9	NULL	5	Using where; Using index
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+1 - 03	1
+1 - 04	0
+1 - 05	0
+1 - 06	0
+1 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i2,it2i3	it2i3	18	NULL	5	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i2,it2i3	it2i3	9	NULL	4	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i2	it2i2	9	NULL	5	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i2,it2i3	it2i3	18	NULL	5	Using where; Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+1 - 04	2 - 04
+1 - 05	2 - 05
+1 - 06	2 - 06
+1 - 07	2 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	9	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	27	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+2 - 03
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ref	it2i2	it2i2	9	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i2,it2i3	it2i4	27	NULL	5	Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	9	NULL	5	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	27	NULL	6	Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	27	NULL	6	Using where; Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	27	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	9	const	2	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+1 - 03
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+1 - 03
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 1.2.3 indexed table access, non-nullabale columns. */
+call set_all_columns_not_null();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	8	NULL	5	Using where; Using index
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+1 - 03	1
+1 - 04	0
+1 - 05	0
+1 - 06	0
+1 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i2,it2i3	it2i3	16	NULL	5	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i2,it2i3	it2i3	8	NULL	4	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i2	it2i2	8	NULL	5	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i2,it2i3	it2i3	16	NULL	5	Using where; Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+1 - 04	2 - 04
+1 - 05	2 - 05
+1 - 06	2 - 06
+1 - 07	2 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	8	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	24	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+2 - 03
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	ref	it2i2	it2i2	8	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i2,it2i3	it2i4	24	NULL	5	Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	8	NULL	5	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	24	NULL	6	Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	24	NULL	6	Using where; Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	24	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	8	const	5	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+1 - 03
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+1 - 03
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+call set_all_columns_nullable();
+/******************************************************************************
+2. Materialization is OFF, in-to-exists is ON, materialization is cheaper.
+******************************************************************************/
+set @@optimizer_switch='materialization=off,in_to_exists=on';
+/* 2.1 non-indexed table access */
+call remove_t2_indexes();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+1 - 03	1
+1 - 04	0
+1 - 05	0
+1 - 06	0
+1 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+1 - 04	2 - 04
+1 - 05	2 - 05
+1 - 06	2 - 06
+1 - 07	2 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+3	DEPENDENT UNION	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+2 - 03
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+1 - 03
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+1 - 03
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 2.2 indexed table access, nullabale columns. */
+call make_t2_indexes();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	9	func	2	Using index; Using where; Full scan on NULL key
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+1 - 03	1
+1 - 04	0
+1 - 05	0
+1 - 06	0
+1 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i3	9	func	2	Using index; Using where; Full scan on NULL key
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i3	9	func	2	Using index; Using where; Full scan on NULL key
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i2	it2i2	9	func	2	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	18	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+1 - 04	2 - 04
+1 - 05	2 - 05
+1 - 06	2 - 06
+1 - 07	2 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	9	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	27	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+2 - 03
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	9	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	18	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	9	NULL	1	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	27	NULL	1	Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	27	NULL	1	Using where; Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	27	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	9	const	2	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+1 - 03
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+1 - 03
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 2.3 indexed table access, non-nullabale columns. */
+call set_all_columns_not_null();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i4	8	func	1	Using index; Using where
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+1 - 03	1
+1 - 04	0
+1 - 05	0
+1 - 06	0
+1 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	ref	it2i4,it2i1,it2i2,it2i3	it2i4	8	func	2	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+2	DEPENDENT SUBQUERY	t2	ref	it2i4,it2i1,it2i2,it2i3	it2i4	8	func	2	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+1 - 03	2 - 03	1
+1 - 04	2 - 04	0
+1 - 05	2 - 05	0
+1 - 06	2 - 06	0
+1 - 07	2 - 07	0
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i2	it2i2	8	func	2	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	16	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+1 - 04	2 - 04
+1 - 05	2 - 05
+1 - 06	2 - 06
+1 - 07	2 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	8	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	24	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+2 - 03
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	8	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i2,it2i3	it2i4	16	func,func	1	Using index; Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	8	NULL	1	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	1	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+1 - 03	2 - 03	3 - 03	3
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	24	NULL	1	Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	index	NULL	it2i4	24	NULL	1	Using where; Using index
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	24	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+1 - 03x	2 - 03x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	8	const	5	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+1 - 03
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+1 - 03
+1 - 04
+1 - 05
+1 - 06
+1 - 07
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+1 - 03
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+call set_all_columns_nullable();
+/******************************************************************************
+3. Materialization is ON, in-to-exists is OFF, in-to-exists is cheaper.
+******************************************************************************/
+set @@optimizer_switch='materialization=on,in_to_exists=off';
+call delete_materialization_data();
+call make_t1_indexes();
+/* 3.1 non-indexed table access */
+call remove_t2_indexes();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i2	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+3	DEPENDENT UNION	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	it1i1,it1i3	it1i1	9	const	1	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	Using where; Using index
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 3.2 indexed table access, nullabale columns. */
+call make_t2_indexes();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using index
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	9	NULL	5	Using where; Using index
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	5	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using index
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i3	9	NULL	4	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	t2	index	NULL	it2i2	9	NULL	5	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	5	Using where; Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i2	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	9	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	27	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	it1i1,it1i3	it1i1	9	const	1	Using where; Using index
+2	SUBQUERY	t2	ref	it2i2	it2i2	9	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	t2	index	NULL	it2i4	27	NULL	5	Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	9	NULL	5	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	18	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	27	NULL	6	Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	27	NULL	6	Using where; Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	27	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	9	const	2	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	Using where; Using index
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	18	NULL	3	Using where; Using index
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	9	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+/* 3.3 indexed table access, non-nullabale columns. */
+call set_all_columns_not_null();
+
+/* A. Subqueries in the SELECT clause. */
+explain
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using index
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	8	NULL	5	Using where; Using index
+select a1, a1 in (select b1 from t2 where b1 > '0') from t1;
+a1	a1 in (select b1 from t2 where b1 > '0')
+1 - 00	0
+1 - 01	1
+1 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	16	NULL	3	Using index
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	5	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+explain
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	16	NULL	3	Using index
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i3	8	NULL	4	Using where; Using index
+select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9') from t1;
+a1	a2	(a1, a2) in (select b1, b2 from t2 where b1 > '0' and b1 < '9')
+1 - 00	2 - 00	0
+1 - 01	2 - 01	1
+1 - 02	2 - 02	1
+
+/*
+B. "Natural" examples of subqueries without grouping that
+cannot be flattened into semijoin.
+*/
+explain
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	16	NULL	3	Using where; Using index
+2	SUBQUERY	t2	index	NULL	it2i2	8	NULL	5	Using index
+select a1 from t1 where a1 in (select b2 from t2) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	16	NULL	3	Using where; Using index
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	5	Using where; Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0') or a2 < '9';
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i2	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref	it2i2	it2i2	8	func	2	Using index
+3	DEPENDENT UNION	t3	index	NULL	it2i4	24	NULL	5	Using where; Using index
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
+a2
+2 - 01
+2 - 02
+
+explain
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	it1i1,it1i3	it1i1	8	const	1	Using where; Using index
+2	SUBQUERY	t2	ref	it2i2	it2i2	8	const	1	Using index condition
+select a1 from t1 where a1 = '1 - 02' and a1 in (select max(b1) from t2 where b2 = '2 - 02');
+a1
+1 - 02
+
+explain
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i3	16	NULL	3	Using where; Using index
+2	SUBQUERY	t2	index	NULL	it2i4	24	NULL	5	Using index
+select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+
+/* C. Subqueries in the WHERE clause with GROUP BY. */
+explain
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i1	8	NULL	5	Using where; Using index
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	index	it2i4,it2i1,it2i3	it2i3	16	NULL	5	Using where; Using index
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2 having b2 <  '2 - 04');
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+1 - 02	2 - 02	3 - 02	2
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	24	NULL	6	Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+explain
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	range	NULL	it2i4	24	NULL	6	Using where; Using index for group-by
+select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = '3 - 02' group by b1, b2);
+a1	a2	a3	a4
+
+explain
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t2	range	it2i4,it2i1,it2i3	it2i4	24	NULL	2	Using where; Using index for group-by
+select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = '1 - 01' group by b1,b2,b3);
+a1	a2	a3	a4
+1 - 01	2 - 01	3 - 01	1
+
+/*
+D. Subqueries for which materialization is not possible, and the
+optimizer reverts to in-to-exists.
+*/
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i3	it2_1024i3	9	func	1	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	index_subquery	it2_1024i1,it2_1024i2,it2_1024i3	it2_1024i1	9	func	2	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+explain
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+explain
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1_1024	ALL	it1_1024i2	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2_1024	range	it2_1024i1,it2_1024i3	it2_1024i1	9	NULL	4	Using where
+select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 > '0') or a2 < '9';
+left(a1,7)	left(a2,7)
+1 - 00x	2 - 00x
+1 - 01x	2 - 01x
+
+
+/* E. Edge cases. */
+
+/* E.1 Both materialization and in_to_exists cannot be off. */
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off,in_to_exists=off';
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+ERROR HY000: At least one of the 'in_to_exists' or 'materialization' optimizer_switch flags must be 'on'.
+set @@optimizer_switch = @save_optimizer_switch;
+/* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	t2	index_subquery	it2i4,it2i1,it2i3	it2i1	8	const	5	Using index; Using where
+select '1 - 03' in (select b1 from t2 where b1 > '0');
+'1 - 03' in (select b1 from t2 where b1 > '0')
+1
+/* E.3 Subqueries without tables. */
+explain
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i1,it1i2,it1i3	it1i3	16	NULL	3	Using where; Using index
+Warnings:
+Note	1249	Select 2 was reduced during optimization
+select a1 from t1 where a1 in (select '1 - 03') or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+UNION subqueries are currently limited to only use IN-TO-EXISTS.
+explain
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select a1 from t1 where a1 in (select '1 - 03' UNION select '1 - 02');
+a1
+1 - 02
+/* E.4 optimize_cond detects FALSE where/having clause. */
+explain
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	it1i2	it1i3	16	NULL	3	Using where; Using index
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = '1 - 03' and b1 = '1 - 02' ) or a2 < '9';
+a1
+1 - 00
+1 - 01
+1 - 02
+/* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
+TODO this test produces wrong result due to missing logic to handle the case
+when JOIN::optimize detects an empty subquery result.
+explain
+select a1 from t1 where a1 in (select max(b1) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+select a1 from t1 where a1 in (select max(b1) from t2);
+a1
+
+explain
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	it1i1	8	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+select a1 from t1 where a1 in (select max(b1) from t2 where b1 = '7 - 02');
+a1
+/* E.6 make_join_select detects impossible WHERE. *
+TODO
+/* E.7 constant optimization detects "no matching row in const table". */
+TODO
+/* E.8 Impossible WHERE noticed after reading const tables. */
+explain
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select '1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0');
+'1 - 03' in (select b1 from t2 where b1 > '0' and b1 < '0')
+0
+
+/* F. UPDATE/DELETE with subqueries. */
+
+TODO
+
+call set_all_columns_nullable();
+drop procedure make_t1_indexes;
+drop procedure make_t2_indexes;
+drop procedure remove_t1_indexes;
+drop procedure remove_t2_indexes;
+drop procedure add_materialization_data;
+drop procedure delete_materialization_data;
+drop procedure set_all_columns_not_null;
+drop procedure set_all_columns_nullable;
+drop table t1, t2, t1_1024, t2_1024;
+#
+# LP BUG#643424 valgrind warning in choose_subquery_plan()
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+c2 int(11) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY c2 (c2));
+INSERT INTO t1 VALUES (1,NULL,2);
+INSERT INTO t1 VALUES (2,7,9);
+INSERT INTO t1 VALUES (9,NULL,8);
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+c2 int(11) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY c2 (c2));
+INSERT INTO t2 VALUES (1,1,7);
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off';
+SELECT pk FROM t1 WHERE (c2, c1) IN (SELECT c2, c2 FROM t2);
+pk
+set session optimizer_switch=@save_optimizer_switch;
+drop table t1, t2;
+#
+# LP BUG#652727 Crash in create_ref_for_key()
+#
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+PRIMARY KEY (pk));
+INSERT INTO t2 VALUES (10,7);
+INSERT INTO t2 VALUES (11,1);
+INSERT INTO t2 VALUES (17,NULL);
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+PRIMARY KEY (pk));
+INSERT INTO t1 VALUES (15,1);
+INSERT INTO t1 VALUES (19,NULL);
+CREATE TABLE t3 (c2 int(11) DEFAULT NULL, KEY c2 (c2));
+INSERT INTO t3 VALUES (1);
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off';
+SELECT c2
+FROM t3
+WHERE (2, 6) IN (SELECT t1.c1, t1.c1 FROM t1 STRAIGHT_JOIN t2 ON t2.pk = t1.pk);
+c2
+set session optimizer_switch=@save_optimizer_switch;
+drop table t1, t2, t3;
+#
+# LP BUG#641245 Crash in Item_equal::contains
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+c2 int(11) DEFAULT NULL,
+c3 varchar(1) DEFAULT NULL,
+c4 varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY c2 (c2),
+KEY c3 (c3,c2));
+INSERT INTO t1 VALUES (10,7,8,'v','v');
+INSERT INTO t1 VALUES (11,1,9,'r','r');
+INSERT INTO t1 VALUES (12,5,9,'a','a');
+create table t1a like t1;
+insert into t1a select * from t1;
+create table t1b like t1;
+insert into t1b select * from t1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+c1 int(11) DEFAULT NULL,
+c2 int(11) DEFAULT NULL,
+c3 varchar(1) DEFAULT NULL,
+c4 varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY c2 (c2),
+KEY c3 (c3,c2));
+INSERT INTO t2 VALUES (1,NULL,2,'w','w');
+INSERT INTO t2 VALUES (2,7,9,'m','m');
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
+EXPLAIN EXTENDED SELECT pk
+FROM t1
+WHERE c1 IN
+(SELECT t1a.c1
+FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN
+t1a ON (t1a.c2 = t1b.pk AND 2)
+WHERE t1.pk) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	index	c3	c3	9	NULL	2	100.00	Using index
+2	DEPENDENT SUBQUERY	t1b	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
+2	DEPENDENT SUBQUERY	t1a	ref	c2	c2	5	test.t1b.pk	2	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,(`test`.`t1`.`pk` and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where (`test`.`t1`.`pk` and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`)))))
+SELECT pk
+FROM t1
+WHERE c1 IN
+(SELECT t1a.c1
+FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN
+t1a ON (t1a.c2 = t1b.pk AND 2)
+WHERE t1.pk) ;
+pk
+DROP TABLE t1, t1a, t1b, t2;