MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:mhansson Date:December 11 2007 11:04am
Subject:bk commit into 5.0 tree (mhansson:1.2589) BUG#32798
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin 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-12-11 12:04:00+01:00, mhansson@stripped +3 -0
  Bug#32798: DISTINCT in GROUP_CONCAT clause fails when ordering 
  by a column with null values
  
  There is only one data structure to keep track of both
  sorting and uniqueness when using 
  GROUP_CONCAT(DISTINCT <fields> ORDER BY <fields>), which
  gives wrong result in the general case.
  Fixed in 5.0 by enforcing a rule that expressions cannot 
  be used and that one list of fields must be a prefix of the other.

  mysql-test/r/func_gconcat.result@stripped, 2007-12-11 12:03:56+01:00, mhansson@stripped +29 -12
    Bug#32798: Test result

  mysql-test/t/func_gconcat.test@stripped, 2007-12-11 12:03:57+01:00, mhansson@stripped +26 -0
    Bug#32798: 
    Marked all now-forbidden uses of GROUP_CONCAT (none removed)
    Test case.

  sql/item_sum.cc@stripped, 2007-12-11 12:03:57+01:00, mhansson@stripped +14 -1
    Bug32798: 
    - The fix, checking that arg_count_field list is
      a prefix of arg_count_order.
    - Removed a function that is no longer called.
    - We now sort only on the longer list of fields.

diff -Nrup a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
--- a/mysql-test/r/func_gconcat.result	2007-10-29 11:53:09 +01:00
+++ b/mysql-test/r/func_gconcat.result	2007-12-11 12:03:56 +01:00
@@ -267,11 +267,9 @@ select group_concat(distinct s1) from t1
 group_concat(distinct s1)
 a,b,c
 select group_concat(distinct s1 order by s2) from t1 where s2 < 4;
-group_concat(distinct s1 order by s2)
-c,b,a
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
 select group_concat(distinct s1 order by s2) from t1;
-group_concat(distinct s1 order by s2)
-c,b,a,c
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
 drop table t1;
 create table t1 (a int, c int);
 insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5);
@@ -757,10 +755,7 @@ aaaaaaaaaa,bbbbbbbbb
 Warnings:
 Warning	1260	1 line(s) were cut by GROUP_CONCAT()
 SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
-GROUP_CONCAT( DISTINCT a ORDER BY b )
-aaaaaaaaaa,bbbbbbbbb
-Warnings:
-Warning	1260	1 line(s) were cut by GROUP_CONCAT()
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
 SET group_concat_max_len = DEFAULT;
 DROP TABLE t1;
 SET group_concat_max_len= 65535;
@@ -770,8 +765,7 @@ SELECT GROUP_CONCAT( a ORDER BY b ) FROM
 GROUP_CONCAT( a ORDER BY b )
 a,b
 SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
-GROUP_CONCAT(DISTINCT a ORDER BY b)
-a,b
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
 SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
 GROUP_CONCAT(DISTINCT a)
 a,b
@@ -780,8 +774,7 @@ SELECT GROUP_CONCAT(a ORDER BY b) FROM t
 GROUP_CONCAT(a ORDER BY b)
 a,b
 SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
-GROUP_CONCAT(DISTINCT a ORDER BY b)
-a,b
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
 SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
 GROUP_CONCAT(DISTINCT a)
 a,b
@@ -870,4 +863,28 @@ select group_concat(f1) from t1;
 group_concat(f1)
 ,
 drop table t1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2);
+SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1;
+GROUP_CONCAT(DISTINCT a, b ORDER BY a, b)
+11,12,21,22
+SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
+SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1;
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
+SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
+ERROR HY000: Incorrect usage of DISTINCT ORDER BY and GROUP_CONCAT
+SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1;
+GROUP_CONCAT(DISTINCT a ORDER BY a, b)
+1,2
+SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1;
+GROUP_CONCAT(DISTINCT b ORDER BY b, a)
+1,2
+SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1;
+GROUP_CONCAT(DISTINCT a, b ORDER BY a)
+12,11,22,21
+SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1;
+GROUP_CONCAT(DISTINCT b, a ORDER BY b)
+12,11,22,21
 End of 5.0 tests
