Bug #7405 group_concat with distinct and rollup => ignores distinct in some rows
Submitted: 18 Dec 2004 16:12 Modified: 1 Jun 2005 3:08
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:FreeBSD (freebsd (probably others))
Assigned to: Ramil Kalimullin CPU Architecture:Any

[18 Dec 2004 16:12] Martin Friebe
Description:
group_concat does not handle distinct correct if rollup is specified.

the example below should explin the problem

How to repeat:
create table t1 (a int, b int);
insert into t1 values (1,1), (1,2), (1,3), (1,1), (1,2), (1,3);

select a, group_concat(distinct b) from t1 group by a;
+------+--------------------------+
| a    | group_concat(distinct b) |
+------+--------------------------+
|    1 | 1,2,3                    |
+------+--------------------------+
# this is expected
 
#same query with rollup
select a, group_concat(distinct b) from t1 group by a with rollup;
+------+--------------------------+
| a    | group_concat(distinct b) |
+------+--------------------------+
|    1 | 1,2,3,1,2,3              |
| NULL | 1,2,3                    |
+------+--------------------------+
# the first row should be as in the result without rollup

Suggested fix:
-
[18 Dec 2004 16:16] Martin Friebe
# the order by, does also break, when used with rollup (I report this to the same bug, as it thems to be quiet related)

select a, group_concat(distinct b order by b) from t1 group by a with rollup;
+------+-------------------------------------+
| a    | group_concat(distinct b order by b) |
+------+-------------------------------------+
|    1 | 0,0,0,0,0,0                         |
| NULL | 1,2,3                               |
+------+-------------------------------------+
# all values in the concat are 0
[18 Dec 2004 17:21] Martin Friebe
It seems that the values with order by are not always 0, but sometimes random values (either from other tables, or maybe pointers?)
This could explain that I had the above crashing the servers (on a different set of data (but cant reproduce)
[17 May 2005 7:12] Ramil Kalimullin
I got server crashes for that and similar queries.
[31 May 2005 6:12] Ramil Kalimullin
fixed in 4.1.13
[31 May 2005 16:27] 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/25395
[1 Jun 2005 3:08] Paul Dubois
Noted in 4.1.13 changelog.