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

[14 Apr 2003 11:39] Ray Rodriguez
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.
[14 Apr 2003 12:06] Sinisa Milivojevic
[/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 |
+-------------+----------------------------------+---------+
[15 Apr 2003 11:54] Vasily Kishkin
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