MySQL Bugs: #2695: Strange behaviour with GROUP

Bug #2695	Strange behaviour with GROUP_CONCAT() using ORDER BY
Submitted:	9 Feb 2004 22:28	Modified:	5 Apr 2004 3:20
Reporter:	Nate Sanden	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1-alpha-nt	OS:	Windows (Windows XP)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
When attempting to ORDER the results of a GROUP_CONCAT in a SELECT query i get strange results.

For example, this first query works as expected:

SELECT a.id1, GROUP_CONCAT(a.id2 ORDER BY a.id2 ASC) AS concat_id
FROM a, b
WHERE a.id1 = b.id1 AND a.id1=1
GROUP BY a.id1

This second query however will give the correct data but the ids are not sorted (as i would expect).

SELECT a.id1, GROUP_CONCAT(a.id2 ORDER BY a.id2 ASC) AS concat_id
FROM a, b
WHERE a.id1 = b.id1
GROUP BY a.id1

I'm not sure if this is a bug or not.

How to repeat:
#
# Table structure for table `a`
#

CREATE TABLE `a` (
  `id1` tinyint(4) NOT NULL default '0',
  `id2` tinyint(4) NOT NULL default '0'
) TYPE=MyISAM DEFAULT CHARSET=latin1;

#
# Dumping data for table `a`
#

INSERT INTO `a` VALUES (1, 1);
INSERT INTO `a` VALUES (1, 2);
INSERT INTO `a` VALUES (1, 3);
INSERT INTO `a` VALUES (1, 4);
INSERT INTO `a` VALUES (1, 5);
INSERT INTO `a` VALUES (2, 1);
INSERT INTO `a` VALUES (2, 2);
INSERT INTO `a` VALUES (2, 3);

#
# Table structure for table `b`
#

CREATE TABLE `b` (
  `id1` tinyint(4) NOT NULL default '0'
) TYPE=MyISAM DEFAULT CHARSET=latin1;

#
# Dumping data for table `b`
#

INSERT INTO `b` VALUES (1);
INSERT INTO `b` VALUES (2);
INSERT INTO `b` VALUES (3);
INSERT INTO `b` VALUES (4);
INSERT INTO `b` VALUES (5);

/* GOOD QUERY */
SELECT a.id1, GROUP_CONCAT(a.id2 ORDER BY a.id2 ASC) AS concat_id
FROM a, b
WHERE a.id1 = b.id1 AND a.id1=1
GROUP BY a.id1

/* BAD QUERY */
SELECT a.id1, GROUP_CONCAT(a.id2 ORDER BY a.id2 ASC) AS concat_id
FROM a, b
WHERE a.id1 = b.id1
GROUP BY a.id1

I found the problem. I will fix this bug as soon as possible.

GROUP_CONCAT(DISTINCT ...) also fails. For example, with MySQL 4.1.2-alpha and 
SuSE 8.2, I get a result which looks non-distinct: 
 
mysql> create table t2 (s1 char(10)); 
Query OK, 0 rows affected (0.04 sec) 
 
mysql> insert into t2 values ('a'),('b'),('c'),('a'); 
Query OK, 4 rows affected (0.00 sec) 
Records: 4  Duplicates: 0  Warnings: 0 
 
mysql> select group_concat(distinct s1) from t2; 
+---------------------------+ 
| group_concat(distinct s1) | 
+---------------------------+ 
| a,c,b,a                   | 
+---------------------------+ 
1 row in set (0.00 sec)

The second bug is another bug. Could you insert it like antoher bug message ?

Okay, I put the report in as a separate error, Bug#3381 Strange behaviour with 
GROUP_CONCAT() using DISTINCT

Will be pushed soon

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.1.2

While fixing this, I found a logical bug in how DISTINCT combined with ORDER BY is handled.  The current code works only if the ORDER BY uses only all columns in the DISTINCT part.  I have for now just documented this issue in the known bugs section in the manual.