Bug #30897 GROUP_CONCAT returns extra comma on empty fields
Submitted: 7 Sep 2007 14:36 Modified: 19 Nov 2007 4:56
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.45-debug-log, 5.1 OS:Any
Assigned to: Sergey Glukhov
Tags: Contribution, qc

[7 Sep 2007 14:36] Philip Stoev
Description:
GROUP_CONCAT returns a "," if the column in question contains only empty strings. It should return an empty string instead.

How to repeat:
mysql> create table t1 (f1 char(20));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (''),('');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select group_concat(distinct f1) from t1;
+---------------------------+
| group_concat(distinct f1) |
+---------------------------+
| ,                         |
+---------------------------+
1 row in set (0.00 sec)

Suggested fix:
It appears to me that there is some special handling in the code for empty strings, which does not work well
[7 Sep 2007 19:18] Hartmut Holzgraefe
mysqltest test case

Attachment: bug30897.tgz (application/x-gtar, text), 850 bytes.

[8 Sep 2007 17:09] Martin Friebe
The issue seems to be caused by  Item_func_group_concat::val_str in item_sum.cc.

It checks if the result string has been written or not and write it if needed.

It does so by checking the length of the result string:
   if (!result.length() && tree)

In the case of yous an empty string in the result this is still 0, but no_appended is false. If the empty string is appended a 2nd time the separator is used.

testing for no_appended (which will be False if anything, including the empty string is in result) seems the better option
   if (no_appended && tree)
[8 Sep 2007 17:09] Martin Friebe
proposed patch

Attachment: bug30897.patch (text/x-patch), 413 bytes.

[25 Oct 2007 12:10] 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/commits/36339

ChangeSet@1.2537, 2007-10-25 17:05:53+05:00, gluh@mysql.com +3 -0
  Bug#30897 GROUP_CONCAT returns extra comma on empty fields
  The fix is a copy of Martin Friebe's suggestion.
  added testing for no_appended which will be false if anything,
  including the empty string is in result
[29 Oct 2007 10:59] 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/commits/36538

ChangeSet@1.2555, 2007-10-29 14:53:10+04:00, gluh@mysql.com +3 -0
  Bug#30897 GROUP_CONCAT returns extra comma on empty fields
  The fix is a copy of Martin Friebe's suggestion.
  added testing for no_appended which will be false if anything,
  including the empty string is in result
[16 Nov 2007 9:30] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[19 Nov 2007 4:56] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.