revision-id: c985c93b6e10c247865b4710c1b9e6623bd6cceb (mariadb-10.4.23-75-gc985c93) parent(s): 39feab3cd31b5414aa9b428eaba915c251ac34a2 author: Igor Babaev committer: Igor Babaev timestamp: 2022-04-29 10:37:01 -0700 message: MDEV-28080 Crash when using HAVING with NOT EXIST predicate in an equality MDEV-28082 Crash when using HAVING with IS NULL predicate in an equality These bugs have been fixed by the patch for MDEV-26402. Only test cases are added. --- mysql-test/main/having_cond_pushdown.result | 31 +++++++++++++++++++++++++++ mysql-test/main/having_cond_pushdown.test | 33 +++++++++++++++++++++++++++++ 2 files changed, 64 insertions(+) diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 9a9ed2b..ad8befb 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4939,3 +4939,34 @@ i Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' DROP TABLE t1; +# +# MDEV-28080: HAVING with NOT EXIST predicate in an equality +# (fixed by the patch for MDEV-26402) +# +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)); +a +1 +SELECT a FROM t1 +GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 7)); +a +0 +DROP TABLE t1, t2; +# +# MDEV-28082: HAVING with IS NULL predicate in an equality +# (fixed by the patch for MDEV-26402) +# +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); +a b +0 11 +SELECT a,b FROM t1 GROUP BY a,b HAVING a = (b IS NULL); +a b +0 11 +DROP TABLE t1; +End of 10.4 tests diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 270eac2..aee5a77 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1450,3 +1450,36 @@ SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); 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); 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