Merge branch '10.2' into 10.3

1 files changed, 74 insertions, 36 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 4a908a49add..e51646a9764 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -2176,7 +2176,7 @@ CREATE TABLE t2 (x INT, y INT, z INT);
 INSERT INTO t1 VALUES (1,1,66,1), (1,1,56,2), (3,2,42,3);
 INSERT INTO t2 VALUES (1,1,66), (1,12,32);
 
-LET $query=
+let $query=
 SELECT *
 FROM t2,
 (
@@ -2187,11 +2187,11 @@ FROM t2,
 ) AS v1
 WHERE (v1.a=1) AND (v1.b=v1.a) AND
       (v1.a=t2.x) AND (v1.max_c>30);
-EVAL $query;
-EVAL EXPLAIN $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
 
-LET $query=
+let $query=
 SELECT *
 FROM t2,
 (
@@ -2202,9 +2202,9 @@ FROM t2,
 ) AS v1
 WHERE (v1.a=1) AND (v1.b=v1.a) AND (v1.b=v1.d) AND
       (v1.a=t2.x) AND (v1.max_c>30);
-EVAL $query;
-EVAL EXPLAIN $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
 
 DROP TABLE t1,t2;
 
@@ -2230,7 +2230,7 @@ CREATE TABLE t2 (e INT, f INT, g INT);
 INSERT INTO t1 VALUES (1,14),(2,13),(1,19),(2,32),(3,24);
 INSERT INTO t2 VALUES (1,19,2),(3,24,1),(1,12,2),(3,11,3),(2,32,1);
 
-LET $query=
+let $query=
 SELECT * FROM t1
 WHERE (t1.a,t1.b) IN
   (
@@ -2244,11 +2244,11 @@ WHERE (t1.a,t1.b) IN
     WHERE d_tab.e>1
   )
 ;
-EVAL $query;
-EVAL EXPLAIN $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
 
-LET $query=
+let $query=
 SELECT * FROM t1
 WHERE (t1.a,t1.b) IN
   (
@@ -2262,11 +2262,11 @@ WHERE (t1.a,t1.b) IN
     WHERE d_tab.max_f<25
   )
 ;
-EVAL $query;
-EVAL EXPLAIN $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
 
-LET $query=
+let $query=
 SELECT * FROM t1
 WHERE (t1.a,t1.b) IN
   (
@@ -2280,11 +2280,11 @@ WHERE (t1.a,t1.b) IN
     GROUP BY d_tab.g
   )
 ;
-EVAL $query;
-EVAL EXPLAIN $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
 
-LET $query=
+let $query=
 SELECT * FROM t1
 WHERE (t1.a,t1.b) IN
   (
@@ -2298,9 +2298,9 @@ WHERE (t1.a,t1.b) IN
     GROUP BY d_tab.g
   )
 ;
-EVAL $query;
-EVAL EXPLAIN $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN $query;
+eval EXPLAIN FORMAT=JSON $query;
 
 DROP TABLE t1,t2;
 
@@ -2329,7 +2329,7 @@ CREATE TABLE t1 (a INT, b INT);
 
 INSERT INTO t1 VALUES (1,2),(2,3),(3,4);
 
-LET $query=
+let $query=
 SELECT * FROM
 (
   SELECT t1.b AS a
@@ -2337,10 +2337,10 @@ SELECT * FROM
   GROUP BY t1.a
 ) dt
 WHERE (dt.a=2);
-EVAL $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN FORMAT=JSON $query;
 
-LET $query=
+let $query=
 SELECT * FROM
 (
   SELECT t1.b AS a
@@ -2349,10 +2349,10 @@ SELECT * FROM
   HAVING (t1.a<3)
 ) dt
 WHERE (dt.a>1);
-EVAL $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN FORMAT=JSON $query;
 
-LET $query=
+let $query=
 SELECT * FROM
 (
   SELECT 'ab' AS a
@@ -2360,10 +2360,10 @@ SELECT * FROM
   GROUP BY t1.a
 ) dt
 WHERE (dt.a='ab');
-EVAL $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN FORMAT=JSON $query;
 
-LET $query=
+let $query=
 SELECT * FROM
 (
   SELECT 1 AS a
@@ -2371,9 +2371,47 @@ SELECT * FROM
   GROUP BY t1.a
 ) dt
 WHERE (dt.a=1);
-EVAL $query;
-EVAL EXPLAIN FORMAT=JSON $query;
+eval $query;
+eval EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16517: pushdown condition with the IN predicate defined
+--echo #             with non-constant values
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2),(1,3);
 
+let $query=
+SELECT * FROM
+(
+  SELECT t1.a
+  FROM t1
+  WHERE 1 IN (0,t1.a)
+  GROUP BY t1.a
+) AS dt1
+JOIN
+(
+  SELECT t1.a
+  FROM t1
+  WHERE 1 IN (0,t1.a)
+) AS dt2
+ON dt1.a = dt2.a;
+eval $query;
+eval EXPLAIN FORMAT=JSON $query;
+
+let $query=
+SELECT * FROM
+(
+  SELECT t1.a,MAX(t1.b)
+  FROM t1
+  GROUP BY t1.a
+) AS dt, t1
+WHERE dt.a=t1.a AND dt.a IN (1,t1.a);
+eval $query;
+eval EXPLAIN FORMAT=JSON $query;
 DROP TABLE t1;
 
 # Start of 10.3 tests