Bug #28514 group_concat(distinct) on text cols has different results than without distinct

• Submitted: 18 May 2007 10:32
• Modified: 18 May 2007 10:52
• Reporter: Martin Andersen
• Status: Can't repeat
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.0.30,5.0.32
• OS: Linux (Ver 8.41 Distrib 5.0.32, for pc-linux-gnu on i486)
• CPU Architecture: Any
• Tags: distinct, group_concat, text

[18 May 2007 10:32] Martin Andersen

Description:
If we have a table with a text column, it behaves differently if we use distinct in a group_concat query, although the values are different.

This error occurs when the column is of type 'text' and not for instance with type 'varchar'.

It behaves correctly if we do not use distinct on the column.

How to repeat:
mysql> CREATE TABLE metadata (`id` int(11) NOT NULL auto_increment, `keyname` varchar(255) NOT NULL default '', `value` text, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

mysql> insert into metadata (keyname,value) values ('country','US'),('country','DE'),('country','DK');

mysql> select group_concat( value ) from metadata where keyname = 'country';
+-----------------------+
| group_concat( value ) |
+-----------------------+
| US,DE,DK              | 
+-----------------------+
1 row in set (0.00 sec)

mysql> select group_concat( distinct value ) from metadata where keyname = 'country';
+--------------------------------+
| group_concat( distinct value ) |
+--------------------------------+
| US                             | 
+--------------------------------+
1 row in set (0.00 sec)

[18 May 2007 10:52] Sveta Smirnova

Thank you for the report.

But versions 5.0.30 and 5.0.32 are quite old and several bugs with DISTINCT were fixed since. Additionally I can not repeat described behaviour with current sources. So I marked the report as "Can't repeat".

Please, upgrade to current 5.0.40 or 5.0.41 version, try with it and if you can repeat the issue fill free to reopen the report.