Bug #12890 group_concat with order, not distinct in join
Submitted: 30 Aug 2005 18:17 Modified: 18 Oct 2005 19:03
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.14/5.0 BK OS:* / freebsd
Assigned to: Evgeny Potemkin CPU Architecture:Any

[30 Aug 2005 18:17] Martin Friebe
Description:
please see example, the result is not distinct.

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

select group_concat(distinct a.a  order by b.a) from t1 a ,t1 b;
+------------------------------------------+
| group_concat(distinct a.a  order by b.a) |
+------------------------------------------+
| 3,2,1,3                                  |
+------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
-
[30 Aug 2005 18:30] Miguel Solorzano
master >create table t1 (a int);
Query OK, 0 rows affected (0.04 sec)

master >insert into t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

master >
master >select group_concat(distinct a.a  order by b.a) from t1 a ,t1 b;
+------------------------------------------+
| group_concat(distinct a.a  order by b.a) |
+------------------------------------------+
| 3,2,1,3                                  |
+------------------------------------------+
1 row in set (0.00 sec)

master >select version();
+------------------+
| version()        |
+------------------+
| 4.1.15-debug-log |
+------------------+
1 row in set (0.00 sec)
[18 Oct 2005 19:03] Evgeny Potemkin
This is known problem with group_concat(). DISTINCT with ORDER BY doesn't work inside GROUP_CONCAT() if you don't use all and only those columns that are in the DISTINCT list.

Also see comments for bug #2695.