mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-12-07 04:45:45 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/having_cond_pushdown.test
Rex 7302e97116 MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS 
This bug is consequence of a serious architectural flaw of the query
processing at the prepare stage. Context analysis of processed queries has
to be done at this stage. The important part of this analysis is resolution
of column references also known as name resolution. The items for column
references used in a query are created by the parser. They are allocated
in the statement memory because their life span is the same as the life
span of the executed query. During name resolution some of the items are
wrapped into objects belonging to classes derived from the Item_ref class.
In many cases we can't do without such wrappers. For example, the parser
does not differentiate between references to columns of base tables and
references to columns of views. However for a reference to a view column
we need a pointer to the translation item for this column. This pointer
is stored in a Item_direct_view_ref object that wraps the item for the
column reference. Before this patch the wrappers were allocated in
execution memory good only for one execution. That wasn't a problem when
the query was executed only once. Yet for the queries executed as prepared
statements or within a stored procedure it could lead to crashes or wrong
result sets at the second execution of the query. It could happen when
the wrapped item was substituted for something else by permanent
transformations during the optimization phase.

This patch allocates all the wrappers around items created for column
references in the statement memory at the first execution of PS or at the
first call of SP/SF.

Author:  Igor Babaev
Commiter: Rex Johnston (rex.johnston@mariadb.com)
2025-06-04 05:27:47 +11:00

1605 lines
36 KiB
Text
Raw Permalink Blame History

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);
#duplicate warning
--disable_view_protocol
--disable_ps2_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_ps2_protocol
--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
Reference in a new issue View git blame Copy permalink