Fix for LP BUG#824425: Prohibiting subqueries in rows for left part of IN/ALL/ANY

Fix for walk() method of subqueries: always call the method on the subquery.

1 files changed, 47 insertions, 0 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 1279fc6b8f4..108fab8bbc9 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1424,4 +1424,51 @@ CALL p1;
 ERROR 42S22: Unknown column 'f1' in 'where clause'
 DROP PROCEDURE p1;
 DROP TABLE t1, t2;
+#
+# fix of LP BUG#824425 (prohibiting subqueries in row in
+# left part of IN/ALL/ANY)
+#
+CREATE TABLE t1 ( a int) ;
+INSERT INTO t1 VALUES (20),(30);
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (3),(9);
+CREATE TABLE t3 ( a int, b int) ;
+INSERT INTO t3 VALUES (20,5),(30,6);
+set @optimizer_switch_save=@@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF';
+SELECT * FROM t1
+WHERE (
+( SELECT a FROM t2 WHERE a = 9 )
+) NOT IN (
+SELECT b
+FROM t3
+);
+a
+20
+30
+explain extended
+SELECT * FROM t1
+WHERE (
+( SELECT a FROM t2 WHERE a = 9 )
+) NOT IN (
+SELECT b
+FROM t3
+);
+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	
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`))))))))
+SELECT * FROM t1
+WHERE (
+( SELECT a FROM t2 WHERE a = 9 ),
+( SELECT a FROM t2 WHERE a = 3 )
+) NOT IN (
+SELECT b , a
+FROM t3
+);
+ERROR 42000: This version of MySQL doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY'
+set optimizer_switch=@optimizer_switch_save;
+drop table t1,t2,t3;
 set @@optimizer_switch=@subselect3_tmp;