MWL#89

Merge with 5.3

1 files changed, 548 insertions, 33 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 686840630f1..53fa73aeaee 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -15,7 +15,7 @@ ORDER BY count(*);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using where; Using index; Using temporary
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-3	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 # should not crash the next statement
 SELECT 1 FROM t1
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
@@ -117,14 +117,14 @@ EXPLAIN
 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	PRIMARY	x	x	x	x	x	x	x	x
-x	SUBQUERY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
 a	b
 EXPLAIN
 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL;
 a	b
 1	NULL
@@ -147,28 +147,28 @@ EXPLAIN
 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	PRIMARY	x	x	x	x	x	x	x	x
-x	SUBQUERY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 );
 a	b
 EXPLAIN
 SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	PRIMARY	x	x	x	x	x	x	x	x
-x	SUBQUERY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
 SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 );
 c	d
 EXPLAIN
 SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	PRIMARY	x	x	x	x	x	x	x	x
-x	SUBQUERY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
 SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 );
 e	f
 EXPLAIN
 SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	PRIMARY	x	x	x	x	x	x	x	x
-x	SUBQUERY	x	x	x	x	x	x	x	x
+x	DEPENDENT SUBQUERY	x	x	x	x	x	x	x	x
 SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 );
 c	d
 SELECT * FROM t1 WHERE ( a, b ) NOT IN 
@@ -342,7 +342,7 @@ INSERT INTO t3 VALUES  ('E4','P4',40);
 INSERT INTO t3 VALUES  ('E4','P5',80);
 SET @old_optimizer_switch = @@session.optimizer_switch;
 SET @old_join_cache_level = @@session.join_cache_level;
-SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,in_to_exists=off,semijoin=on';
 SET SESSION join_cache_level = 1;
 CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
 EXPLAIN SELECT EMPNAME
@@ -565,7 +565,7 @@ INSERT INTO t3 VALUES  ('E4','P4',40);
 INSERT INTO t3 VALUES  ('E4','P5',80);
 SET @old_optimizer_switch = @@session.optimizer_switch;
 SET @old_join_cache_level = @@session.join_cache_level;
-SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
+SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,in_to_exists=off,semijoin=on';
 SET SESSION join_cache_level = 1;
 CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
 EXPLAIN SELECT EMPNAME
@@ -726,7 +726,7 @@ INSERT INTO t1 VALUES ('28','6','m'),('29','4','c');
 CREATE TABLE t2 (f11 varchar(1)) ;
 INSERT INTO t2 VALUES ('f'),('d');
 SET @old_optimizer_switch = @@session.optimizer_switch;
