diff options
Diffstat (limited to 'mysql-test/main/derived_cond_pushdown.test')
-rw-r--r-- | mysql-test/main/derived_cond_pushdown.test | 110 |
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 |