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/r/having.result | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/r/having.result') 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; -- cgit v1.2.1