MDEV-521 fix.

After pullout item during single row subselect transformation it should be fixed properly.

7 files changed, 90 insertions, 4 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 5126e6b8d5b..2dfe139739b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6163,5 +6163,20 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 3	NULL
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
+#
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index ad56c1c2f49..343e3368eba 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6162,6 +6162,21 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 3	NULL
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
+#
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 92bd97593b3..1d1510defa0 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6158,6 +6158,21 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 3	NULL
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
+#
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 7e2ae9606ef..d80cd477fd0 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6169,6 +6169,21 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 3	NULL
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
+#
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 53b914a5af0..2a1d685710e 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6158,6 +6158,21 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 3	NULL
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
+#
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 70682842ee1..bbdf1252334 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5229,5 +5229,18 @@ set optimizer_switch=@mdev367_optimizer_switch;
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-521 single value subselect transformation problem
+--echo #
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+
+# Cleanup
+DROP TABLE t1;
+
 --echo # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index c86deecb813..6fc1a591594 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1007,11 +1007,9 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
     }
     substitution= select_lex->item_list.head();
     /*
-      as far as we moved content to upper level, field which depend of
-      'upper' select is not really dependent => we remove this dependence
+      as far as we moved content to upper level we have to fix dependences & Co
     */
-    substitution->walk(&Item::remove_dependence_processor, 0,
-		       (uchar *) select_lex->outer_select());
+    substitution->fix_after_pullout(select_lex->outer_select(), &substitution);
   }
   DBUG_RETURN(false);
 }