mirror of
https://github.com/MariaDB/server.git
synced 2026-04-07 00:46:34 +02:00
a8471426f4
Problem: When building a pushable condition that can be pushed from HAVING into WHERE, the server tries to clone aggregate functions. This is not necessary because aggregate functions can not be pushed into WHERE anyway. Fix: This fix introduces a check within `Item::build_pushable_cond` to skip cloning aggregate functions. Also fixes assert failure in MDEV-38492, by adding a missing copy method for `Item_aggregate_ref`.
1632 lines
37 KiB
Text
1632 lines
37 KiB
Text
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 #
|
|
--echo # MDEV-19520 Extend condition normalization to include 'NOT a'
|
|
--echo # having Item_func_not in item tree breaks assumptions during the
|
|
--echo # optimization phase about transformation possibilities in fix_fields().
|
|
--echo # Remove Item_func_not by extending normalization during parsing.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (0),(1);
|
|
SELECT a FROM t1 GROUP BY a HAVING NOT a;
|
|
DROP TABLE t1;
|
|
|
|
--echo End of 10.4 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-29363: Constant subquery causing a crash in pushdown optimization
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b INT, c INT);
|
|
INSERT INTO t1 VALUES (3, 3, 4), (NULL, NULL, 2);
|
|
|
|
let $q=
|
|
SELECT a,b,c FROM t1 GROUP BY a,b,c
|
|
HAVING a = (SELECT MIN(b) AS min_b FROM t1) and (a = b or a = c);
|
|
|
|
eval EXPLAIN FORMAT=JSON $q;
|
|
eval $q;
|
|
|
|
let $q=
|
|
SELECT a FROM t1 GROUP BY a,b
|
|
HAVING a = (SELECT MIN(a) AS min_a FROM t1) AND (a = 3 or a > b);
|
|
|
|
eval EXPLAIN FORMAT=JSON $q;
|
|
eval $q;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-32424: Pushdown: server crashes at JOIN::save_explain_data()
|
|
--echo # (fixed by the patch for MDEV-29363)
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b INT, c INT);
|
|
INSERT INTO t1 VALUES (1, 1, 3), (3, 2, 3);
|
|
|
|
SELECT a,b,c FROM t1 GROUP BY a,b,c
|
|
HAVING a = (SELECT MIN(b) AS min_b FROM t1) and a IN (b, c);
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-32293: Pushdown: server crashes at check_simple_equality()
|
|
--echo # (fixed by the patch for MDEV-29363)
|
|
--echo #
|
|
|
|
CREATE VIEW v1 AS SELECT 1 AS a;
|
|
|
|
SELECT * FROM v1 GROUP BY a HAVING a = 'b' AND a = (a IS NULL);
|
|
|
|
DROP VIEW v1;
|
|
|
|
--echo #
|
|
--echo # MDEV-32304: Pushdown: server crashes at Item_field::used_tables()
|
|
--echo # (fixed by the patch for MDEV-29363)
|
|
--echo #
|
|
|
|
CREATE VIEW v1 AS SELECT 1 AS a;
|
|
|
|
SELECT * FROM v1
|
|
GROUP BY a HAVING a = (a IS NULL OR a IS NULL);
|
|
|
|
DROP VIEW v1;
|
|
|
|
--echo #
|
|
--echo # MDEV-32608: Expression with constant subquery causes a crash
|
|
--echo # in pushdown from HAVING
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
INSERT INTO t1 VALUES (2, 1), (3, 2);
|
|
|
|
let $q=
|
|
SELECT * FROM t1
|
|
GROUP BY b
|
|
HAVING (SELECT MAX(b) FROM t1) = a AND a + b = 3;
|
|
|
|
eval EXPLAIN FORMAT=JSON $q;
|
|
eval $q;
|
|
|
|
let $q=
|
|
SELECT * FROM t1
|
|
GROUP BY b
|
|
HAVING (SELECT MAX(b) FROM t1) = a AND a > b;
|
|
|
|
eval EXPLAIN FORMAT=JSON $q;
|
|
eval $q;
|
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-25084: Moving equality with constant right side
|
|
--echo # from HAVING to WHERE
|
|
--echo # (fixed by the patch for MDEV-26402)
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a CHAR(3)) CHARSET=sjis;
|
|
INSERT INTO t1 VALUES ('foo'),('bar');
|
|
SELECT LOAD_FILE('') AS f, a FROM t1 GROUP BY f, a HAVING f = a;
|
|
DROP TABLE t1;
|
|
|
|
--echo End of 10.5 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-38487: Prevent aggregate functions cloning when pushing HAVING into WHERE
|
|
--echo #
|
|
|
|
CREATE TABLE t (c INT);
|
|
insert into t values (1), (2), (3);
|
|
--disable_warnings
|
|
let $q= SELECT * FROM t GROUP BY c HAVING c=c OR TIMESTAMPADD(SECOND, c, AVG(c) IS NULL IS NULL) AND c LIKE (SELECT 1);
|
|
eval explain format=JSON $q;
|
|
eval $q;
|
|
--enable_warnings
|
|
|
|
# Tests to verify all possible conditions are pushed except aggregate function
|
|
let $q= SELECT c FROM t GROUP BY c HAVING c > 1 AND AVG(c) > 0;
|
|
eval explain format=JSON $q;
|
|
eval $q;
|
|
|
|
let $q= SELECT c FROM t GROUP BY c HAVING c > 0 AND (c < 10 AND AVG(c) IS NOT NULL);
|
|
eval explain format=JSON $q;
|
|
eval $q;
|
|
|
|
let $q= SELECT c FROM t GROUP BY c HAVING c = 1 OR AVG(c) > 2;
|
|
eval explain format=JSON $q;
|
|
eval $q;
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo # End of 10.6 tests
|