MWL#89

- Added regression test with queries over the WORLD database.
- Discovered and fixed several bugs in the related cost calculation
  functionality both in the semijoin and non-semijon subquery code.
- Added DBUG printing of the cost variables used to decide between
  IN-EXISTS and MATERIALIZATION.

3 files changed, 970 insertions, 3968 deletions

diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 9d6986cf092..b30154b3f99 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -1,3771 +1,561 @@
-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
+TEST GROUP 1:
+Typical cases of in-to-exists and materialization subquery strategies
+=====================================================================
+drop database if exists world;
+set names utf8;
+create database world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+);
+Make the schema and data more diverse by adding more indexes, nullable
+columns, and NULL data.
+create index SurfaceArea on Country(SurfaceArea);
+create index Language on CountryLanguage(Language);
+create index CityName on City(Name);
+alter table City change population population int(11) null default 0;
+select max(id) from City into @max_city_id;
+insert into City values (@max_city_id + 1,'Kilifarevo','BGR',NULL);
+SELECT COUNT(*) FROM Country;
+COUNT(*)
+239
+SELECT COUNT(*) FROM City;
+COUNT(*)
+4080
+SELECT COUNT(*) FROM CountryLanguage;
+COUNT(*)
+984
+set @@optimizer_switch = 'in_to_exists=on,semijoin=on,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on';
+
+1. Subquery in a disjunctive WHERE clause of the outer query.
+
+
+Q1.1m:
+MATERIALIZATION: there are too many rows in the outer query
+to be looked up in the inner table.
+EXPLAIN
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+Name LIKE 'L%') AND
+surfacearea > 1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	Country	ALL	Name,SurfaceArea	NULL	NULL	NULL	239	Using where
+2	SUBQUERY	City	ALL	Population,Country	NULL	NULL	NULL	4080	Using where
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+Name LIKE 'L%') AND
+surfacearea > 1000000;
+Name
+Algeria
+Angola
+Argentina
+Australia
+Bolivia
+Brazil
+Egypt
+South Africa
+Ethiopia
+Indonesia
+India
+Iran
+Canada
+Kazakstan
+China
+Colombia
+Congo, The Democratic Republic of the
+Libyan Arab Jamahiriya
+Mali
+Mauritania
+Mexico
+Mongolia
+Niger
+Peru
+Saudi Arabia
+Sudan
+Chad
+Russian Federation
+United States
+Q1.1e:
+IN-EXISTS: the materialization cost is the same as above, but
+there are much fewer outer rows to be looked up, thus the
+materialization cost is too high to compensate for fast lookups.
+EXPLAIN
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+Name LIKE 'L%') AND
+surfacearea > 10*1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	Country	range	Name,SurfaceArea	SurfaceArea	4	NULL	5	Using index condition; Using where; Rowid-ordered scan
+2	DEPENDENT SUBQUERY	City	index_subquery	Population,Country	Country	3	func	18	Using where
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+Name LIKE 'L%') AND
+surfacearea > 10*1000000;
+Name
+Russian Federation
+
+Q1.2m:
+MATERIALIZATION: the IN predicate is pushed (attached) to the last table
+in the join order (Country, City), therefore there are too many row
+combinations to filter by re-executing the subquery for each combination.
+EXPLAIN
+SELECT *
+FROM Country, City
+WHERE City.Country = Country.Code AND
+Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+(City.Name IN
+(select Language from CountryLanguage where Percentage > 50) OR
+City.name LIKE '%Island%');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	Country	ALL	PRIMARY,SurfaceArea	NULL	NULL	NULL	239	Using where
+1	PRIMARY	City	ref	Country	Country	3	world.Country.Code	18	Using where
+2	SUBQUERY	CountryLanguage	ALL	Percentage,Language	NULL	NULL	NULL	984	Using where
+SELECT *
+FROM Country, City
+WHERE City.Country = Country.Code AND
+Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+(City.Name IN
+(select Language from CountryLanguage where Percentage > 50) OR
+City.name LIKE '%Island%');
+Code	Name	SurfaceArea	Population	Capital	ID	Name	Country	population
+CCK	Cocos (Keeling) Islands	14.00	600	2317	2317	West Island	CCK	167
+Q1.2e:
+IN_EXISTS: join order is the same, but the left IN operand refers to
+only the first table in the join order (Country), so there are much
+fewer rows to filter by subquery re-execution.
+EXPLAIN extended
+SELECT *
+FROM Country, City
+WHERE City.Country = Country.Code AND
+Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+(Country.Name IN
+(select Language from CountryLanguage where Percentage > 50) OR
+Country.name LIKE '%Island%');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	Country	ALL	PRIMARY,SurfaceArea	NULL	NULL	NULL	239	19.67	Using where
+1	PRIMARY	City	ref	Country	Country	3	world.Country.Code	18	100.00	
+2	DEPENDENT SUBQUERY	CountryLanguage	index_subquery	Percentage,Language	Language	30	func	2	100.00	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
-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
+Note	1003	select `world`.`Country`.`Code` AS `Code`,`world`.`Country`.`Name` AS `Name`,`world`.`Country`.`SurfaceArea` AS `SurfaceArea`,`world`.`Country`.`Population` AS `Population`,`world`.`Country`.`Capital` AS `Capital`,`world`.`City`.`ID` AS `ID`,`world`.`City`.`Name` AS `Name`,`world`.`City`.`Country` AS `Country`,`world`.`City`.`population` AS `population` from `world`.`Country` join `world`.`City` where ((`world`.`City`.`Country` = `world`.`Country`.`Code`) and (`world`.`Country`.`SurfaceArea` < 3000) and (`world`.`Country`.`SurfaceArea` > 10) and (<expr_cache><`world`.`Country`.`Name`>(<in_optimizer>(`world`.`Country`.`Name`,<exists>(<index_lookup>(<cache>(`world`.`Country`.`Name`) in CountryLanguage on Language where ((`world`.`CountryLanguage`.`Percentage` > 50) and (<cache>(`world`.`Country`.`Name`) = `world`.`CountryLanguage`.`Language`)))))) or (`world`.`Country`.`Name` like '%Island%')))
+SELECT *
+FROM Country, City
+WHERE City.Country = Country.Code AND
+Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+(Country.Name IN
+(select Language from CountryLanguage where Percentage > 50) OR
+Country.name LIKE '%Island%');
+Code	Name	SurfaceArea	Population	Capital	ID	Name	Country	population
+VGB	Virgin Islands, British	151.00	21000	537	537	Road Town	VGB	8000
+CYM	Cayman Islands	264.00	38000	553	553	George Town	CYM	19600
+COK	Cook Islands	236.00	20000	583	583	Avarua	COK	11900
+FRO	Faroe Islands	1399.00	43000	901	901	Tórshavn	FRO	14542
+CXR	Christmas Island	135.00	2500	1791	1791	Flying Fish Cove	CXR	700
+KIR	Kiribati	726.00	83000	2256	2255	Bikenibeu	KIR	5055
+KIR	Kiribati	726.00	83000	2256	2256	Bairiki	KIR	2226
+CCK	Cocos (Keeling) Islands	14.00	600	2317	2316	Bantam	CCK	503
+CCK	Cocos (Keeling) Islands	14.00	600	2317	2317	West Island	CCK	167
+MHL	Marshall Islands	181.00	64000	2507	2507	Dalap-Uliga-Darrit	MHL	28000
+NRU	Nauru	21.00	12000	2728	2727	Yangor	NRU	4050
+NRU	Nauru	21.00	12000	2728	2728	Yaren	NRU	559
+NFK	Norfolk Island	36.00	2000	2806	2806	Kingston	NFK	800
+PLW	Palau	459.00	19000	2881	2881	Koror	PLW	12000
+MNP	Northern Mariana Islands	464.00	78000	2913	2913	Garapan	MNP	9200
+TCA	Turks and Caicos Islands	430.00	17000	3423	3423	Cockburn Town	TCA	4800
+TUV	Tuvalu	26.00	12000	3424	3424	Funafuti	TUV	4600
+VIR	Virgin Islands, U.S.	347.00	93000	4067	4067	Charlotte Amalie	VIR	13000
+
+Q1.3:
+For the same reasons as in Q2 IN-EXISTS and MATERIALIZATION chosen
+for each respective subquery.
+EXPLAIN
+SELECT City.Name, Country.Name
+FROM City,Country
+WHERE City.Country = Country.Code AND
+Country.SurfaceArea < 30000 AND Country.SurfaceArea > 10 AND
+((Country.Code, Country.Name) IN
+(select Country, Language from CountryLanguage where Percentage > 50) AND
+Country.Population > 3000000
+OR
+(Country.Code, City.Name) IN
+(select Country, Language from CountryLanguage));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	Country	ALL	PRIMARY,SurfaceArea	NULL	NULL	NULL	239	Using where
+1	PRIMARY	City	ref	Country	Country	3	world.Country.Code	18	Using where
+3	SUBQUERY	CountryLanguage	index	PRIMARY,Language	PRIMARY	33	NULL	984	Using index
+2	DEPENDENT SUBQUERY	CountryLanguage	unique_subquery	PRIMARY,Percentage,Language	PRIMARY	33	func,func	1	Using where
+SELECT City.Name, Country.Name
+FROM City,Country
+WHERE City.Country = Country.Code AND
+Country.SurfaceArea < 30000 AND Country.SurfaceArea > 10 AND
+((Country.Code, Country.Name) IN
+(select Country, Language from CountryLanguage where Percentage > 50) AND
+Country.Population > 3000000
+OR
+(Country.Code, City.Name) IN
+(select Country, Language from CountryLanguage));
+Name	Name
+Kigali	Rwanda
+
+2. NOT IN subqueries
+
+
+Q2.1:
+Number of cities that are not capitals in countries with small population.
+MATERIALIZATION is 50 times faster because the cost of each subquery
+re-execution is much higher than the cost of index lookups into the
+materialized subquery.
+EXPLAIN
+select count(*) from City
+where City.id not in (select capital from Country
+where capital is not null and population < 100000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	City	index	NULL	PRIMARY	4	NULL	4080	Using where; Using index
+2	SUBQUERY	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+
+Q2.2e:
+Countries that speak French, but do not speak English
+IN-EXISTS because the outer query filters many rows, thus
+there are few lookups to make.
+EXPLAIN
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+AND CountryLanguage.Language = 'French'
+  AND Code = Country;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	CountryLanguage	ref	PRIMARY,Language	Language	30	const	20	Using index condition
+1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index condition
+2	DEPENDENT SUBQUERY	CountryLanguage	unique_subquery	PRIMARY,Language	PRIMARY	33	func,const	1	Using index; Using where
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+AND CountryLanguage.Language = 'French'
+  AND Code = Country;
+Name
+France
+Saint Pierre and Miquelon
+Belgium
+Burundi
+Guadeloupe
+Haiti
+Madagascar
+Martinique
+Mayotte
+French Polynesia
+Rwanda
+Sao Tome and Principe
+Switzerland
+New Caledonia
+Lebanon
+Mauritius
+Andorra
+Italy
+Luxembourg
+Q2.2m:
+Countries that speak French OR Spanish, but do not speak English
+MATERIALIZATION because the outer query filters less rows than Q5-a,
+so there are more lookups.
+EXPLAIN
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+AND Code = Country;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	CountryLanguage	range	PRIMARY,Language	Language	30	NULL	45	Using index condition; Rowid-ordered scan
+1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index condition
+2	SUBQUERY	CountryLanguage	ref	PRIMARY,Language	Language	30	const	47	Using index condition
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+AND Code = Country;
+Name
+Andorra
+Argentina
+Bolivia
+Chile
+Costa Rica
+Dominican Republic
+Ecuador
+El Salvador
+Spain
+Guatemala
+Honduras
+Colombia
+Cuba
+Mexico
+Nicaragua
+Panama
+Paraguay
+Peru
+France
+Saint Pierre and Miquelon
+Uruguay
+Venezuela
+Belgium
+Burundi
+Guadeloupe
+Haiti
+Madagascar
+Martinique
+Mayotte
+French Polynesia
+Rwanda
+Sao Tome and Principe
+Switzerland
+New Caledonia
+Lebanon
+Mauritius
+Andorra
+Italy
+Luxembourg
+France
+Sweden
+
+Q2.3e:
+Not a very meaningful query that tests NOT IN.
+IN-EXISTS because the outer query is cheap enough to reexecute many times.
+EXPLAIN
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+(SELECT City.Name, Country.Code
+FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	CountryLanguage	index	NULL	PRIMARY	33	NULL	984	Using where; Using index
+2	DEPENDENT SUBQUERY	City	ref	CityName	CityName	35	func	2	Using index condition
+2	DEPENDENT SUBQUERY	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using index
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+(SELECT City.Name, Country.Code
+FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
+count(*)
+979
+Q2.3m:
+MATERIALIZATION with the PARTIAL_MATCH_MERGE strategy, because the HAVING
+clause prevents the use of the index on City(Name), and in practice reduces
+radically the size of the temp table.
+EXPLAIN
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+(SELECT City.Name, Country.Code
+FROM City LEFT JOIN Country ON (Country = Code)
+HAVING City.Name LIKE "Santa%");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	CountryLanguage	index	NULL	PRIMARY	33	NULL	984	Using where; Using index
+2	SUBQUERY	City	ALL	NULL	NULL	NULL	NULL	4080	
+2	SUBQUERY	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using index
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+(SELECT City.Name, Country.Code
+FROM City LEFT JOIN Country ON (Country = Code)
+HAVING City.Name LIKE "Santa%");
+count(*)
+984
+
+3. Subqueries with GROUP BY, HAVING, and aggregate functions
+
+Q3.1:
+Languages that are spoken in countries with 10 or 11 languages
+MATERIALIZATION is about 100 times faster than IN-EXISTS.
+EXPLAIN
+select count(*)
+from CountryLanguage
+where
+(Country, 10) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+WHERE Code = Country GROUP BY Code)
+OR
+(Country, 11) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+WHERE Code = Country GROUP BY Code)
+order by Country;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	CountryLanguage	index	NULL	PRIMARY	33	NULL	984	Using where; Using index
+3	SUBQUERY	CountryLanguage	index	PRIMARY	PRIMARY	33	NULL	984	Using index; Using temporary
+3	SUBQUERY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index
+2	SUBQUERY	CountryLanguage	index	PRIMARY	PRIMARY	33	NULL	984	Using index; Using temporary
+2	SUBQUERY	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index
+select count(*)
+from CountryLanguage
+where
+(Country, 10) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+WHERE Code = Country GROUP BY Code)
+OR
+(Country, 11) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+WHERE Code = Country GROUP BY Code)
+order by Country;
+count(*)
+102
+
+Q3.2:
+Countries whose capital is a city name that names more than one
+cities.
+MATERIALIZATION because the cost of single subquery execution is
+close to that of materializing the subquery.
+EXPLAIN
+select * from Country, City
+where capital = id and
+(City.name in (SELECT name FROM City
+GROUP BY name HAVING Count(*) > 2) OR
+capital is null);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	PRIMARY	City	eq_ref	PRIMARY	PRIMARY	4	world.Country.Capital	1	Using where
+2	SUBQUERY	City	index	NULL	CityName	35	NULL	4080	Using index
+select * from Country, City
+where capital = id and
+(City.name in (SELECT name FROM City
+GROUP BY name HAVING Count(*) > 2) OR
+capital is null);
+Code	Name	SurfaceArea	Population	Capital	ID	Name	Country	population
+BMU	Bermuda	53.00	65000	191	191	Hamilton	BMU	1200
+BOL	Bolivia	1098581.00	8329000	194	194	La Paz	BOL	758141
+CRI	Costa Rica	51100.00	4023000	584	584	San José	CRI	339131
+HKG	Hong Kong	1075.00	6782000	937	937	Victoria	HKG	1312637
+SYC	Seychelles	455.00	77000	3206	3206	Victoria	SYC	41000
+
+Q3.3: MATERIALIZATION is 25 times faster than IN-EXISTS
+EXPLAIN
+SELECT Name
+FROM Country
+WHERE Country.Code NOT IN
+(SELECT Country FROM City GROUP BY Name HAVING COUNT(Name) = 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+2	SUBQUERY	City	ALL	NULL	NULL	NULL	NULL	4080	Using temporary
+SELECT Name
+FROM Country
+WHERE Country.Code NOT IN
+(SELECT Country FROM City GROUP BY Name HAVING COUNT(Name) = 1);
+Name
+Antigua and Barbuda
+Costa Rica
+Montserrat
+Norfolk Island
+Seychelles
+Antarctica
+Bouvet Island
+British Indian Ocean Territory
+South Georgia and the South Sandwich Islands
+Heard Island and McDonald Islands
+French Southern territories
+United States Minor Outlying Islands
+
+4. Subqueries in the SELECT and HAVING clauses
+
+Q4.1m:
+Capital information about very big cities
+MATERIALIZATION
+EXPLAIN
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	City	range	Population	Population	5	NULL	4	Using index condition; Rowid-ordered scan
+2	SUBQUERY	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+Name	is_capital
+Mumbai (Bombay)	0
+Q4.1e:
+IN-TO-EXISTS after adding an index to make the subquery re-execution
+efficient.
+create index CountryCapital on Country(capital);
+EXPLAIN
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	City	range	Population	Population	5	NULL	4	Using index condition; Rowid-ordered scan
+2	DEPENDENT SUBQUERY	Country	index_subquery	CountryCapital	CountryCapital	5	func	2	Using index; Using where
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+Name	is_capital
+Mumbai (Bombay)	0
+drop index CountryCapital on Country;
+
+Q4.2:
+MATERIALIZATION
+EXPLAIN
+SELECT City.Name, City.Population
+FROM City JOIN Country ON City.Country = Country.Code
+GROUP BY City.Name
+HAVING City.Name IN (select Name from Country where population < 1000000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	City	ALL	Country	NULL	NULL	NULL	4080	Using temporary; Using filesort
+1	PRIMARY	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using index
+2	SUBQUERY	Country	ALL	Name	NULL	NULL	NULL	239	Using where
+SELECT City.Name, City.Population
+FROM City JOIN Country ON City.Country = Country.Code
+GROUP BY City.Name
+HAVING City.Name IN (select Name from Country where population < 1000000);
+Name	Population
+Djibouti	383000
+Gibraltar	27025
+Macao	437500
+San Marino	2294
+
+5. Subqueries with UNION
+
+Q5.1:
+EXPLAIN
+SELECT * from City where (Name, 91) in
+(SELECT Name, round(Population/1000)
+FROM City
+WHERE Country = "IND" AND Population > 2500000
+UNION
+SELECT Name, round(Population/1000)
+FROM City
+WHERE Country = "IND" AND Population < 100000);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	City	ALL	NULL	NULL	NULL	NULL	4080	Using where
+2	DEPENDENT SUBQUERY	City	ref	Population,Country,CityName	CityName	35	func	1	Using where
+3	DEPENDENT UNION	City	ref	Population,Country,CityName	CityName	35	func	1	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	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;
+SELECT * from City where (Name, 91) in
+(SELECT Name, round(Population/1000)
+FROM City
+WHERE Country = "IND" AND Population > 2500000
+UNION
+SELECT Name, round(Population/1000)
+FROM City
+WHERE Country = "IND" AND Population < 100000);
+ID	Name	Country	population
+1359	Hassan	IND	90803
+1360	Ambala Sadar	IND	90712
+1361	Baidyabati	IND	90601
+set @@optimizer_switch='default';
+drop database world;
+
+
+TEST GROUP 2:
+Tests of various combinations of optimizer switches, types of queries,
+available indexes, column nullability, constness of tables/predicates.
+=====================================================================
diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def
index c4eb6bf829e..c244d08e308 100644
--- a/mysql-test/t/disabled.def
+++ b/mysql-test/t/disabled.def
@@ -13,4 +13,3 @@ kill                     : Bug#37780 2008-12-03 HHunger need some changes to be
 query_cache_28249        : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically
 log_tables-big           : Bug#48646 2010-11-15 mattiasj report already exists
 read_many_rows_innodb    : Bug#37635 2010-11-15 mattiasj report already exists
-main.subselect_mat_cost  : MWL#89 tests that must be adjusted to the cost model introduced after the code review
diff --git a/mysql-test/t/subselect_mat_cost.test b/mysql-test/t/subselect_mat_cost.test
index ced99bccea3..7fd0fc74272 100644
--- a/mysql-test/t/subselect_mat_cost.test
+++ b/mysql-test/t/subselect_mat_cost.test
@@ -1,204 +1,417 @@
-#
-# Tets of cost-based choice between the materialization and in-to-exists
+	#
+# Tests of cost-based choice between the materialization and in-to-exists
 # subquery execution strategies (MWL#89)
 #
+# The test file is divided into two groups of tests:
+# A. Typical cases when either of the two strategies is selected:
+#    1. Subquery in disjunctive WHERE clause of the outer query.
+#    2. NOT IN subqueries
+#    3. Subqueries with GROUP BY, HAVING, and aggregate functions
+#    4. Subqueries in the SELECT and HAVING clauses
+#    5. Subqueries with UNION
+# B. Reasonably exhaustive tests of the various combinations of optimizer
+#    switches, data distribution, available indexes, and typical queries.
+#
+
+
+-- echo TEST GROUP 1:
+-- echo Typical cases of in-to-exists and materialization subquery strategies
+-- echo =====================================================================
+
+--disable_warnings
+drop database if exists world;
+--enable_warnings
+
+set names utf8;
+
+create database world;
+use world;
 
+--source include/world_schema.inc
+--disable_query_log
+--disable_result_log
 --disable_warnings
-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;
+--source include/world.inc
 --enable_warnings
+--enable_result_log
+--enable_query_log
+
+-- echo Make the schema and data more diverse by adding more indexes, nullable
+-- echo columns, and NULL data.
+create index SurfaceArea on Country(SurfaceArea);
+create index Language on CountryLanguage(Language);
+create index CityName on City(Name);
+alter table City change population population int(11) null default 0;
+
+select max(id) from City into @max_city_id;
+insert into City values (@max_city_id + 1,'Kilifarevo','BGR',NULL);
+
+
+SELECT COUNT(*) FROM Country;
+SELECT COUNT(*) FROM City;
+SELECT COUNT(*) FROM CountryLanguage;
+
+set @@optimizer_switch = 'in_to_exists=on,semijoin=on,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on';
+
+-- echo
+-- echo 1. Subquery in a disjunctive WHERE clause of the outer query.
+-- echo
+
+-- echo
+-- echo Q1.1m:
+-- echo MATERIALIZATION: there are too many rows in the outer query
+-- echo to be looked up in the inner table.
+EXPLAIN
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+       Name LIKE 'L%') AND
+      surfacearea > 1000000;
+
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+       Name LIKE 'L%') AND
+      surfacearea > 1000000;
+
+-- echo Q1.1e:
+-- echo IN-EXISTS: the materialization cost is the same as above, but
+-- echo there are much fewer outer rows to be looked up, thus the
+-- echo materialization cost is too high to compensate for fast lookups.
+EXPLAIN
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+       Name LIKE 'L%') AND
+      surfacearea > 10*1000000;
+
+SELECT Name FROM Country
+WHERE (Code IN (select Country from City where City.Population > 100000) OR
+       Name LIKE 'L%') AND
+      surfacearea > 10*1000000;
+
+-- echo
+-- echo Q1.2m:
+-- echo MATERIALIZATION: the IN predicate is pushed (attached) to the last table
+-- echo in the join order (Country, City), therefore there are too many row
+-- echo combinations to filter by re-executing the subquery for each combination.
+EXPLAIN
+SELECT *
+  FROM Country, City
+  WHERE City.Country = Country.Code AND
+        Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+        (City.Name IN
+         (select Language from CountryLanguage where Percentage > 50) OR
+         City.name LIKE '%Island%');
+
+SELECT *
+  FROM Country, City
+  WHERE City.Country = Country.Code AND
+        Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+        (City.Name IN
+         (select Language from CountryLanguage where Percentage > 50) OR
+         City.name LIKE '%Island%');
+
+-- echo Q1.2e:
+-- echo IN_EXISTS: join order is the same, but the left IN operand refers to
+-- echo only the first table in the join order (Country), so there are much
+-- echo fewer rows to filter by subquery re-execution.
+EXPLAIN extended
+SELECT *
+  FROM Country, City
+  WHERE City.Country = Country.Code AND
+        Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+        (Country.Name IN
+         (select Language from CountryLanguage where Percentage > 50) OR
+         Country.name LIKE '%Island%');
+
+SELECT *
+  FROM Country, City
+  WHERE City.Country = Country.Code AND
+        Country.SurfaceArea < 3000 AND Country.SurfaceArea > 10 AND
+        (Country.Name IN
+         (select Language from CountryLanguage where Percentage > 50) OR
+         Country.name LIKE '%Island%');
+
+
+-- echo
+-- echo Q1.3:
+-- echo For the same reasons as in Q2 IN-EXISTS and MATERIALIZATION chosen
+-- echo for each respective subquery.
+EXPLAIN
+SELECT City.Name, Country.Name
+  FROM City,Country
+  WHERE City.Country = Country.Code AND
+        Country.SurfaceArea < 30000 AND Country.SurfaceArea > 10 AND
+        ((Country.Code, Country.Name) IN
+         (select Country, Language from CountryLanguage where Percentage > 50) AND
+         Country.Population > 3000000
+         OR
+         (Country.Code, City.Name) IN
+         (select Country, Language from CountryLanguage));
+
+SELECT City.Name, Country.Name
+  FROM City,Country
+  WHERE City.Country = Country.Code AND
+        Country.SurfaceArea < 30000 AND Country.SurfaceArea > 10 AND
+        ((Country.Code, Country.Name) IN
+         (select Country, Language from CountryLanguage where Percentage > 50) AND
+         Country.Population > 3000000
+         OR
+         (Country.Code, City.Name) IN
+         (select Country, Language from CountryLanguage));
+
+
+-- echo
+-- echo 2. NOT IN subqueries
+-- echo
+
+-- echo
+-- echo Q2.1:
+-- echo Number of cities that are not capitals in countries with small population.
+-- echo MATERIALIZATION is 50 times faster because the cost of each subquery
+-- echo re-execution is much higher than the cost of index lookups into the
+-- echo materialized subquery.
+
+EXPLAIN
+select count(*) from City
+where City.id not in (select capital from Country
+                      where capital is not null and population < 100000);
+
+-- echo
+-- echo Q2.2e:
+-- echo Countries that speak French, but do not speak English
+-- echo IN-EXISTS because the outer query filters many rows, thus
+-- echo there are few lookups to make.
+EXPLAIN
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+  AND CountryLanguage.Language = 'French'
+  AND Code = Country;
+
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+  AND CountryLanguage.Language = 'French'
+  AND Code = Country;
+
+-- echo Q2.2m:
+-- echo Countries that speak French OR Spanish, but do not speak English
+-- echo MATERIALIZATION because the outer query filters less rows than Q5-a,
+-- echo so there are more lookups.
+EXPLAIN
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+  AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+  AND Code = Country;
+
+SELECT Country.Name
+FROM Country, CountryLanguage 
+WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English')
+  AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish')
+  AND Code = Country;
+
+-- echo
+-- echo Q2.3e:
+-- echo Not a very meaningful query that tests NOT IN.
+-- echo IN-EXISTS because the outer query is cheap enough to reexecute many times.
+EXPLAIN
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+      (SELECT City.Name, Country.Code
+       FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
+
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+      (SELECT City.Name, Country.Code
+       FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
+
+-- echo Q2.3m:
+-- echo MATERIALIZATION with the PARTIAL_MATCH_MERGE strategy, because the HAVING
+-- echo clause prevents the use of the index on City(Name), and in practice reduces
+-- echo radically the size of the temp table.
+EXPLAIN
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+      (SELECT City.Name, Country.Code
+       FROM City LEFT JOIN Country ON (Country = Code)
+       HAVING City.Name LIKE "Santa%");
+
+select count(*)
+from CountryLanguage
+where (Language, Country) NOT IN
+      (SELECT City.Name, Country.Code
+       FROM City LEFT JOIN Country ON (Country = Code)
+       HAVING City.Name LIKE "Santa%");
+
 
-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);
-
-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)));
-
-delimiter |;
-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|
-
-delimiter ;|
--- echo
-
--- echo /******************************************************************************
--- echo 1. Both materialization and in-to-exists are ON, make a cost-based choice.
--- echo ******************************************************************************/
-set @@optimizer_switch='materialization=on,in_to_exists=on';
--- echo 
--- echo /* 1.1 In-to-exists is cheaper */
-call make_t1_indexes();
-
--- echo /* 1.1.1 non-indexed table access */
--- source include/subselect_mat_cost.inc
-
--- echo /* 1.1.2 indexed table access, nullabale columns. */
-call make_t2_indexes();
--- source include/subselect_mat_cost.inc
-
--- echo /* 1.1.3 indexed table access, non-nullabale columns. */
-call set_all_columns_not_null();
--- source include/subselect_mat_cost.inc
-call set_all_columns_nullable();
-
--- echo 
--- echo /* 1.2 Materialization is cheaper */
-# make materialization cheaper
-call add_materialization_data();
-call remove_t1_indexes();
-
--- echo /* 1.2.1 non-indexed table access */
-call remove_t2_indexes();
--- source include/subselect_mat_cost.inc
-
--- echo /* 1.2.2 indexed table access, nullabale columns. */
-call make_t2_indexes();
--- source include/subselect_mat_cost.inc
-
--- echo /* 1.2.3 indexed table access, non-nullabale columns. */
-call set_all_columns_not_null();
--- source include/subselect_mat_cost.inc
-call set_all_columns_nullable();
-
-
-insert into t1 values ('1 - 02', '2 - 02', '3 - 02', 2);
-
--- echo /******************************************************************************
--- echo 2. Materialization is OFF, in-to-exists is ON, materialization is cheaper.
--- echo ******************************************************************************/
-set @@optimizer_switch='materialization=off,in_to_exists=on';
-
--- echo /* 2.1 non-indexed table access */
-call remove_t2_indexes();
--- source include/subselect_mat_cost.inc
-
--- echo /* 2.2 indexed table access, nullabale columns. */
-call make_t2_indexes();
--- source include/subselect_mat_cost.inc
-
--- echo /* 2.3 indexed table access, non-nullabale columns. */
-call set_all_columns_not_null();
--- source include/subselect_mat_cost.inc
-call set_all_columns_nullable();
-
-
--- echo /******************************************************************************
--- echo 3. Materialization is ON, in-to-exists is OFF, in-to-exists is cheaper.
--- echo ******************************************************************************/
-set @@optimizer_switch='materialization=on,in_to_exists=off';
-# make IN-TO-EXISTS cheaper
-call delete_materialization_data();
-call make_t1_indexes();
-
--- echo /* 3.1 non-indexed table access */
-call remove_t2_indexes();
--- source include/subselect_mat_cost.inc
-
--- echo /* 3.2 indexed table access, nullabale columns. */
-call make_t2_indexes();
--- source include/subselect_mat_cost.inc
-
--- echo /* 3.3 indexed table access, non-nullabale columns. */
-call set_all_columns_not_null();
--- source include/subselect_mat_cost.inc
-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;
+-- echo
+-- echo 3. Subqueries with GROUP BY, HAVING, and aggregate functions
+-- echo
+
+-- echo Q3.1:
+-- echo Languages that are spoken in countries with 10 or 11 languages
+-- echo MATERIALIZATION is about 100 times faster than IN-EXISTS.
+
+EXPLAIN
+select count(*)
+from CountryLanguage
+where
+(Country, 10) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+                  WHERE Code = Country GROUP BY Code)
+OR
+(Country, 11) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+                  WHERE Code = Country GROUP BY Code)
+order by Country;
+
+select count(*)
+from CountryLanguage
+where
+(Country, 10) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+                  WHERE Code = Country GROUP BY Code)
+OR
+(Country, 11) IN (SELECT Code, COUNT(*) FROM CountryLanguage, Country
+                  WHERE Code = Country GROUP BY Code)
+order by Country;
+
+
+-- echo
+-- echo Q3.2:
+-- echo Countries whose capital is a city name that names more than one
+-- echo cities.
+-- echo MATERIALIZATION because the cost of single subquery execution is
+-- echo close to that of materializing the subquery.
+
+EXPLAIN
+select * from Country, City
+where capital = id and
+      (City.name in (SELECT name FROM City
+                     GROUP BY name HAVING Count(*) > 2) OR
+       capital is null);
+
+select * from Country, City
+where capital = id and
+      (City.name in (SELECT name FROM City
+                     GROUP BY name HAVING Count(*) > 2) OR
+       capital is null);
+
+-- echo
+-- echo Q3.3: MATERIALIZATION is 25 times faster than IN-EXISTS
+
+EXPLAIN
+SELECT Name
+FROM Country
+WHERE Country.Code NOT IN
+      (SELECT Country FROM City GROUP BY Name HAVING COUNT(Name) = 1);
+
+SELECT Name
+FROM Country
+WHERE Country.Code NOT IN
+      (SELECT Country FROM City GROUP BY Name HAVING COUNT(Name) = 1);
+
+
+-- echo
+-- echo 4. Subqueries in the SELECT and HAVING clauses
+-- echo
+
+-- echo Q4.1m:
+-- echo Capital information about very big cities
+-- echo MATERIALIZATION
+EXPLAIN
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+
+-- echo Q4.1e:
+-- echo IN-TO-EXISTS after adding an index to make the subquery re-execution
+-- echo efficient.
+
+create index CountryCapital on Country(capital);
+
+EXPLAIN
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+
+select Name, City.id in (select capital from Country where capital is not null) as is_capital
+from City
+where City.population > 10000000;
+
+drop index CountryCapital on Country;
+
+-- echo
+-- echo Q4.2:
+-- echo MATERIALIZATION
+# TODO: the cost estimates for subqueries in the HAVING clause need to be changed
+# to take into account that the subquery predicate is executed #times ~ to the
+# number of groups, not number of rows
+EXPLAIN
+SELECT City.Name, City.Population
+FROM City JOIN Country ON City.Country = Country.Code
+GROUP BY City.Name
+HAVING City.Name IN (select Name from Country where population < 1000000);
+
+SELECT City.Name, City.Population
+FROM City JOIN Country ON City.Country = Country.Code
+GROUP BY City.Name
+HAVING City.Name IN (select Name from Country where population < 1000000);
+
+
+-- echo
+-- echo 5. Subqueries with UNION
+-- echo
+
+-- echo Q5.1:
+EXPLAIN
+SELECT * from City where (Name, 91) in
+(SELECT Name, round(Population/1000)
+ FROM City
+ WHERE Country = "IND" AND Population > 2500000
+UNION
+ SELECT Name, round(Population/1000)
+ FROM City
+ WHERE Country = "IND" AND Population < 100000);
+
+SELECT * from City where (Name, 91) in
+(SELECT Name, round(Population/1000)
+ FROM City
+ WHERE Country = "IND" AND Population > 2500000
+UNION
+ SELECT Name, round(Population/1000)
+ FROM City
+ WHERE Country = "IND" AND Population < 100000);
+
+set @@optimizer_switch='default';
+drop database world;
+-- echo
+
+
+-- echo
+-- echo TEST GROUP 2:
+-- echo Tests of various combinations of optimizer switches, types of queries,
+-- echo available indexes, column nullability, constness of tables/predicates.
+-- echo =====================================================================
+
+
+#TODO From Igor's review:
+#
+#2.1 Please add a case when two subqueries  are used in the where clause
+#(or in select) of a 2-way join.
+#The first subquery is accessed after the first table, while the second
+#is accessed after the second table.
+#
+#2.2. Please add a test case when one non-correlated subquery contains
+#another non-correlated subquery.
+#Consider 4 subcases:
+#   both subqueries are materialized
+#   IN_EXIST transformations are applied to both subqueries
+#   outer subquery is materialized while the inner subquery  is not
+#(IN_EXIST transformation is applied to it)
+#   inner subqyery is materialized while the outer subquery  is not (
+#IN_EXIST transformation is applied to it)