bk commit into 4.1 tree (igor:1.2676) BUG#29911

[igor]

Below is the list of changes that have just been committed into a local
4.1 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-07-20 22:51:22-07:00, igor@stripped +3 -0
  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@stripped, 2007-07-20 22:42:31-07:00, igor@stripped +19 -0
    Added a test case for bug #29911.

  mysql-test/t/having.test@stripped, 2007-07-20 22:42:31-07:00, igor@stripped +26 -0
    Added a test case for bug #29911.

  sql/sql_select.cc@stripped, 2007-07-20 22:42:31-07:00, igor@stripped +1 -1
    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.   

diff -Nrup a/mysql-test/r/having.result b/mysql-test/r/having.result
--- a/mysql-test/r/having.result	2006-05-06 23:48:09 -07:00
+++ b/mysql-test/r/having.result	2007-07-20 22:42:31 -07:00
@@ -158,3 +158,22 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP B
 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 -Nrup a/mysql-test/t/having.test b/mysql-test/t/having.test
--- a/mysql-test/t/having.test	2006-05-06 23:48:09 -07:00
+++ b/mysql-test/t/having.test	2007-07-20 22:42:31 -07:00
@@ -151,4 +151,30 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP B
 
 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 -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-05-14 23:55:16 -07:00
+++ b/sql/sql_select.cc	2007-07-20 22:42:31 -07:00
@@ -8118,7 +8118,7 @@ remove_duplicates(JOIN *join, TABLE *ent
       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);