From 72c6c789cf206977894fc531ada2c253bc0b1806 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 20 Jul 2007 22:56:19 -0700 Subject: Fixed bug #29911. This bug manifested itself for join queries with GROUP BY and HAVING clauses whose SELECT lists contained DISTINCT. It occurred when the optimizer could deduce that the result set would have not more than one row. The bug could lead to wrong result sets for queries of this type because HAVING conditions were erroneously ignored in some cases in the function remove_duplicates. mysql-test/r/having.result: Added a test case for bug #29911. mysql-test/t/having.test: Added a test case for bug #29911. --- mysql-test/t/having.test | 26 ++++++++++++++++++++++++++ 1 file changed, 26 insertions(+) (limited to 'mysql-test/t/having.test') 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 -- cgit v1.2.1