From: Date: April 14 2006 5:39pm
Subject: bk commit into 5.0 tree (evgen:1.2149) BUG#18739
List-Archive: http://lists.mysql.com/commits/4965
X-Bug: 18739
Message-Id: <20060414153909.E376B22F55D@moonbone.moonbone.local>

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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
  1.2149 06/04/14 19:39:03 evgen@stripped +3 -0
  Fixed bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode.
  
  The SQL standard doesn't allow to use in HAVING clause fields that is not found
  in GROUP BY clause and not enclosed in any aggregate function in the HAVING
  clause. However, mysql allows using of such fields to simplify users 
  calculations. This extension assume that the non-groped fields will have the 
  same group-wise values. Otherwise, the result will be unpredictable.
  This HAVING extension allowed in strict ANSI sql mode results in 
  misunderstanding of HAVING capabilities.
  
  The resolve_ref_in_select_and_group() function now doesn't search fields in
  the SELECT list if ANSI sql mode is enabled and an Item_ref from HAVING clause
  is being fixed.

  sql/item.cc
    1.215 06/04/14 19:35:56 evgen@stripped +15 -7
    Fixed bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode.
    The resolve_ref_in_select_and_group() function now doesn't search fields in
    the SELECT list if ANSI sql mode is enabled and an Item_ref from HAVING caluse
    is being fixed.

  mysql-test/r/having.result
    1.24 06/04/14 19:35:36 evgen@stripped +13 -0
    Added test case for the bug#18739:  non-standard HAVING extension was allowed in strict ANSI sql mode.

  mysql-test/t/having.test
    1.21 06/04/14 19:34:35 evgen@stripped +13 -0
    Added test case for the bug#18739:  non-standard HAVING extension was allowed in strict ANSI sql mode.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/work/18739-bug-5.0-mysql

--- 1.214/sql/item.cc	2006-04-07 02:29:09 +04:00
+++ 1.215/sql/item.cc	2006-04-14 19:35:56 +04:00
@@ -3153,17 +3153,18 @@
     both clauses contain different fields with the same names, a warning is
     issued that name of 'ref' is ambiguous. We extend ANSI SQL in that when no
     GROUP BY column is found, then a HAVING name is resolved as a possibly
-    derived SELECT column.
+    derived SELECT column. This extension is allowed only if the ANSI sql mode
+    isn't enabled.
 
   NOTES
     The resolution procedure is:
-    - Search for a column or derived column named col_ref_i [in table T_j]
-      in the SELECT clause of Q.
+    - If the ANSI mode isn't enabled then search for a column or derived column
+      named col_ref_i [in table T_j] in the SELECT clause of Q.
     - Search for a column named col_ref_i [in table T_j]
       in the GROUP BY clause of Q.
     - If found different columns with the same name in GROUP BY and SELECT
       - issue a warning and return the GROUP BY column,
-      - otherwise return the found SELECT column.
+      - otherwise return the found SELECT column
 
 
   RETURN
@@ -3183,12 +3184,19 @@
   bool not_used;
 
   /*
+    Deny search of fields in HAVING in the SELECT item list in the ANSI
+    sql mode. In other modes we allow such fields, but the result will be
+    unpredicatble if such a field will have different values among same group.
+  */
+  if (select->having_fix_field && thd->variables.sql_mode & MODE_ANSI)
+    select_ref= not_found_item;
+  /*
     Search for a column or derived column named as 'ref' in the SELECT
     clause of the current select.
   */
-  if (!(select_ref= find_item_in_list(ref, *(select->get_item_list()),
-                                      &counter, REPORT_EXCEPT_NOT_FOUND,
-                                      &not_used)))
+  if (!select_ref && !(select_ref=
+      find_item_in_list(ref, *(select->get_item_list()), &counter,
+                        REPORT_EXCEPT_NOT_FOUND, &not_used)))
     return NULL; /* Some error occurred. */
 
   /* If this is a non-aggregated field inside HAVING, search in GROUP BY. */

--- 1.23/mysql-test/r/having.result	2006-04-11 01:14:13 +04:00
+++ 1.24/mysql-test/r/having.result	2006-04-14 19:35:36 +04:00
@@ -392,3 +392,16 @@
 EMPNUM	GRADE*1000
 E3	13000
 DROP SCHEMA HU;
+USE test;
+create table t1(f1 int);
+select f1 from t1 having max(f1)=f1;
+f1
+select f1 from t1 group by f1 having max(f1)=f1;
+f1
+set session sql_mode='ansi';
+select f1 from t1 having max(f1)=f1;
+ERROR 42S22: Unknown column 'f1' in 'having clause'
+select f1 from t1 group by f1 having max(f1)=f1;
+f1
+set session sql_mode='';
+drop table t1;

--- 1.20/mysql-test/t/having.test	2006-04-11 01:14:13 +04:00
+++ 1.21/mysql-test/t/having.test	2006-04-14 19:34:35 +04:00
@@ -393,3 +393,16 @@
                            HAVING HU.PROJ.CITY = HU.STAFF.CITY);
 
 DROP SCHEMA HU;
+USE test;
+#
+# Bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode.
+#
+create table t1(f1 int);
+select f1 from t1 having max(f1)=f1;
+select f1 from t1 group by f1 having max(f1)=f1;
+set session sql_mode='ansi';
+--error 1054
+select f1 from t1 having max(f1)=f1;
+select f1 from t1 group by f1 having max(f1)=f1;
+set session sql_mode='';
+drop table t1;