diff -Nrup a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
--- a/mysql-test/t/func_gconcat.test	2007-10-29 11:53:09 +01:00
+++ b/mysql-test/t/func_gconcat.test	2007-12-11 12:03:57 +01:00
@@ -155,8 +155,10 @@ create table t1 (s1 char(10), s2 int not
 insert into t1 values ('a',2),('b',2),('c',1),('a',3),('b',4),('c',4);
 select distinct s1 from t1 order by s2,s1;
 select group_concat(distinct s1) from t1;
+--error ER_WRONG_USAGE
 select group_concat(distinct s1 order by s2) from t1 where s2 < 4;
 # The following is wrong and needs to be fixed ASAP
+--error ER_WRONG_USAGE
 select group_concat(distinct s1 order by s2) from t1;
 drop table t1;
 
@@ -517,6 +519,7 @@ SET group_concat_max_len = 20;
 SELECT GROUP_CONCAT( a ) FROM t1;
 SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;  
 SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;          
+--error ER_WRONG_USAGE
 SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; 
 SET group_concat_max_len = DEFAULT;
 DROP TABLE t1;
@@ -526,10 +529,12 @@ SET group_concat_max_len= 65535;
 CREATE TABLE t1( a TEXT, b INTEGER );
 INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 );
 SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+--error ER_WRONG_USAGE
 SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
 SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
 SET group_concat_max_len= 10;
 SELECT GROUP_CONCAT(a ORDER BY b) FROM t1;
+--error ER_WRONG_USAGE
 SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
 SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
 
@@ -598,5 +603,26 @@ insert into t1 values (''),('');
 select group_concat(distinct f1) from t1;
 select group_concat(f1) from t1;
 drop table t1;
+
+#
+# Bug#32798: DISTINCT in GROUP_CONCAT clause fails when ordering by a column
+# with null values
+#
+CREATE TABLE t1 (a INT, b INT);
+
+INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2);
+
+SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1;
+--error ER_WRONG_USAGE
+SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
+--error ER_WRONG_USAGE
+SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1;
+--error ER_WRONG_USAGE
+SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
+SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1;
+SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1;
+SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1;
+SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1;
 
 --echo End of 5.0 tests
diff -Nrup a/sql/item_sum.cc b/sql/item_sum.cc
--- a/sql/item_sum.cc	2007-10-29 11:53:09 +01:00
+++ b/sql/item_sum.cc	2007-12-11 12:03:57 +01:00
@@ -3267,6 +3267,19 @@ bool Item_func_group_concat::setup(THD *
   DBUG_ENTER("Item_func_group_concat::setup");
 
   /*
+    When using GROUP_CONCAT(DISTINCT <fields> ORDER BY <fields>), one list of
+    fields must be a prefix of the other since the same (sorted)data structure
+    is used to ensure both uniqueness and ordering. Note that we don't allow
+    expressions in this case.
+   */
+  if (arg_count_order > 0 && distinct)
+    for(uint i= 0; i < min(arg_count_order, arg_count_field); ++i)
+      if(args[i]->type() == FIELD_ITEM &&
+         (*order[i]->item)->type() == FIELD_ITEM &&
+         !args[i]->eq(*order[i]->item, FALSE))
+        my_error(ER_WRONG_USAGE, MYF(0), "DISTINCT ORDER BY", "GROUP_CONCAT");
+
+  /*
     Currently setup() can be called twice. Please add
     assertion here when this is fixed.
   */
@@ -3306,7 +3319,7 @@ bool Item_func_group_concat::setup(THD *
   if (arg_count_order &&
       setup_order(thd, args, context->table_list, list, all_fields, *order))
     DBUG_RETURN(TRUE);
-
+  
   count_field_types(select_lex, tmp_table_param, all_fields, 0);
   tmp_table_param->force_copy_fields= force_copy_fields;
   DBUG_ASSERT(table == 0);
Thread
bk commit into 5.0 tree (mhansson:1.2589) BUG#32798mhansson11 Dec