bk commit into 5.0 tree (timour:1.1932) BUG#11211

[timour]

Below is the list of changes that have just been committed into a local
5.0 repository of timka. When timka 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.1932 05/06/14 19:38:12 timour@stripped +3 -0
  Fix for BUG#11211 "GROUP BY doesn't work correctly"
  
  When the GROUP BY clause contains a column reference that can be resolved to
  both an aliased column in the SELECT list, and to a column in the FROM clause,
  the group column is resolved to the column in the FROM clause (for ANSI conformance).
  However, it may be so that the user's intent is just the other way around, and he/she
  gets the query results grouped by a completely different column than expexted.
  This patch adds a warning in such cases that tells the user that there is potential
  ambiguity in the group column.

  sql/sql_select.cc
    1.334 05/06/14 19:38:05 timour@stripped +28 -13
    - Added a warning when a GROUP column is ambiguous due to that there is a
      column reference with the same name both in the SELECT and FROM clauses.
      In this case we resolve to the column in FROM clause and warn the user
      of a possible ambiguity.
    - More extensive comments.
    - Changed the function to return bool instead of int (as in other places).

  mysql-test/t/group_by.test
    1.37 05/06/14 19:38:05 timour@stripped +23 -0
    Added test for BUG#11211.

  mysql-test/r/group_by.result
    1.51 05/06/14 19:38:05 timour@stripped +24 -0
    Added test for BUG#11211.

# 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:	timour
# Host:	zmei.home
# Root:	/home/timka/mysql/src/5.0-bug-11211

--- 1.333/sql/sql_select.cc	2005-06-13 16:28:15 +03:00
+++ 1.334/sql/sql_select.cc	2005-06-14 19:38:05 +03:00
@@ -11778,11 +11778,11 @@
     ref_pointer_array.
 
   RETURN
-    0 if OK
-    1 if error occurred
+    FALSE if OK
+    TRUE  if error occurred
 */
 
-static int
+static bool
 find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
                    ORDER *order, List<Item> &fields, List<Item>
&all_fields,
                    bool is_group_field)
@@ -11799,13 +11799,13 @@
     {
       my_error(ER_BAD_FIELD_ERROR, MYF(0),
                order_item->full_name(), thd->where);
-      return 1;
+      return TRUE;
     }
     order->item= ref_pointer_array + count - 1;
     order->in_field_list= 1;
     order->counter= count;
     order->counter_used= 1;
-    return 0;
+    return FALSE;
   }
   /* Lookup the current GROUP/ORDER field in the SELECT clause. */
   uint counter;
@@ -11813,7 +11813,7 @@
   select_item= find_item_in_list(order_item, fields, &counter,
                                  REPORT_EXCEPT_NOT_FOUND, &unaliased);
   if (!select_item)
-    return 1; /* Some error occured. */
+    return TRUE; /* The item is not unique, or some other error occured. */
 
 
   /* Check whether the resolved field is not ambiguos. */
@@ -11827,7 +11827,7 @@
     */
     if (unaliased && !order_item->fixed &&
order_item->fix_fields(thd, tables,
                                                                   order->item))
-      return 1;
+      return TRUE;
 
     /* Lookup the current GROUP field in the FROM clause. */
     order_item_type= order_item->type();
@@ -11867,27 +11867,42 @@
     {
       order->item= ref_pointer_array + counter;
       order->in_field_list=1;
-      return 0;
+      return FALSE;
     }
+    else
+      /*
+        There is a field with the same name in the FROM clause. This is the field
+        that will be chosen. In this case we issue a warning so the user knows
+        that the field from the FROM clause overshadows the column reference from
+        the SELECT list.
+      */
+      push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_NON_UNIQ_ERROR,
+                          ER(ER_NON_UNIQ_ERROR), from_field->field_name,
+                          current_thd->where);
   }
 
   order->in_field_list=0;
   /*
+    The call to order_item->fix_fields() means that here we resolve 'order_item'
+    to a column from a table in the list 'tables', or to a column in some outer
+    query. Exactly because of the second case we come to this point even if
+    (select_item == not_found_item), inspite of that fix_fields() calls
+    find_item_in_list() one more time.
+
     We check order_item->fixed because Item_func_group_concat can put
     arguments for which fix_fields already was called.
-
-    'it' reassigned in if condition because fix_field can change it.
   */
   if (!order_item->fixed &&
       (order_item->fix_fields(thd, tables, order->item) ||
        (order_item= *order->item)->check_cols(1) ||
        thd->is_fatal_error))
-    return 1;					// Wrong field 
+    return TRUE; /* Wrong field. */
+
   uint el= all_fields.elements;
-  all_fields.push_front(order_item);		        // Add new field to field list
+  all_fields.push_front(order_item); /* Add new field to field list. */
   ref_pointer_array[el]= order_item;
   order->item= ref_pointer_array + el;
-  return 0;
+  return FALSE;
 }
 
 

--- 1.50/mysql-test/r/group_by.result	2005-06-07 13:07:34 +03:00
+++ 1.51/mysql-test/r/group_by.result	2005-06-14 19:38:05 +03:00
@@ -722,3 +722,27 @@
     GROUP BY hostname;
 hostname	no
 cache-dtc-af05.proxy.aol.com	1
+drop table if exists t1, t2;
+Warnings:
+Note	1051	Unknown table 't2'
+create table t1 (c1 char(3), c2 char(3));
+create table t2 (c3 char(3), c4 char(3));
+insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
+insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
+select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
+group by c2;
+c2
+aaa
+aaa
+Warnings:
+Warning	1052	Column 'c2' in group statement is ambiguous
+show warnings;
+Level	Code	Message
+Warning	1052	Column 'c2' in group statement is ambiguous
+select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
+group by t1.c1;
+c2
+aaa
+show warnings;
+Level	Code	Message
+drop table t1, t2;

--- 1.36/mysql-test/t/group_by.test	2005-06-07 12:57:40 +03:00
+++ 1.37/mysql-test/t/group_by.test	2005-06-14 19:38:05 +03:00
@@ -539,3 +539,26 @@
   WHERE hostname LIKE '%aol%'
     GROUP BY hostname;
 
+#
+# Bug#11211: Ambiguous column reference in GROUP BY.
+#
+
+drop table if exists t1, t2;
+create table t1 (c1 char(3), c2 char(3));
+create table t2 (c3 char(3), c4 char(3));
+insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
+insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
+
+# query with ambiguous column reference 'c2'
+select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
+group by c2;
+
+show warnings;
+
+# this query has no ambiguity
+select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
+group by t1.c1;
+
+show warnings;
+
+drop table t1, t2;