From: Sergey Petrunia
Date: September 9 2008 7:36pm
Subject: bzr commit into mysql-6.0-opt branch (sergefp:2689) Bug#38072
List-Archive: http://lists.mysql.com/commits/53644
X-Bug: 38072
Message-Id: <20080909193641.46A1D15A2E6@pslp2.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

#At file:///home/spetrunia/dev/mysql-6.0-bugs/

 2689 Sergey Petrunia	2008-09-09
      BUG#38072: Wrong result: HAVING not observed in a query with aggregate
      - Make send_row_on_empty_set() return FALSE when simplify_cond() has found out
        that HAVING is always FALSE
modified:
  mysql-test/r/group_by.result
  mysql-test/t/group_by.test
  sql/sql_select.h

per-file messages:
  mysql-test/r/group_by.result
    BUG#38072: Wrong result: HAVING not observed in a query with aggregate
    - Testcase
  mysql-test/t/group_by.test
    BUG#38072: Wrong result: HAVING not observed in a query with aggregate
    - Testcase
  sql/sql_select.h
    BUG#38072: Wrong result: HAVING not observed in a query with aggregate
    - Make send_row_on_empty_set() return FALSE when simplify_cond() has found out
      that HAVING is always FALSE
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2008-04-01 15:13:57 +0000
+++ b/mysql-test/r/group_by.result	2008-09-09 19:36:23 +0000
@@ -1624,7 +1624,7 @@ b
 NULL
 1
 2
-DROP TABLE t1;
+DROP TABLE t1, t2;
 CREATE TABLE t1 ( a INT, b INT );
 SELECT b c, (SELECT a FROM t1 WHERE b = c)
 FROM t1;
@@ -1661,3 +1661,33 @@ FROM t1;
 ERROR 21000: Subquery returns more than 1 row
 DROP TABLE t1;
 SET @@sql_mode = @old_sql_mode;
+
+BUG#38072: Wrong result: HAVING not observed in a query with aggregate
+
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY int_key (int_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t1 VALUES 
+(1,5,5, 'h','h'),
+(2,1,1, '{','{'),
+(3,1,1, 'z','z'),
+(4,8,8, 'x','x'),
+(5,7,7, 'o','o'),
+(6,3,3, 'p','p'),
+(7,9,9, 'c','c'),
+(8,0,0, 'k','k'),
+(9,6,6, 't','t'),
+(10,0,0,'c','c');
+explain SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
+SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar';
+X
+drop table t1;

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2008-04-01 15:13:57 +0000
+++ b/mysql-test/t/group_by.test	2008-09-09 19:36:23 +0000
@@ -1062,7 +1062,7 @@ SELECT a from t2 GROUP BY a;
 EXPLAIN SELECT b from t2 GROUP BY b;
 SELECT b from t2 GROUP BY b;
 
-DROP TABLE t1;
+DROP TABLE t1, t2;
 
 #
 # Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
@@ -1109,5 +1109,32 @@ FROM t1;
 DROP TABLE t1;
 SET @@sql_mode = @old_sql_mode;
 
-
+--echo
+--echo BUG#38072: Wrong result: HAVING not observed in a query with aggregate
+--echo 
+CREATE TABLE t1 (
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  int_nokey int(11) NOT NULL,
+  int_key int(11) NOT NULL,
+  varchar_key varchar(1) NOT NULL,
+  varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY int_key (int_key),
+  KEY varchar_key (varchar_key)
+);
+INSERT INTO t1 VALUES 
+(1,5,5, 'h','h'),
+(2,1,1, '{','{'),
+(3,1,1, 'z','z'),
+(4,8,8, 'x','x'),
+(5,7,7, 'o','o'),
+(6,3,3, 'p','p'),
+(7,9,9, 'c','c'),
+(8,0,0, 'k','k'),
+(9,6,6, 't','t'),
+(10,0,0,'c','c');
+
+explain SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar';
+SELECT COUNT(varchar_key) AS X FROM t1 WHERE pk = 8 having 'foo'='bar';
+drop table t1;
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2008-08-16 15:22:47 +0000
+++ b/sql/sql_select.h	2008-09-09 19:36:23 +0000
@@ -684,7 +684,7 @@ public:
   bool send_row_on_empty_set()
   {
     return (do_send_rows && tmp_table_param.sum_func_count != 0 &&
-	    !group_list);
+	    !group_list && having_value != Item::COND_FALSE);
   }
   bool change_result(select_result *result);
   bool is_top_level_join() const