Problem was that for cases like:

SELECT ... WHERE XX IN (SELECT YY)
this was transformed to something like:
SELECT ... WHERE IF_EXISTS(SELECT ... HAVING XX=YY)

The bug was that for normal execution XX was fixed in the original outer SELECT context while in PS it was fixed in the sub query context and this confused the optimizer.

Fixed by ensuring that XX is always fixed in the outer context.

3 files changed, 74 insertions, 1 deletions

diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 7039bbfca15..8c25577eb01 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2145,6 +2145,33 @@ drop database mysqltest1;
 drop database mysqltest2;
 drop database mysqltest3;
 drop database mysqltest4;
+#
+# MDEV-7810 Wrong result on execution of a query as a PS
+# (both 1st and further executions)
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0),(8);
+SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2;
+a
+0
+8
+SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a));
+MIN(t3.a)
+0
+SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq 
+WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) );
+a
+0
+PREPARE stmt FROM "
+SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq 
+WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) )
+";
+execute stmt;
+a
+0
+execute stmt;
+a
+0
+drop table t1;
 # End of 5.5 tests
 set @subselect_mat_test_optimizer_switch_value=null;
 set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 58810670395..6c69d195bd3 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2185,4 +2185,31 @@ drop database mysqltest1;
 drop database mysqltest2;
 drop database mysqltest3;
 drop database mysqltest4;
+#
+# MDEV-7810 Wrong result on execution of a query as a PS
+# (both 1st and further executions)
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0),(8);
+SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2;
+a
+0
+8
+SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a));
+MIN(t3.a)
+0
+SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq 
+WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) );
+a
+0
+PREPARE stmt FROM "
+SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq 
+WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) )
+";
+execute stmt;
+a
+0
+execute stmt;
+a
+0
+drop table t1;
 # End of 5.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 912e9d5befd..63c72f20e21 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1841,5 +1841,24 @@ drop database mysqltest2;
 drop database mysqltest3;
 drop database mysqltest4;
 
---echo # End of 5.5 tests
+--echo #
+--echo # MDEV-7810 Wrong result on execution of a query as a PS
+--echo # (both 1st and further executions)
+
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0),(8);
+
+SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2;
+SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a));
+SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq 
+WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) );
+PREPARE stmt FROM "
+SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq 
+WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) )
+";
+execute stmt;
+execute stmt;
 
+drop table t1;
+
+--echo # End of 5.5 tests