diff options
| -rw-r--r-- | mysql-test/r/having.result | 19 | ||||
| -rw-r--r-- | mysql-test/t/having.test | 26 | ||||
| -rw-r--r-- | sql/sql_select.cc | 2 |
3 files changed, 46 insertions, 1 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index ccd1f0e61e7..7b4340a133b 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -158,3 +158,22 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING DROP table t1; +CREATE TABLE t1 (a int PRIMARY KEY); +CREATE TABLE t2 (b int PRIMARY KEY, a int); +CREATE TABLE t3 (b int, flag int); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1,1), (2,1), (3,1); +INSERT INTO t3(b,flag) VALUES (2, 1); +SELECT t1.a +FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b +GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0; +a +SELECT DISTINCT t1.a, MAX(t3.flag) +FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b +GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0; +a MAX(t3.flag) +SELECT DISTINCT t1.a +FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b +GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0; +a +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 8b39e3bd454..c0ce3cbace7 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -151,4 +151,30 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1; DROP table t1; +# +# Bug #29911: HAVING clause depending on constant table and evaluated to false +# + +CREATE TABLE t1 (a int PRIMARY KEY); +CREATE TABLE t2 (b int PRIMARY KEY, a int); +CREATE TABLE t3 (b int, flag int); + +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1,1), (2,1), (3,1); +INSERT INTO t3(b,flag) VALUES (2, 1); + +SELECT t1.a + FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b + GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0; + +SELECT DISTINCT t1.a, MAX(t3.flag) + FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b + GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0; + +SELECT DISTINCT t1.a + FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b + GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0; + +DROP TABLE t1,t2,t3; + # End of 4.1 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8b5664a7f96..afbffc499ad 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8118,7 +8118,7 @@ remove_duplicates(JOIN *join, TABLE *entry,List<Item> &fields, Item *having) field_count++; } - if (!field_count && !(join->select_options & OPTION_FOUND_ROWS)) + if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having) { // only const items with no OPTION_FOUND_ROWS join->unit->select_limit_cnt= 1; // Only send first row DBUG_RETURN(0); |