diff options
Diffstat (limited to 'mysql-test/main/having_cond_pushdown.test')
| -rw-r--r-- | mysql-test/main/having_cond_pushdown.test | 1492 |
1 files changed, 1492 insertions, 0 deletions
diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test new file mode 100644 index 00000000..99e4a597 --- /dev/null +++ b/mysql-test/main/having_cond_pushdown.test @@ -0,0 +1,1492 @@ +let $no_pushdown= + set statement optimizer_switch='condition_pushdown_from_having=off' for; + +CREATE TABLE t1(a INT, b INT, c INT); +CREATE TABLE t2(x INT, y INT); + +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +INSERT INTO t2 VALUES (2,13),(5,22),(3,14),(1,22); + +CREATE VIEW v1 +AS SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a; + +CREATE FUNCTION f1() RETURNS INT RETURN 3; + +--echo # conjunctive subformula +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : using equality +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.a<4); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) AND (t1.a<4) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) OR (a IN (SELECT 3)); +eval $no_pushdown $query; +eval $query; +--enable_prepare_warnings +eval explain $query; +eval explain format=json $query; +--disable_prepare_warnings +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) OR (a IN (SELECT 3)) +GROUP BY t1.a; +--enable_prepare_warnings +eval $no_pushdown explain format=json $query; +--disable_prepare_warnings + +let $query= +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a>2) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a>2) AND (MAX(t1.b)>13)) OR ((t1.a<3) AND (MIN(t1.c)>1)); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : no aggregation formula pushdown +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.a)<3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.a)<3); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)>13); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)>13); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=3) AND (MAX(t1.a)=3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=3) +GROUP BY t1.a +HAVING (MAX(t1.a)=3); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)>12); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)>12); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MAX(t1.b)=13); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MAX(t1.b)=13); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (MIN(t1.c)<3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (MIN(t1.c)<3); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),MIN(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a=2) AND (MAX(t1.b)=13) AND (MIN(t1.c)=2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MIN(t1.c) +FROM t1 +WHERE (t1.a=2) +GROUP BY t1.a +HAVING (MAX(t1.b)=13) AND (MIN(t1.c)=2); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : no stored function pushdown +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +HAVING (t1.a>1) AND (a=test.f1()); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (a=test.f1()); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into derived table WHERE clause +let $query= +SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.a +HAVING (v1.a>1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT v1.a +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.a>1) +GROUP BY v1.a; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into derived table HAVING clause +let $query= +SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) +GROUP BY v1.c +HAVING (v1.c>2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT v1.a,v1.c +FROM t2,v1 +WHERE (t2.x=v1.a) AND (v1.c>2) +GROUP BY v1.c; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into materialized IN subquery +--echo # WHERE clause +let $query= +SELECT * FROM t1 +WHERE + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a +HAVING (t1.a>1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT * FROM t1 +WHERE + (t1.a>1) AND + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : pushdown into materialized IN subquery +--echo # HAVING clause +let $query= +SELECT * FROM t1 +WHERE + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b +HAVING (t1.b<14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT * FROM t1 +WHERE + (t1.b<14) AND + (t1.a,t1.b) IN (SELECT t2.x,MAX(t2.y) FROM t2 WHERE t2.x<5 GROUP BY t2.x) +GROUP BY t1.b; +eval $no_pushdown explain format=json $query; + +--echo # non-standard allowed queries +--echo # conjunctive subformula +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.c=2) AND (t1.a>1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE (t1.a>1) +GROUP BY t1.a +HAVING (t1.c=2); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.b=13) AND (t1.c=2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT MAX(t1.a),t1.a,t1.b,t1.c +FROM t1 +WHERE (t1.b=13) +GROUP BY t1.b +HAVING (t1.a=2) AND (t1.c=2); +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula : using equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c>1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) FROM t1 +WHERE (t1.a=t1.c) AND (t1.a>1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a=t1.c) AND (t1.c=2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.a=2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a=t1.c) AND (t1.a>1)) OR (t1.a<3) +GROUP BY t1.a +HAVING ((t1.a=t1.c) AND (t1.a>1)) OR ((t1.a<3) AND (t1.c>3)); +eval $no_pushdown explain format=json $query; + +--echo # conjuctive subformula : pushdown using WHERE multiple equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.c<3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.c<3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND-formula : pushdown using WHERE multiple equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (t1.a>1) AND (t1.c<3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (t1.a>1) AND (t1.c<3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING (((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4)) AND (t1.a<2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND (((t1.a>1) OR (t1.c<4)) AND (t1.a<2)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +eval $no_pushdown explain format=json $query; + +--echo # extracted OR-formula : pushdown using WHERE multiple equalities +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a=t1.c) AND ((t1.a>1) OR (t1.c<4)) +GROUP BY t1.a +HAVING ((t1.a>1) AND (MAX(t1.c)<3)) OR (t1.c<4); +eval $no_pushdown explain format=json $query; + +DROP TABLE t1,t2; +DROP VIEW v1; +DROP FUNCTION f1; + +--echo # +--echo # MDEV-18668: pushdown from HAVING into impossible WHERE +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); + +SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3; +EXPLAIN +SELECT a FROM t1 WHERE b = 1 AND b = 2 GROUP BY a HAVING a <= 3; + +DROP TABLE t1; + +--echo # +--echo # MDEV-18769: unfixed OR condition pushed from HAVING into WHERE +--echo # + +CREATE TABLE t1(a INT, b INT, c INT); +CREATE TABLE t3(a INT, b INT, c INT, d INT); + +INSERT INTO t1 VALUES (1,14,3), (2,13,2), (1,22,1), (3,13,4), (3,14,2); +INSERT INTO t3 VALUES (1,2,16,1), (1,3,11,2), (2,3,10,2); + +--echo # nothing to push +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING t1.b = 13 AND MAX(t1.c) > 2; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR t1.b > 15) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +--echo # extracted AND formula : equality in the inner AND formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1 OR t1.b > 10) AND (t1.b < 14 OR (t1.b > 15 AND t1.a = 2)) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +--echo # extracted OR formula +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2) OR (t1.b = 13 AND t1.a > 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.b = 13 AND t1.a > 2) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND t1.b = 13) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.b +HAVING (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2 AND t1.b = 14) OR (t1.a > 2 AND (t1.b = 13 OR t1.b = 14)) +GROUP BY t1.a,t1.b; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a < 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a < 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 2) OR (t1.a = 1 OR t1.a = 2) +GROUP BY t1.a +HAVING (t1.a = 2 AND MAX(t1.c) = 2) OR (MAX(t1.c) > 2 AND (t1.a = 1 OR t1.a = 2)); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : equality pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) AND (MAX(t1.c) = 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (MAX(t1.c) = 3); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : equalities pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 3) AND MAX(t1.b = 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +eval $no_pushdown explain format=json $query; + +--echo # conjunctive subformula : multiple equality consists of +--echo two equalities pushdown +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +GROUP BY t1.a,t1.c +HAVING (t1.a = 1) AND (t1.c = 1) AND MAX(t1.b = 14); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 1) +GROUP BY t1.a,t1.c +HAVING (MAX(t1.b) = 14); +eval $no_pushdown explain format=json $query; + +--echo # +--echo # Pushdown from HAVING into non-empty WHERE +--echo # + +--echo # inequality : inequality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a < 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a < 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : inequality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) +GROUP BY t1.a +HAVING (t1.a = 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b > 2) AND (t1.a = 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : equality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a < 3); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a < 3) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : equality in WHERE +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,t1.b,MAX(t1.c) +FROM t1 +WHERE (t1.b = 14) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : equality in WHERE, impossible WHERE +let $query= +SELECT t1.a,MAX(t1.c) +FROM t1 +WHERE (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.c) +FROM t1 +WHERE (t1.a = 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : equality in WHERE (equal through constant) +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c = 1) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # inequality : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a > 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.c > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # equality : AND formula in WHERE, impossible WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 0) AND (t1.c < 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 0) AND (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 0) AND (t1.a = 3) AND (t1.a = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.b = 2) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.a = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.b = 2) AND (t3.d = 1) AND (t3.a = 1) +GROUP BY t3.a,t3.b,t3.d; +eval $no_pushdown explain format=json $query; + +--echo # inequality : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND (t1.a < 2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a +HAVING (t1.a = 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b) +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND (t1.a = 2) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : inequality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : equality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a < 4) AND (t1.a > 0) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : inequality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : equality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.a < 4) OR (t1.a > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND ((t1.a < 4) OR (t1.a > 0)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : AND formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) AND (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) AND (t1.c < 3)) AND + (t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) AND (t1.c < 3)) AND + ((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) AND (t1.c = 3)) AND + ((t1.a < 4) AND (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b +HAVING (t3.b = 2) AND (t3.d > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) AND + (t3.b = 2) +GROUP BY t3.a,t3.b +HAVING (t3.d > 0); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) +GROUP BY t3.a,t3.b,t3.d +HAVING (t3.b = 2) AND (t3.d > 0); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a = 1) AND (t3.d = 1) AND + (t3.b = 2) AND (t3.d > 0) +GROUP BY t3.a,t3.b,t3.d; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : OR formula in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND + (t1.a < 4) +GROUP BY t1.a +HAVING (t1.c > 1); +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a > 1) OR (t1.c < 3) +GROUP BY t1.a,t1.c +HAVING (t1.a < 4) AND (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a > 1) OR (t1.c < 3)) AND + (t1.a < 4) AND (t1.c > 1) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) OR (t1.a = 3) +GROUP BY t1.a,t1.c +HAVING (t1.a = 4) OR (t1.c > 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE ((t1.a = 1) OR (t1.a = 3)) AND + ((t1.a = 4) OR (t1.c > 1)) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +--echo # equality : pushdown through equality in WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND (t1.c = 1) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : pushdown through equality +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 1) OR (t1.c = 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND + ((t1.c = 1) OR (t1.c = 2)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # OR formula : pushdown through equality, impossible WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) +GROUP BY t1.a +HAVING (t1.c = 3) OR (t1.c = 2); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.a = t1.c) AND + ((t1.c = 3) OR (t1.c = 2)) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula : pushdown through equality, impossible WHERE +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a,t1.c +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND + (t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a,t1.c; +eval $no_pushdown explain format=json $query; + +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) +GROUP BY t1.a +HAVING (t1.c = 3) AND (t1.a > 2) AND (t1.a = t1.c); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +WHERE (t1.a = 1) AND (t1.c = 3) AND + (t1.a > 2) AND (t1.a = t1.c) +GROUP BY t1.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula with OR subformula : AND condition in WHERE +let $query= +SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t3.a,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND + (t3.a = t3.d) AND ((t3.d = 1) OR (t3.d > 1)) +GROUP BY t3.a; +eval $no_pushdown explain format=json $query; + +--echo # AND formula with OR subformula : AND condition in WHERE +let $query= +SELECT t3.a,MAX(t3.b),t3.c,t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) +GROUP BY t3.a +HAVING (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1)); +eval $no_pushdown $query; +eval $query; +eval explain $query; +eval explain format=json $query; +let $query= +SELECT t3.a,t3.b,MAX(t3.c),t3.d +FROM t3 +WHERE (t3.a > 1) AND ((t3.c = 3) OR (t3.c < 2)) AND + (t3.a = t3.d) AND (((t3.d = t3.c) AND (t3.c < 15)) OR (t3.d > 1)) +GROUP BY t3.a; +eval $no_pushdown explain format=json $query; + +--echo # prepare statement +PREPARE stmt1 from " +SELECT t1.a,MAX(t1.b),t1.c +FROM t1 +GROUP BY t1.a +HAVING (t1.a = 1) +"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +DROP TABLE t1,t3; + + +--echo # +--echo # MDEV-19185: pushdown constant function with subquery +--echo # + +CREATE TABLE t1 (pk INT, c1 VARCHAR(64)); +INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc'); +CREATE VIEW v1 AS SELECT * FROM t1; + +SELECT pk +FROM t1 +GROUP BY pk +HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1))); + +DROP TABLE t1; +DROP VIEW v1; + + +--echo # +--echo # MDEV-19186: temporary table defined with view field in HAVING +--echo # + +CREATE TABLE t1 (pk INT, x VARCHAR(10)); +INSERT INTO t1 VALUES (1,'y'),(2,'s'),(3,'aaa'); +CREATE VIEW v1 AS SELECT * FROM t1; + +CREATE TABLE t2 (pk INT, x VARCHAR(10)); +INSERT INTO t2 VALUES (1,'aa'),(2,'t'),(3,'bb'); + +CREATE TABLE tmp1 +SELECT v1.pk +FROM t2,v1 +WHERE v1.x = t2.x +GROUP BY v1.pk +HAVING (v1.pk = 1); + +DROP TABLE t1,t2,tmp1; +DROP VIEW v1; + +--echo # +--echo # MDEV-19164: pushdown of condition with cached items +--echo # + +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); + +let $q1= +select d1 from t1 + group by d1 + having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; + +eval $q1; +eval explain extended $q1; +eval explain format=json $q1; + +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); + +let $q2= +select d1 from t1 + group by d1 + having d1 not between 0 AND exp(0); + +eval $q2; +eval explain extended $q2; +eval explain format=json $q2; + +drop table t1; + +--echo # +--echo # MDEV-19245: Impossible WHERE should be noticed earlier +--echo # after HAVING pushdown +--echo # + +CREATE TABLE t1 (a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1); + +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3; +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3; +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ; + +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3); +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a; + +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1); +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a; + +DROP TABLE t1; + +--echo # +--echo # MDEV-21184: Constant subquery in condition movable to WHERE +--echo # + +CREATE TABLE t1(a int, b int); +INSERT INTO t1 VALUES + (1,10), (2,20), (1,11), (1,15), (2,20), (1,10), (2,21); + +CREATE TABLE t2 (c INT); +INSERT INTO t2 VALUES (2),(3); + +let $q= +SELECT a FROM t1 GROUP BY a HAVING a = 8 OR a = ( SELECT MIN(c) FROM t2 ); + +eval EXPLAIN FORMAT=JSON $q; +eval $q; + +let $q= +SELECT a FROM t1 GROUP BY a,b + HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and b < 20; + +eval EXPLAIN FORMAT=JSON $q; +eval $q; + +let $q= +SELECT a FROM t1 GROUP BY a + HAVING ( a = 8 OR a = ( SELECT MIN(c) FROM t2 ) ) and SUM(b) > 20; + +eval EXPLAIN FORMAT=JSON $q; +eval $q; + +let $q= +SELECT a FROM t1 GROUP BY a HAVING a = ( SELECT MIN(c) FROM t2 ); + +eval EXPLAIN FORMAT=JSON $q; +eval $q; + +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order or Assertion `fixed == 1' +--echo # + +CREATE TABLE t1 (i int NOT NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); +#dublicate warning +--disable_view_protocol +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); +SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); +--enable_view_protocol +DROP TABLE t1; + +--echo # +--echo # MDEV-28080: HAVING with NOT EXIST predicate in an equality +--echo # (fixed by the patch for MDEV-26402) +--echo # + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (0), (1), (1), (0); +INSERT INTO t2 VALUES (3), (7); + +SELECT a FROM t1 + GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 1)); +SELECT a FROM t1 + GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 7)); + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-28082: HAVING with IS NULL predicate in an equality +--echo # (fixed by the patch for MDEV-26402) +--echo # + +CREATE TABLE t1 (a int, b int NOT NULL) ; +INSERT INTO t1 VALUES (1,10), (0,11), (0,11), (1,10); + +SELECT a,b FROM t1 GROUP BY a HAVING a = (b IS NULL); + +SELECT a,b FROM t1 GROUP BY a,b HAVING a = (b IS NULL); + +DROP TABLE t1; + +--echo End of 10.4 tests |