MySQL Bugs: #289: group_concat with select order by problem

Bug #289	group_concat with select order by problem
Submitted:	14 Apr 2003 11:39	Modified:	15 Apr 2003 11:54
Reporter:	Ray Rodriguez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0-alpha	OS:	Other (SCO Unix 5.0.4)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
I'm having a problem when using group_concat and an order by clause in a select.

  I am using the included CLI client and have had no problems compiling other releases including 4.0.12.  I am using GCC version 2.95.2

How to repeat:
select *,count(*) as c,group_concat(dir_code order by dir_code separator
',') as gr from `directry` where dir_code='012601' or dir_code='012388' group by zip_code;

works as expected.  The gr column contains all dir_code values as contained in the grouped records, however the following query:

select *,count(*) as c,group_concat(dir_code order by dir_code separator
',') as gr from `directry` where dir_code='012601' or dir_code='012388' group by zip_code order by c;

(note the 'order by' at the end) causes the gr column to come back empty on all rows.

[/mnt/work/mysql-4.1]$ mysql telcent -e "describe mails"
+-----------+-----------------------------------+-------------------+------+-----+---------------------+----------------+
| Field     | Type                              | Collation         | Null | Key | Default             | Extra          |
+-----------+-----------------------------------+-------------------+------+-----+---------------------+----------------+
| msg_nro   | mediumint(8) unsigned             | binary            |      | MUL | NULL                | auto_increment |
| date      | datetime                          | latin1_swedish_ci |      | PRI | 0000-00-00 00:00:00 |                |
| time_zone | varchar(6) character set latin1   | latin1_swedish_ci |      | PRI |                     |                |
| mail_from | varchar(120) character set latin1 | latin1_swedish_ci |      | PRI |                     |                |
| reply     | varchar(120) character set latin1 | latin1_swedish_ci | YES  |     | NULL                |                |
| mail_to   | text character set latin1         | latin1_swedish_ci | YES  |     | NULL                |                |
| cc        | text character set latin1         | latin1_swedish_ci | YES  |     | NULL                |                |
| sbj       | varchar(200) character set latin1 | latin1_swedish_ci | YES  |     | NULL                |                |
| txt       | mediumtext character set latin1   | latin1_swedish_ci |      |     |                     |                |
| file      | varchar(32) character set latin1  | latin1_swedish_ci |      |     |                     |                |
| hash      | int(11)                           | binary            |      | PRI | 0                   |                |
+-----------+-----------------------------------+-------------------+------+-----+---------------------+----------------+
[/mnt/work/mysql-4.1]$ mysql telcent -e "select reply, cc, group_concat(mag_nro order by msg_nro separator ',') as gr from mails group by reply order by cc"
ERROR 1054 at line 1: Unknown column 'mag_nro' in 'field list'
[/mnt/work/mysql-4.1]$ mysql telcent -e "select reply, cc, group_concat(msg_nro order by msg_nro  separator ',') as gr from mails group by reply order by cc"
+-------------+------+------+
| reply       | cc   | gr   |
+-------------+------+------+
| jani@tcx.se | NULL |      |
|             |      |      |
+-------------+------+------+
[/mnt/work/mysql-4.1]$ mysql telcent -e "select reply, cc, msg_nro from mails"
+-------------+----------------------------------+---------+
| reply       | cc                               | msg_nro |
+-------------+----------------------------------+---------+
| jani@tcx.se | NULL                             |   40148 |
| jani@tcx.se | jani@tcx.se                      |   40229 |
| jani@tcx.se | Jani Tolonen <jani@tcx.se>       |   40791 |
| jani@tcx.se | NULL                             |   42899 |
| jani@tcx.se | Dragon <jani@tcx.se>             |   44000 |
| jani@tcx.se | monty@mysql.com, antti@mysql.com |   59328 |
|             |                                  |   59329 |
+-------------+----------------------------------+---------+

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html