summaryrefslogtreecommitdiff
path: root/mysql-test/main/derived_cond_pushdown.test
diff options
context:
space:
mode:
authorSergei Golubchik <serg@mariadb.org>2018-06-30 16:39:20 +0200
committerSergei Golubchik <serg@mariadb.org>2018-06-30 16:39:20 +0200
commit36e59752e7fc70bc5179a3d730b5ce3ee58e4e30 (patch)
tree0d3e30ce973b2e1f044931c0eb1846d7192becda /mysql-test/main/derived_cond_pushdown.test
parent7c0779da7c37f6ef6eff2f79dda6f1b0c57e3869 (diff)
parent1dd3c8f8ba49ec06e550d7376d27ff05ce024bec (diff)
downloadmariadb-git-36e59752e7fc70bc5179a3d730b5ce3ee58e4e30.tar.gz
Merge branch '10.2' into 10.3
Diffstat (limited to 'mysql-test/main/derived_cond_pushdown.test')
-rw-r--r--mysql-test/main/derived_cond_pushdown.test110
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