Fixed bug mdev-10053.

The implementation of the walk method for the class Item_in_subselect
was missing. As a result the method never traversed the left operand
of any IN subquery predicate.

Item_exists_subselect::exists2in_processor() that performs the
Exist-To-In transformation calls the walk method to collect info
on outer references. As the walk method did not traverse the
left operands of the IN subqueries the outer references there
were not taken into account and some subqueries that were actually
correlated were marked as uncorrelated. It could lead to an
attempt of the materialization of such a subquery.

Also added a cleanup for some test cases merged from 5.5.

3 files changed, 58 insertions, 8 deletions

diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result
index 1d0732060b7..5deb2dfa9c5 100644
--- a/mysql-test/r/subselect_exists2in.result
+++ b/mysql-test/r/subselect_exists2in.result
@@ -884,5 +884,55 @@ a
 deallocate prepare stmt;
 drop view v1;
 drop table t1,t2;
+#
+#MDEV-10053: EXIST to IN transformation turned down
+#
+CREATE TABLE t1 (
+pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk))
+ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
+set @optimizer_switch_save=@@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
+explain extended SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DEPENDENT SUBQUERY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+4	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1276	Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1
+Note	1003	select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(exists(select 1 from `test`.`t1` `sq2` semi join (`test`.`t1`) where ((`test`.`sq2`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`pk` = `test`.`t1`.`f1`))))
+SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set optimizer_switch='exists_to_in=on';
+explain extended SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DEPENDENT SUBQUERY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+4	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1276	Field or reference 'sq1.pk' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'sq1.f1' of SELECT #3 was resolved in SELECT #1
+Note	1003	select straight_join `test`.`t1`.`f2` AS `f2` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`sq2`.`f1` from `test`.`t1` `sq2` semi join (`test`.`t1`) where ((`test`.`t1`.`pk` = `test`.`t1`.`f1`) and (<cache>(`test`.`t1`.`f1`) = `test`.`sq2`.`f1`)))))
+SELECT STRAIGHT_JOIN sq1.f2 
+FROM ( SELECT * FROM t1 ) AS sq1
+WHERE EXISTS ( SELECT * FROM t1 AS sq2 
+WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+f2
+foo
+set optimizer_switch= @optimizer_switch_save;
+DROP TABLE t1;
 # End of 10.0 tests
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index f7fddf675ea..09223d36429 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2166,7 +2166,6 @@ drop table t1;
 #
 # MDEV-12429: IN subquery used in WHERE of EXISTS subquery
 #
-set optimizer_switch='exists_to_in=off';
 CREATE TABLE t1 (
 pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
@@ -2175,6 +2174,8 @@ WHERE EXISTS ( SELECT * FROM t1 AS sq2
 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
 f2
 foo
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
 EXPLAIN
 SELECT sq1.f2    FROM t1 AS sq1
 WHERE EXISTS ( SELECT * FROM t1 AS sq2
@@ -2185,7 +2186,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
 # this checks the result set above
-set @save_optimizer_switch= @@optimizer_switch;
 set optimizer_switch= 'materialization=off,semijoin=off';
 SELECT sq1.f2    FROM t1 AS sq1
 WHERE EXISTS ( SELECT * FROM t1 AS sq2
@@ -2197,13 +2197,14 @@ DROP TABLE t1;
 #
 # MDEV-12145: IN subquery used in WHERE of EXISTS subquery
 #
-set optimizer_switch='exists_to_in=off';
 CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (4),(6);
 CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM;
 INSERT INTO t2 VALUES (8),(7),(1);
 CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM;
 INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
 SELECT * FROM t1
 WHERE EXISTS ( SELECT * FROM t2, t3
 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
@@ -2223,7 +2224,6 @@ Warnings:
 Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`))))
 # this checks the result set above
-set @save_optimizer_switch= @@optimizer_switch;
 set optimizer_switch= 'materialization=off,semijoin=off';
 SELECT * FROM t1
 WHERE EXISTS ( SELECT * FROM t2, t3
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 02c89b7d884..3f91b510c5b 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2206,7 +2206,6 @@ drop table t1;
 #
 # MDEV-12429: IN subquery used in WHERE of EXISTS subquery
 #
-set optimizer_switch='exists_to_in=off';
 CREATE TABLE t1 (
 pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
@@ -2215,6 +2214,8 @@ WHERE EXISTS ( SELECT * FROM t1 AS sq2
 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
 f2
 foo
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
 EXPLAIN
 SELECT sq1.f2    FROM t1 AS sq1
 WHERE EXISTS ( SELECT * FROM t1 AS sq2
@@ -2225,7 +2226,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	sq2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
 # this checks the result set above
-set @save_optimizer_switch= @@optimizer_switch;
 set optimizer_switch= 'materialization=off,semijoin=off';
 SELECT sq1.f2    FROM t1 AS sq1
 WHERE EXISTS ( SELECT * FROM t1 AS sq2
@@ -2237,13 +2237,14 @@ DROP TABLE t1;
 #
 # MDEV-12145: IN subquery used in WHERE of EXISTS subquery
 #
-set optimizer_switch='exists_to_in=off';
 CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (4),(6);
 CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM;
 INSERT INTO t2 VALUES (8),(7),(1);
 CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM;
 INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='exists_to_in=off';
 SELECT * FROM t1
 WHERE EXISTS ( SELECT * FROM t2, t3
 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
@@ -2263,7 +2264,6 @@ Warnings:
 Note	1276	Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`))))
 # this checks the result set above
-set @save_optimizer_switch= @@optimizer_switch;
 set optimizer_switch= 'materialization=off,semijoin=off';
 SELECT * FROM t1
 WHERE EXISTS ( SELECT * FROM t2, t3