Bug #12863 group_concat ignores empty string, if first inlist
Submitted: 29 Aug 2005 19:39 Modified: 9 Sep 2005 20:28
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.14/BK 4.1 source OS:* / freebsd
Assigned to: Igor Babaev CPU Architecture:Any

[29 Aug 2005 19:39] Martin Friebe
Description:
group_concat ignores the empty string, if this is the first value to occur in the result.

the first query, depends on the order values are returned by the table handler.

The last query includes the empty string. The seperator is present.

the 2nd query ("order by a" ascending) does not include the seperator, the empty string has been dropped from the list.

The bug also happens without the "order by" in the grooup_concat, if the values in the table are placed accordingly.

How to repeat:
create table t1 (a varchar(9));
insert into t1 select ''; insert into t1 select 'x';

select group_concat( a )  from t1;
+-------------------+
| group_concat( a ) |
+-------------------+
| x                 |
+-------------------+

select group_concat( a order by a)  from t1;
+-----------------------------+
| group_concat( a order by a) |
+-----------------------------+
| x                           |
+-----------------------------+
1 row in set (0.00 sec)

select group_concat( a order by a desc)  from t1;
+----------------------------------+
| group_concat( a order by a desc) |
+----------------------------------+
| x,                               |
+----------------------------------+
1 row in set (0.02 sec)

Suggested fix:
the empty string should not be dropped
[7 Sep 2005 5:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29405
[8 Sep 2005 19:43] Igor Babaev
ChangeSet
  1.2407 05/09/06 22:20:11 igor@rurik.mysql.com +4 -0
  func_gconcat.result, func_gconcat.test:
    Added test cases for bug #12863.
  item_sum.cc, item_sum.h:
    Fixed bug #12863.
    Added a flag to Item_func_group_concat set to FALSE after
    concatenation of the first element of a group.

The fix was merged in 5.0.

This fix will appear in 4.1.15 and 5.0.13
[9 Sep 2005 20:28] Paul Dubois
Noted in 4.1.15, 5.0.13 changelogs.