From: Date: June 15 2005 9:12am Subject: bk commit into 5.0 tree (timour:1.1940) BUG#11211 List-Archive: http://lists.mysql.com/internals/26009 X-Bug: 11211 Message-Id: <20050615071258.5BB75163257@zmei.home> 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.1940 05/06/15 10:12:49 timour@stripped +6 -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 - 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 Added test for BUG#11211. mysql-test/r/group_by.result Added test for BUG#11211. mysql-test/r/having.result 1.17 05/06/15 10:12:44 timour@stripped +1 -0 Added warning for potentially ambiguous column. mysql-test/r/alias.result 1.8 05/06/15 10:12:44 timour@stripped +2 -0 Added warning for potentially ambiguous column. BitKeeper/etc/ignore 1.203 05/06/15 10:12:44 timour@stripped +1 -0 Added ndb/src/dummy.cpp to the ignore list sql/sql_select.cc 1.336 05/06/14 19:38:05 timour@stripped[timka] +28 -13 Import patch 11211.diff mysql-test/t/group_by.test 1.37 05/06/14 19:38:05 timour@stripped[timka] +23 -0 Import patch 11211.diff mysql-test/r/group_by.result 1.51 05/06/14 19:38:05 timour@stripped[timka] +24 -0 Import patch 11211.diff # 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-dbg --- 1.335/sql/sql_select.cc 2005-06-15 01:22:24 +03:00 +++ 1.336/sql/sql_select.cc 2005-06-14 19:38:05 +03:00 @@ -11790,11 +11790,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 &fields, List &all_fields, bool is_group_field) @@ -11811,13 +11811,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; @@ -11825,7 +11825,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. */ @@ -11839,7 +11839,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(); @@ -11879,27 +11879,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.7/mysql-test/r/alias.result 2004-05-05 14:49:51 +03:00 +++ 1.8/mysql-test/r/alias.result 2005-06-15 10:12:44 +03:00 @@ -58,6 +58,8 @@ SELECT ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''), 'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat (geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma (Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma (Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl. Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse = 'Workflow' GROUP BY kundentyp ORDER BY kategorie; Kundentyp kategorie Privat (Private Nutzung) Mobilfunk +Warnings: +Warning 1052 Column 'kundentyp' in group statement is ambiguous drop table t1; CREATE TABLE t1 ( AUFNR varchar(12) NOT NULL default '', --- 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.16/mysql-test/r/having.result 2004-11-09 17:56:30 +02:00 +++ 1.17/mysql-test/r/having.result 2005-06-15 10:12:44 +03:00 @@ -304,6 +304,7 @@ 0 0 Warnings: +Warning 1052 Column 's1' in group statement is ambiguous Warning 1052 Column 's1' in having clause is ambiguous select s1*0 from t1 group by s1 having s1 = 0; s1*0 --- 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; --- 1.202/BitKeeper/etc/ignore 2005-06-07 18:19:42 +03:00 +++ 1.203/BitKeeper/etc/ignore 2005-06-15 10:12:44 +03:00 @@ -1116,3 +1116,4 @@ vio/test-sslclient vio/test-sslserver vio/viotest-ssl +ndb/src/dummy.cpp