Description:
When trying to use GROUP_CONCAT and sorting the results by a field containing numeric values I get an empty result in the GROUP_CONCAT column.
How to repeat:
Create a Table using this:
CREATE TABLE test_se (
id int(10) unsigned NOT NULL auto_increment,
engine varchar(100) NOT NULL default '',
query varchar(255) NOT NULL default '',
num_q int(11) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
Now trying the following query gives me an empty result in the engine column:
mysql> SELECT query, sum(num_q) as all_q, group_concat( engine ORDER BY engine SEPARATOR ', ') FROM test_se GROUP BY query ORDER BY all_q DESC;
+-----------+-------+------------------------------------------------------+
| query | all_q | group_concat( engine ORDER BY engine SEPARATOR ', ') |
+-----------+-------+------------------------------------------------------+
| gibberish | 1337 | |
| asdf | 55 | |
+-----------+-------+------------------------------------------------------+
2 rows in set (0.00 sec)
But when trying to use the ORDER BY on another column, I get the right results:
mysql> SELECT query, sum(num_q) as all_q, group_concat( engine ORDER BY engine SEPARATOR ', ') FROM test_se GROUP BY query ORDER BY query DESC;
+-----------+-------+------------------------------------------------------+
| query | all_q | group_concat( engine ORDER BY engine SEPARATOR ', ') |
+-----------+-------+------------------------------------------------------+
| gibberish | 1337 | test1 |
| asdf | 55 | 2, test1 |
+-----------+-------+------------------------------------------------------+
2 rows in set (0.00 sec)
I tried to change the type of the num_q column, but it had no effect.