-SET SESSION optimizer_switch = 'materialization=on';
+SET SESSION optimizer_switch = 'materialization=on,in_to_exists=off,';
 EXPLAIN
 SELECT * FROM t1
 WHERE f3 = (
@@ -735,7 +735,7 @@ WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary
 SELECT * FROM t1
 WHERE f3 = (
 SELECT t1.f3 FROM t1
@@ -749,7 +749,7 @@ WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary
 SELECT * FROM t1
 WHERE f3 = (
 SELECT f3 FROM t1
@@ -763,7 +763,7 @@ drop table t1,t2;
 CREATE TABLE t1 (f1 int, f2 int);
 CREATE TABLE t2 (f3 int, f4 int not null, PRIMARY KEY (f3));
 set @save_optimizer_switch=@@optimizer_switch;
-SET @@optimizer_switch = 'materialization=on,semijoin=off';
+SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -851,10 +851,10 @@ EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 f1	f2
-SET @@optimizer_switch = 'materialization=off,semijoin=off';
+SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -949,25 +949,25 @@ INSERT INTO t1 VALUES (1, 2);
 INSERT INTO t1 VALUES (3, 4);
 INSERT INTO t2 VALUES (5, 6);
 INSERT INTO t2 VALUES (7, 8);
-SET @@optimizer_switch = 'materialization=on,semijoin=off';
+SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -982,14 +982,14 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -1036,29 +1036,29 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
-SET @@optimizer_switch = 'materialization=off,semijoin=off';
+SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -1073,14 +1073,14 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -1127,7 +1127,7 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -1154,20 +1154,21 @@ PRIMARY KEY (pk),
 KEY f2 (f2));
 INSERT INTO t2 VALUES (1,7,'p');
 set @save_optimizer_switch=@@optimizer_switch;
-set @@optimizer_switch='materialization=off,semijoin=off';
+set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
 WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	t2	system	PRIMARY	NULL	NULL	NULL	1	
+1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	
 2	DEPENDENT SUBQUERY	t1	index_subquery	f2	f2	4	func	2	Using index
 SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
 WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1);
 f3	MAX(t1.f2)
 NULL	NULL
-set @@optimizer_switch='materialization=on,semijoin=off';
+set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 EXPLAIN
 SELECT t1.f3, MAX(t1.f2)
 FROM t1, t2
@@ -1185,3 +1186,517 @@ TODO: add a test case for semijoin when the wrong result is fixed
 set @@optimizer_switch='materialization=off,semijoin=on';
 set @@optimizer_switch=@save_optimizer_switch;
 drop table t1, t2;
+#
+# LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
+#
+CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
+CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t2 VALUES ('k'), ('d');
+CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
+INSERT INTO t3 VALUES ('a'), ('b'), ('c');
+CREATE TABLE t4 (c1 varchar(1) primary key);
+INSERT INTO t4 VALUES ('k'), ('d');
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1
+EXPLAIN
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1	c1
+EXPLAIN
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	index	NULL	PRIMARY	3	NULL	2	Using index
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
+c1	c1
+drop table t1, t2, t3, t4;
+#
+# LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache()
+#               on EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f2 int) ;
+INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);
+CREATE TABLE t2 (f1 int, f5 int) ;
+INSERT IGNORE INTO t2 VALUES (1,0);
+CREATE TABLE t3 (f4 int) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+set @@optimizer_switch='in_to_exists=on,materialization=off,semijoin=off';
+EXPLAIN
+SELECT * FROM t2
+WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+drop table t1, t2, t3;
+#
+# LP BUG#680005 Second assertion `cache != __null' failed in
+#                sub_select_cache() on EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ;
+INSERT IGNORE INTO t1 VALUES
+('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'),
+('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'),
+('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0');
+CREATE TABLE t2 (f3 int) ;
+INSERT IGNORE INTO t2 VALUES ('7');
+CREATE TABLE t3 (f3 int) ;
+INSERT IGNORE INTO t3 VALUES ('2');
+EXPLAIN
+SELECT t1.f4
+FROM t2 JOIN t1 ON t1.f6
+WHERE
+( t1.f2 ) IN (SELECT SUBQUERY2_t1.f3
+FROM t3 AS SUBQUERY2_t1
+JOIN
+(t1 AS SUBQUERY2_t2
+JOIN
+t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1)
+ON SUBQUERY2_t3.f2)
+GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	PRIMARY	t1	index	NULL	f4	5	NULL	11	Using where
+2	DEPENDENT SUBQUERY	SUBQUERY2_t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	SUBQUERY2_t2	index	NULL	f4	5	NULL	11	Using index
+2	DEPENDENT SUBQUERY	SUBQUERY2_t3	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer (flat, BNL join)
+drop table t1, t2, t3;
+#
+# LP BUG#680038 bool close_thread_table(THD*, TABLE**):
+#               Assertion `table->key_read == 0' failed in EXPLAIN
+#
+CREATE TABLE t1 (f1 int,f3 int,f4 int) ;
+INSERT IGNORE INTO t1 VALUES (NULL,1,0);
+CREATE TABLE t2 (f2 int,f4 int,f5 int) ;
+INSERT IGNORE INTO t2 VALUES (8,0,0),(5,0,0);
+CREATE TABLE t3 (f4 int,KEY (f4)) ;
+INSERT IGNORE INTO t3 VALUES (0),(0);
+set @@optimizer_switch='semijoin=off';
+EXPLAIN
+SELECT * FROM t1 WHERE
+(SELECT f2 FROM t2
+WHERE f4 <= ALL
+(SELECT SQ1_t1.f4
+FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4
+GROUP BY SQ1_t1.f4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	SUBQUERY	SQ1_t1	index	NULL	f4	5	NULL	2	Using index; Using temporary
+3	SUBQUERY	SQ1_t3	index	NULL	f4	5	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
+drop table t1, t2, t3;
+#
+# BUG#52317: Assertion failing in Field_varstring::store() 
+# 	    at field.cc:6833
+#
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i INTEGER, KEY k(i));
+INSERT INTO t2 VALUES (1), (2);
+EXPLAIN  
+SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref_or_null	k	k	5	const	2	Using where; Using index
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# LP BUG#680846: Crash in clear_tables() with subqueries
+#
+CREATE TABLE t1 (f3 int) ;
+INSERT IGNORE INTO t1 VALUES (0),(0);
+CREATE TABLE t2 (f1 int,f3 int,f4 varchar(32)) ;
+INSERT IGNORE INTO t2 VALUES (1,0,'f');
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+COUNT(t2.f3)	f9
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9
+FROM t2 JOIN t1 ON t1.f3
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+COUNT(t2.f3)	f9
+0	2
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+GROUP BY f9;
+COUNT(t2.f3)	f9
+EXPLAIN
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT COUNT(t2.f3),
+(SELECT t2.f1 FROM t1 limit 1) AS f9
+FROM t2 JOIN t1
+WHERE ('v') IN (SELECT f4 FROM t2)
+ORDER BY f9;
+COUNT(t2.f3)	f9
+0	NULL
+drop table t1,t2;
+#
+# LP BUG#682683 Crash in create_tmp_table called from
+#               JOIN::init_execution
+#
+CREATE TABLE t2 (f1 int) ;
+INSERT INTO t2 VALUES (1),(2);
+CREATE TABLE t1 (f1 int) ;
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+field1
+NULL
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+field1
+NULL
+NULL
+INSERT INTO t1 VALUES (1),(2);
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1;
+ERROR 21000: Subquery returns more than 1 row
+EXPLAIN
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1;
+ERROR 21000: Subquery returns more than 1 row
+drop table t1,t2;
+#
+# LP BUG#680943 Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))' failed with subquery
+#
+CREATE TABLE t1 (f1 int,f3 int) ;
+INSERT IGNORE INTO t1 VALUES ('6','0'),('4','0');
+CREATE TABLE t2 (f1 int,f2 int,f3 int) ;
+INSERT IGNORE INTO t2 VALUES ('6','0','0'),('2','0','0');
+SELECT f2
+FROM (SELECT * FROM t2) AS alias1
+WHERE (SELECT SQ2_t2.f1
+FROM t1 JOIN t1 AS SQ2_t2 ON SQ2_t2.f3
+WHERE SQ2_t2.f3 AND alias1.f1)
+ORDER BY f3 ;
+f2
+drop table t1,t2;
+#
+# LP BUG#715062: Wrong result with VIEW + UNION + subquery in maria-5.3-mwl89
+#
+create table t1 (f1 int);
+create table t2 (f2 int);
+create table t3 (f3 int);
+insert into t1 values (2);
+insert into t2 values (2);
+insert into t3 values (7);
+CREATE VIEW v1 AS SELECT 2 UNION SELECT 2 ;
+CREATE VIEW v2 AS SELECT * from t1 UNION SELECT * from t2 ;
+set @save_optimizer_switch=@@optimizer_switch;
+SET @@optimizer_switch = 'in_to_exists=off,semijoin=off,materialization=on';
+EXPLAIN
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
+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
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
+bug
+EXPLAIN
+SELECT ( 5 ) IN ( SELECT * FROM v1 );
+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
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT ( 5 ) IN ( SELECT * FROM v1 );
+( 5 ) IN ( SELECT * FROM v1 )
+0
+EXPLAIN
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
+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
+3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
+bug
+EXPLAIN
+SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
+2	SUBQUERY	<derived3>	system	NULL	NULL	NULL	NULL	1	
+3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2);
+bug
+EXPLAIN
+SELECT ( 5 ) IN ( SELECT * FROM v2 );
+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
+3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT ( 5 ) IN ( SELECT * FROM v2 );
+( 5 ) IN ( SELECT * FROM v2 )
+0
+SET @@optimizer_switch = 'in_to_exists=on,semijoin=off,materialization=off';
+EXPLAIN
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
+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
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 );
+bug
+EXPLAIN
+SELECT ( 5 ) IN ( SELECT * FROM v1 );
+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
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT ( 5 ) IN ( SELECT * FROM v1 );
+( 5 ) IN ( SELECT * FROM v1 )
+0
+EXPLAIN
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
+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
+3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2);
+bug
+EXPLAIN
+SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2);
+bug
+EXPLAIN
+SELECT ( 5 ) IN ( SELECT * FROM v2 );
+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
+3	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	
+4	UNION	t2	system	NULL	NULL	NULL	NULL	1	
+NULL	UNION RESULT	<union3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT ( 5 ) IN ( SELECT * FROM v2 );
+( 5 ) IN ( SELECT * FROM v2 )
+0
+set @@optimizer_switch=@save_optimizer_switch;
+drop table t1,t2,t3;
+drop view v1,v2;
+#
+# LP BUG#715069 Wrong result with GROUP BY inside subquery and materialization=off
+#
+CREATE TABLE t0 ( f1 int(11), f2 int(11), f10 varchar(1), PRIMARY KEY (f1)) ;
+INSERT INTO t0 VALUES (8,8,'u'),(10,5,'o');
+CREATE TABLE t1 (f1a int, f2a int not null, f3a varchar(3) not null, PRIMARY KEY (f1a)) ;
+INSERT INTO t1 VALUES
+(8,8,'a1a'),
+(10,5,'b1b');
+CREATE TABLE t2 (f1b int, f2b int not null, f3b varchar(3) not null, PRIMARY KEY (f1b)) ;
+INSERT INTO t2 VALUES
+(10,5,'d1d');
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch = 'materialization=off';
+EXPLAIN
+SELECT alias2.f1 , alias2.f2
+FROM t0 AS alias1
+RIGHT JOIN t0 AS alias2 ON alias2.f10
+WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	alias1	index	NULL	PRIMARY	4	NULL	2	Using where; Using index
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	2	
+SELECT alias2.f1 , alias2.f2
+FROM t0 AS alias1
+RIGHT JOIN t0 AS alias2 ON alias2.f10
+WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 );
+f1	f2
+8	8
+EXPLAIN
+SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a);
+f1b	f2b	f3b
+10	5	d1d
+EXPLAIN
+SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a);
+f1b	f2b	f3b
+10	5	d1d
+SET @@optimizer_switch = 'materialization=on';
+EXPLAIN
+SELECT alias2.f1 , alias2.f2
+FROM t0 AS alias1
+RIGHT JOIN t0 AS alias2 ON alias2.f10
+WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	alias1	index	NULL	PRIMARY	4	NULL	2	Using where; Using index
+2	SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	2	
+SELECT alias2.f1 , alias2.f2
+FROM t0 AS alias1
+RIGHT JOIN t0 AS alias2 ON alias2.f10
+WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 );
+f1	f2
+8	8
+EXPLAIN
+SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a);
+f1b	f2b	f3b
+10	5	d1d
+EXPLAIN
+SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a);
+f1b	f2b	f3b
+10	5	d1d
+set @@optimizer_switch=@save_optimizer_switch;
+drop table t0,t1,t2;
+#                                                                                                                                                     
+# LP BUG#718593 Crash in substitute_for_best_equal_field -> eliminate_item_equal -> 
+# Item_field::find_item_equal -> Item_equal::contains
+#                                                                                                                                                     
+set @save_optimizer_switch=@@optimizer_switch;
+SET @@optimizer_switch = 'semijoin=off';
+CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ;
+INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d');
+CREATE TABLE t2 ( f12 int(11), f13 int(11)) ;
+insert into t2 values (1,2), (3,4);
+EXPLAIN
+SELECT * FROM t2
+WHERE ( f12 ) IN (
+SELECT alias2.f3
+FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11
+WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	alias1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	alias2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM t2
+WHERE ( f12 ) IN (
+SELECT alias2.f3
+FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11
+WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10
+);
+f12	f13
+EXPLAIN
+SELECT * FROM t2
+WHERE ( f12 ) IN (
+SELECT alias2.f3
+FROM t1 AS alias1, t1 AS alias2
+WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	alias1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	alias2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM t2
+WHERE ( f12 ) IN (
+SELECT alias2.f3
+FROM t1 AS alias1, t1 AS alias2
+WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10));
+f12	f13
+set @@optimizer_switch=@save_optimizer_switch;
+drop table t1, t2;