Fix bug lp:833702

Analysis:
Equality propagation propagated the constant '7' into
args[0] of the Item_in_optimizer that stands for the
"< ANY" predicate. At the same the min/max subquery
rewrite swapped the order of the left and right operands
of the "<" predicate, but used Item_in_subselect::left_expr.

As a result, when the <ANY predicate is executed early in the
execution phase as a contant condition, instead of a constant
right (swapped) argument of the < predicate, there was a field
(t3.a). This field had no data, since the whole predicate is
considered constant, and it is evaluated before any tables are
read. Having junk in the field row buffer produced wrong result

Solution:
Fix create_swap to pick the correct Item_in_optimizer left
argument.

1 files changed, 27 insertions, 0 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 506170849a1..6c33f8a404c 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2104,5 +2104,32 @@ NULL
 deallocate prepare st1;
 drop table t1, t2, t3;
 set optimizer_switch=@subselect4_tmp;
+#
+# LP BUG#833702 Wrong result with nested IN and singlerow subqueries and equality propagation
+#
+CREATE TABLE t2 (c int , a int, b int);
+INSERT INTO t2 VALUES (10,7,0);
+CREATE TABLE t3 (a int, b int) ;
+INSERT INTO t3 VALUES (5,0),(7,0);
+CREATE TABLE t4 (a int);
+INSERT INTO t4 VALUES (2),(8);
+set @@optimizer_switch='in_to_exists=on,materialization=off,subquery_cache=off';
+SELECT * FROM t2
+WHERE t2.b IN (SELECT b FROM t3 WHERE t3.a = t2.a AND a < SOME (SELECT * FROM t4))
+OR ( t2.c > 242 );
+c	a	b
+10	7	0
+EXPLAIN SELECT * FROM t2
+WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7);
+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	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2
+WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3.a = 7);
+c	a	b
+10	7	0
+drop table t2, t3, t4;
+set optimizer_switch=@subselect4_tmp;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;