bk commit into 5.0 tree (evgen:1.2149) BUG#18739

[eugene]

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/21 01:52:59 evgen@stripped +5 -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 are not 
  present in GROUP BY clause and not under any aggregate function in the HAVING
  clause. However, mysql allows using such fields. This extension assume that 
  the non-grouping fields will have the same group-wise values. Otherwise, the 
  result will be unpredictable. This extension allowed in strict 
  MODE_ONLY_FULL_GROUP_BY sql mode results in misunderstanding of HAVING 
  capabilities.
  
  The new error message ER_NON_GROUPING_FIELD_USED message is added. It says
  "non-grouping field '%-.64s' is used in %-.64s clause". This message is
  supposed to be used for reporting errors when some field is not found in the
  GROUP BY clause but have to be present there. Use cases for this message are 
  this bug and when a field is present in a SELECT item list not under any 
  aggregate function and there is GROUP BY clause present which doesn't mention 
  that field. It renders the ER_WRONG_FIELD_WITH_GROUP error message obsolete as
  being more descriptive.
  The resolve_ref_in_select_and_group() function now reports the 
  ER_NON_GROUPING_FIELD_FOUND error if the strict mode is set and the field for 
  HAVING clause is found in the SELECT item list only.
  

  sql/item.cc
    1.215 06/04/21 01:50:59 evgen@stripped +16 -2
    Fixed bug#18739: non-standard HAVING extension was allowed in strict ANSI sql mode.
    The resolve_ref_in_select_and_group() function now reports the
    ER_NON_GROUPING_FIELD_FOUND error if the strict MODE_ONLY_FULL_GROUP_BY mode
    is set and the field for HAVING clause is found in the SELECT item list only.

  sql/sql_select.cc
    1.405 06/04/21 01:49:30 evgen@stripped +4 -0
    Added TODO comment to change the ER_WRONG_FIELD_WITH_GROUP to more detailed
ER_NON_GROUPING_FIELD_USED message.

  mysql-test/r/having.result
    1.24 06/04/21 01:49:19 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/21 01:49:05 evgen@stripped +13 -0
    Added test case for the bug#18739:  non-standard HAVING extension was allowed in
strict ANSI sql mode.

  sql/share/errmsg.txt
    1.62 06/04/21 01:48:27 evgen@stripped +2 -0
    Added the new ER_NON_GROUPING_FIELD_USED error message for the bug#14169.

# 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-21 01:50:59 +04:00
@@ -3153,7 +3153,8 @@
     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
+    MODE_ONLY_FULL_GROUP_BY sql mode isn't enabled.
 
   NOTES
     The resolution procedure is:
@@ -3163,7 +3164,9 @@
       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
+        - if the MODE_ONLY_FULL_GROUP_BY mode is enabled return error
+        - else return the found SELECT column.
 
 
   RETURN
@@ -3208,6 +3211,17 @@
     }
   }
 
+  if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+      select_ref != not_found_item && !group_by_ref)
+  {
+    /*
+      Report the error if fields was found only in the SELECT item list and
+      the strict mode is enabled.
+    */
+    my_error(ER_NON_GROUPING_FIELD_USED, MYF(0),
+             ref->name, "HAVING");
+    return NULL;
+  }
   if (select_ref != not_found_item || group_by_ref)
   {
     if (select_ref != not_found_item && !ambiguous_fields)

--- 1.404/sql/sql_select.cc	2006-04-04 08:02:34 +04:00
+++ 1.405/sql/sql_select.cc	2006-04-21 01:49:30 +04:00
@@ -12613,6 +12613,10 @@
       if (item->type() != Item::SUM_FUNC_ITEM && !item->marker &&
 	  !item->const_item())
       {
+        /*
+          TODO: change ER_WRONG_FIELD_WITH_GROUP to more detailed
+          ER_NON_GROUPING_FIELD_USED
+        */
 	my_error(ER_WRONG_FIELD_WITH_GROUP, MYF(0), item->full_name());
 	return 1;
       }

--- 1.61/sql/share/errmsg.txt	2006-03-10 12:42:37 +03:00
+++ 1.62/sql/share/errmsg.txt	2006-04-21 01:48:27 +04:00
@@ -5611,3 +5611,5 @@
          eng "Table upgrade required. Please do \"REPAIR TABLE `%-.32s`\" to fix it!"
 ER_SP_NO_AGGREGATE 42000
 	eng "AGGREGATE is not supported for stored functions"
+ER_NON_GROUPING_FIELD_USED 42000
+	eng "non-grouping field '%-.64s' is used in %-.64s clause"

--- 1.23/mysql-test/r/having.result	2006-04-11 01:14:13 +04:00
+++ 1.24/mysql-test/r/having.result	2006-04-21 01:49:19 +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='ONLY_FULL_GROUP_BY';
+select f1 from t1 having max(f1)=f1;
+ERROR 42000: non-grouping field 'f1' is used 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-21 01:49:05 +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='ONLY_FULL_GROUP_BY';
+--error 1461
+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;