Description:
When using the DISTINCT option of GROUP_CONCAT on the results of a COALESCE the results are corrupted or skip values, eliminating either the DISTINCT or the COALESCE provides the expected result as does explicitly CONVERTing the column to BINARY.
The problem is apparent with both MyISAM an InnoDB tables with a column of type MEDIUMTEXT, however it does not occur when the column is of type varchar.
group_concat_max_len is set to 32,000
How to repeat:
mysql> SELECT VERSION();
+----------------------+
| VERSION() |
+----------------------+
| 5.0.41-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> DROP TABLE gctest;
Query OK, 0 rows affected (0.00 sec)
mysql> SET group_concat_max_len = 32000;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE gctest (City mediumtext DEFAULT NULL) CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO gctest VALUES ('Canberra'), ('Sydney'), ('Melbourne'), ('Hobart'), ('Adelaide'), ('Perth'), ('Darwin'), ('Brisbane');
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT GROUP_CONCAT(DISTINCT COALESCE(City) ORDER BY City SEPARATOR ', ') FROM gctest;
+--------------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT COALESCE(City) ORDER BY City SEPARATOR ', ') |
+--------------------------------------------------------------------+
| Brisb, Brisba, Brisbane, Brisbanee |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
-- When converted to BINARY the result is as expected:
mysql> SELECT GROUP_CONCAT(DISTINCT COALESCE(CONVERT(City USING BINARY)) ORDER BY City SEPARATOR ', ') FROM gctest;
+------------------------------------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT COALESCE(CONVERT(City USING binary)) ORDER BY City SEPARATOR ', ') |
+------------------------------------------------------------------------------------------+
| Adelaide, Brisbane, Canberra, Darwin, Hobart, Melbourne, Perth, Sydney |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
GROUP_CONCAT should return the expected result as in the case when CONVERT USING BINARY is specified.
Description: When using the DISTINCT option of GROUP_CONCAT on the results of a COALESCE the results are corrupted or skip values, eliminating either the DISTINCT or the COALESCE provides the expected result as does explicitly CONVERTing the column to BINARY. The problem is apparent with both MyISAM an InnoDB tables with a column of type MEDIUMTEXT, however it does not occur when the column is of type varchar. group_concat_max_len is set to 32,000 How to repeat: mysql> SELECT VERSION(); +----------------------+ | VERSION() | +----------------------+ | 5.0.41-community-log | +----------------------+ 1 row in set (0.00 sec) mysql> use test; Database changed mysql> DROP TABLE gctest; Query OK, 0 rows affected (0.00 sec) mysql> SET group_concat_max_len = 32000; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TEMPORARY TABLE gctest (City mediumtext DEFAULT NULL) CHARSET=utf8; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO gctest VALUES ('Canberra'), ('Sydney'), ('Melbourne'), ('Hobart'), ('Adelaide'), ('Perth'), ('Darwin'), ('Brisbane'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> SELECT GROUP_CONCAT(DISTINCT COALESCE(City) ORDER BY City SEPARATOR ', ') FROM gctest; +--------------------------------------------------------------------+ | GROUP_CONCAT(DISTINCT COALESCE(City) ORDER BY City SEPARATOR ', ') | +--------------------------------------------------------------------+ | Brisb, Brisba, Brisbane, Brisbanee | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) -- When converted to BINARY the result is as expected: mysql> SELECT GROUP_CONCAT(DISTINCT COALESCE(CONVERT(City USING BINARY)) ORDER BY City SEPARATOR ', ') FROM gctest; +------------------------------------------------------------------------------------------+ | GROUP_CONCAT(DISTINCT COALESCE(CONVERT(City USING binary)) ORDER BY City SEPARATOR ', ') | +------------------------------------------------------------------------------------------+ | Adelaide, Brisbane, Canberra, Darwin, Hobart, Melbourne, Perth, Sydney | +------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: GROUP_CONCAT should return the expected result as in the case when CONVERT USING BINARY is specified.