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)
• Status: Closed
• 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.