diff options
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/having_cond_pushdown.result | 43 | ||||
-rw-r--r-- | mysql-test/main/having_cond_pushdown.test | 31 |
2 files changed, 74 insertions, 0 deletions
diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index d7c9d936627..82a4813b156 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4733,3 +4733,46 @@ EXPLAIN } } drop table t1; +# +# MDEV-19245: Impossible WHERE should be noticed earlier +# after HAVING pushdown +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +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); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +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); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +DROP TABLE t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 5088dad734d..f1bf70627f6 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1370,3 +1370,34 @@ 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; |