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)
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 4.1.14/5.0 BK
• OS: * / freebsd
• Assigned to: Assigned Account
• 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] MySQL Verification Team

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.