Merge branch '10.4' into bb-10.4-mdev17096

1 files changed, 39 insertions, 0 deletions

diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test
index 3c673fb128b..2482fd91103 100644
--- a/mysql-test/main/in_subq_cond_pushdown.test
+++ b/mysql-test/main/in_subq_cond_pushdown.test
@@ -821,3 +821,42 @@ WHERE (t1.a) IN
 );
 
 DROP TABLE t1,t2,t3,t4;
+
+--echo #
+--echo # MDEV-17360: IN subquery predicate with outer reference in the left part
+--echo #             that refers to a field of a mergeable derived table
+--echo #
+
+CREATE TABLE t1 (id1 int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (1814),(0),(NULL),(1);
+
+CREATE TABLE t2 (id2 int) ENGINE=MYISAM;
+
+SELECT 1 AS r FROM t2,t1,(SELECT * FROM t1) dt1
+  WHERE NOT EXISTS (SELECT id2 FROM t2
+                      WHERE dt1.id1 IN (SELECT t2.id2 FROM t2
+                                          HAVING t2.id2 >= 1));
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-17027: IN subquery predicate with outer reference in the left part
+--echo #             conjuncted with equality predicate
+--echo #
+
+CREATE TABLE t1 (pk int, i1 int, v1 varchar(1));
+INSERT INTO t1 VALUES (3,2,'x'), (1,1,'y'), (4,2,'z');
+
+CREATE TABLE t2 (pk int, i1 int, v1 varchar(1));
+INSERT INTO t2 VALUES (5,2,'x'), (7,1,'x');
+
+CREATE TABLE t3 (pk int, i1 int, v1 varchar(1));
+INSERT INTO t3 VALUES (8,2,'x'), (7,1,'z');
+
+SELECT t3.i1 FROM t3
+  WHERE EXISTS ( SELECT t2.v1 FROM t1,t2
+                   WHERE t1.v1 = t2.v1 AND
+                         t3.i1 IN (SELECT t.i1 FROM t1 as t
+                                    GROUP BY i1 HAVING t.i1 < 3));
+
+DROP TABLE t1,t2,t3;