From: Date: November 1 2007 5:36pm
Subject: bk commit into 5.0 tree (gkodinov:1.2558) BUG#31794
List-Archive: http://lists.mysql.com/commits/36903
X-Bug: 31794
Message-Id: <200711011636.lA1GaRT3004127@magare.gmz>

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge 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-11-01 18:36:24+02:00, gkodinov@stripped +3 -0
  Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2
  
  The HAVING clause is subject to the same rules as the SELECT list
  about using aggregated and non-aggregated columns.
  But this was not enforced when processing implicit grouping from
  using aggregate functions.
  Fixed by performing the same checks for HAVING as for SELECT.

  mysql-test/r/func_group.result@stripped, 2007-11-01 18:36:24+02:00, gkodinov@stripped +15 -0
    Bug #31794: test case

  mysql-test/t/func_group.test@stripped, 2007-11-01 18:36:24+02:00, gkodinov@stripped +19 -0
    Bug #31794: test case

  sql/sql_select.cc@stripped, 2007-11-01 18:36:24+02:00, gkodinov@stripped +10 -2
    Bug #31794: Check HAVING in addition to SELECT list

diff -Nrup a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
--- a/mysql-test/r/func_group.result	2007-10-24 11:15:07 +03:00
+++ b/mysql-test/r/func_group.result	2007-11-01 18:36:24 +02:00
@@ -1392,4 +1392,19 @@ SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2
 MIN(b)
 NULL
 DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+SET SQL_MODE=ONLY_FULL_GROUP_BY;
+SELECT a FROM t1 HAVING COUNT(*)>2;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SELECT COUNT(*), a FROM t1;
+ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+SET SQL_MODE=DEFAULT;
+SELECT a FROM t1 HAVING COUNT(*)>2;
+a
+1
+SELECT COUNT(*), a FROM t1;
+COUNT(*)	a
+4	1
+DROP TABLE t1;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
--- a/mysql-test/t/func_group.test	2007-10-24 11:15:07 +03:00
+++ b/mysql-test/t/func_group.test	2007-11-01 18:36:24 +02:00
@@ -882,5 +882,24 @@ CREATE TABLE t1 (a int, b date NOT NULL,
 SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
 DROP TABLE t1;
 
+#
+# Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2;
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+
+SET SQL_MODE=ONLY_FULL_GROUP_BY;
+--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+SELECT a FROM t1 HAVING COUNT(*)>2;
+--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
+SELECT COUNT(*), a FROM t1;
+
+SET SQL_MODE=DEFAULT;
+SELECT a FROM t1 HAVING COUNT(*)>2;
+SELECT COUNT(*), a FROM t1;
+
+DROP TABLE t1;
+
 ###
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-10-23 16:48:56 +03:00
+++ b/sql/sql_select.cc	2007-11-01 18:36:24 +02:00
@@ -565,11 +565,12 @@ JOIN::prepare(Item ***rref_pointer_array
   
 
   /*
-    Check if one one uses a not constant column with group functions
-    and no GROUP BY.
+    Check if there are references to un-aggregated columns when computing 
+    aggregate functions with implicit grouping (there is no GROUP BY).
     TODO:  Add check of calculation of GROUP functions and fields:
 	   SELECT COUNT(*)+table.col1 from table1;
   */
+  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
   {
     if (!group_list)
     {
@@ -582,6 +583,13 @@ JOIN::prepare(Item ***rref_pointer_array
 	  flag|=1;
 	else if (!(flag & 2) && !item->const_during_execution())
 	  flag|=2;
+      }
+      if (having)
+      {
+        if (having->with_sum_func)
+          flag |= 1;
+        else if (!having->const_during_execution())
+          flag |= 2;
       }
       if (flag == 3)
       {