Bug#46051: Incorrectly market field caused wrong result.

In a subselect all fields from outer selects are marked as dependent on
selects they are belong to. In some cases optimizer substitutes it for an
equivalent expression. For example "a_field IN (SELECT outer_field)" is
substituted with "a_field = outer_field". As we moved the outer_field to the
upper select it's not really outer anymore. But it was left marked as outer.
If exists an index over a_field optimizer choose wrong execution plan and thus
return wrong result.

Now the Item_in_subselect::single_value_transformer function removes dependent
marking from fields when a subselect is optimized away.

3 files changed, 50 insertions, 0 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 671e5d8f532..d3f7f631f70 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4452,4 +4452,30 @@ WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 DELETE FROM v3;
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
+#
+# Bug#45061: Incorrectly market field caused wrong result.
+#
+CREATE TABLE `C` (
+`int_nokey` int(11) NOT NULL,
+`int_key` int(11) NOT NULL,
+KEY `int_key` (`int_key`)
+);
+INSERT INTO `C` VALUES (9,9), (0,0), (8,6), (3,6), (7,6), (0,4),
+(1,7), (9,4), (0,8), (9,4), (0,7), (5,5), (0,0), (8,5), (8,7),
+(5,2), (1,8), (7,0), (0,9), (9,5);
+SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
+int_nokey	int_key
+9	9
+0	0
+5	5
+0	0
+EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	20	Using where
+Warnings:
+Note	1276	Field or reference 'test.C.int_nokey' of SELECT #2 was resolved in SELECT #1
+Note	1249	Select 2 was reduced during optimization
+Note	1003	select `test`.`C`.`int_nokey` AS `int_nokey`,`test`.`C`.`int_key` AS `int_key` from `test`.`C` where (`test`.`C`.`int_nokey` = `test`.`C`.`int_key`)
+DROP TABLE C;
+# End of test for bug#45061.
 End of 5.0 tests.
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 96e5738526b..63ab2710f89 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3428,4 +3428,24 @@ DELETE FROM v3;
 DROP VIEW v1,v2,v3;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug#45061: Incorrectly market field caused wrong result.
+--echo #
+CREATE TABLE `C` (
+  `int_nokey` int(11) NOT NULL,
+  `int_key` int(11) NOT NULL,
+  KEY `int_key` (`int_key`)
+);
+
+INSERT INTO `C` VALUES (9,9), (0,0), (8,6), (3,6), (7,6), (0,4),
+(1,7), (9,4), (0,8), (9,4), (0,7), (5,5), (0,0), (8,5), (8,7),
+(5,2), (1,8), (7,0), (0,9), (9,5);
+
+SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
+EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
+
+DROP TABLE C;
+--echo # End of test for bug#45061.
+
+
 --echo End of 5.0 tests.
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 13eeba3ea27..805669b3cfa 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1145,6 +1145,10 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       else
       {
 	// it is single select without tables => possible optimization
+        // remove the dependence mark since the item is moved to upper
+        // select and is not outer anymore.
+        item->walk(&Item::remove_dependence_processor,
+                           (byte *) select_lex->outer_select());
 	item= func->create(left_expr, item);
 	// fix_field of item will be done in time of substituting
 	substitution= item;