MySQL Bugs: #29532: GROUP_CONCAT with ORDER BY text-column returns incorrect results

Bug #29532	GROUP_CONCAT with ORDER BY text-column returns incorrect results
Submitted:	3 Jul 2007 20:58	Modified:	13 Jul 2007 22:24
Reporter:	Bob Fitterman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When using the GROUP_CONCAT(), if the ORDER BY clause specifies a column of the TEXT data type, the results are not sorted in a predictable order.

I attempted this on a Linux system running version (mysql  Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i386) using readline 5.0) and was unable to reproduce it.

Note that switching the column type to either CHAR or VARCHAR do not exhibit this problem. Hence my conclusion that it's caused by the data type TEXT. 

How to repeat:
DROP TABLE IF EXISTS foo;
CREATE TABLE  foo (
  `id` int(10) unsigned NOT NULL auto_increment,
  `puk` varchar(255) default NULL,
  `value1` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO foo VALUES (1, "z", "The Good, The Bad");
INSERT INTO foo VALUES (2, "w", "The First In Time");
INSERT INTO foo VALUES (3, "m", "Be Bad, But Not Evil");
INSERT INTO foo VALUES (4, "s", "A Story About People");

SELECT * from foo ORDER BY value1;

SELECT GROUP_CONCAT(id ORDER BY value1) as foo_list FROM foo;

/*
   At this point you'd expect to see the result "4,3,2,1" in foo_list. 
*/

/*
   The following versions use other data types and the results are
   as expected
*/

DROP TABLE IF EXISTS foo;
CREATE TABLE  foo (
  `id` int(10) unsigned NOT NULL auto_increment,
  `puk` varchar(255) default NULL,
  `value1` char(20),
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO foo VALUES (1, "z", "The Good, The Bad");
INSERT INTO foo VALUES (2, "w", "The First In Time");
INSERT INTO foo VALUES (3, "m", "Be Bad, But Not Evil");
INSERT INTO foo VALUES (4, "s", "A Story About People");

SELECT * from foo ORDER BY value1, puk;

SELECT GROUP_CONCAT(id ORDER BY value1) as foo_list FROM foo;

DROP TABLE IF EXISTS foo;
CREATE TABLE  foo (
  `id` int(10) unsigned NOT NULL auto_increment,
  `puk` varchar(255) default NULL,
  `value1` varchar(20),
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO foo VALUES (1, "z", "The Good, The Bad");
INSERT INTO foo VALUES (2, "w", "The First In Time");
INSERT INTO foo VALUES (3, "m", "Be Bad, But Not Evil");
INSERT INTO foo VALUES (4, "s", "A Story About People");

SELECT * from foo ORDER BY value1, puk;

SELECT GROUP_CONCAT(id ORDER BY value1) as foo_list FROM foo;

Suggested fix:
Sort the GROUP_CONCAT results in the same order the free-standing query does.

Thank you for the report.

I can not repeat described behaviour. Please provide your configuration file.

I have attached the my.ini file I'm running with.

I can not repeat the results you describe on my XP:

mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TABLE  foo (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `puk` varchar(255) default NULL,
    ->   `value1` text,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> INSERT INTO foo VALUES (1, "z", "The Good, The Bad");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO foo VALUES (2, "w", "The First In Time");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO foo VALUES (3, "m", "Be Bad, But Not Evil");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO foo VALUES (4, "s", "A Story About People");
Query OK, 1 row affected (0.05 sec)

mysql>
mysql> SELECT * from foo ORDER BY value1;
+----+------+----------------------+
| id | puk  | value1               |
+----+------+----------------------+
|  4 | s    | A Story About People |
|  3 | m    | Be Bad, But Not Evil |
|  2 | w    | The First In Time    |
|  1 | z    | The Good, The Bad    |
+----+------+----------------------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT GROUP_CONCAT(id ORDER BY value1) as foo_list FROM foo;
+----------+
| foo_list |
+----------+
| 4,3,2,1  |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.44-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)

Please, copy and paste your results the same way I did.

Here's what I get when I do the same thing you did:

mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE  foo (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `puk` varchar(255) default NULL,
    ->   `value1` text,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO foo VALUES (1, "z", "The Good, The Bad");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO foo VALUES (2, "w", "The First In Time");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO foo VALUES (3, "m", "Be Bad, But Not Evil");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO foo VALUES (4, "s", "A Story About People");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * from foo ORDER BY value1;
+----+------+----------------------+
| id | puk  | value1               |
+----+------+----------------------+
|  4 | s    | A Story About People |
|  3 | m    | Be Bad, But Not Evil |
|  2 | w    | The First In Time    |
|  1 | z    | The Good, The Bad    |
+----+------+----------------------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT GROUP_CONCAT(id ORDER BY value1) as foo_list FROM foo;
+----------+
| foo_list |
+----------+
| 2,1,4,3  |
+----------+
1 row in set (0.00 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.41-community-nt |
+---------------------+
1 row in set (0.00 sec)

As you can see, foo_list should contain "4,3,2,1" but it doesn't. (Also, please note you tested on a different version than the one I submitted the bug for.)

I thought on the off-chance there might be some other relevant thing about the database settings, I would include the output of mysqldump for this database:

-- MySQL dump 10.11
--
-- Host: localhost    Database: bug
-- ------------------------------------------------------
-- Server version       5.0.41-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `foo`
--

DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `puk` varchar(255) default NULL,
  `value1` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `foo`
--

LOCK TABLES `foo` WRITE;
/*!40000 ALTER TABLE `foo` DISABLE KEYS */;
INSERT INTO `foo` VALUES (1,'z','The Good, The Bad'),(2,'w','The First In Time'),(3,'m','Be Bad, But Not Evil'),(4,'s','A Story About People');
/*!40000 ALTER TABLE `foo` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-07-08 21:52:58

Please, try to repeat with a newer version, 5.0.45, we just released, and inform about the results.

Fixed in version "5.0.45-community-nt"