MySQL Bugs: #3543: Subquery GROUP_CONCAT does not work properly

Bug #3543	Subquery GROUP_CONCAT does not work properly
Submitted:	22 Apr 2004 13:18	Modified:	5 May 2004 18:17
Reporter:	Daniel Grace	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql-4.1.1-alpha-standard (Official)	OS:	Linux (Linux bassett-dell 2.2.18)
Assigned to:	Timothy Smith	CPU Architecture:	Any

Description:
When using GROUP_CONCAT in a dependant subquery when the parent query returns multiple rows, that field will have the same value for every row as it should have for the first row.  Rows after the first row will have incorrect value.

Using LIMIT to change the first row returned changes the value that is thus returned by GROUP CONCAT.

How to repeat:
DROP TABLE IF EXISTS course;
CREATE TABLE `course` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` char(64) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS entity;
CREATE TABLE `entity` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` char(16) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS course_membership;
CREATE TABLE `course_membership` (
`id` int(10) unsigned NOT NULL auto_increment,
`courseid` int(10) unsigned NOT NULL default '0',
`entityid` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY(entityid, courseid),
KEY(courseid),
FOREIGN KEY(entityid) REFERENCES entity(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY(courseid) REFERENCES course(id) ON UPDATE CASCADE ON DELETE CASCADE,
) Type=InnoDB;
INSERT INTO course (id, title) VALUES (1, "Course 1A"), (2, "Course 1B"), (3, "Course 1C"), (4, "Course 2A"), (5, "Course 2B");
INSERT INTO entity (id, name)  VALUES (1, "Teacher 1"), (2, "Teacher 2");
INSERT INTO course_membership (courseid, entityid) VALUES (1, 1), (2, 1), (3,1), (4,2), (5,2);

SELECT c.id, c.title, (SELECT GROUP_CONCAT(e.name SEPARATOR "; ") FROM course_membership cm, entity e WHERE cm.entityid=e.id AND cm.courseid=c.id) AS teachers
FROM course c 
GROUP BY c.id   /* Doesn't affect results, was an attempt at a workaround */
LIMIT 0,5;    /* try 2,5  and 1,5.  All matching rows will report "Teacher 1" for 0,5 and 1,5 -- but will report "Teacher 2" for 2,5 */

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

In the query, GROUP_CONCAT() is used in a (sub-)query that does not have any GROUP clause.

The GROUP clause in the external query doesn't apply to the internal query.  It simply GROUPs on c.id, which is always unique already.

E.g., run the sub-query on its own:

mysql> select group_concat(e.name separator "; ") from course_membership cm, entity e where cm.entityid=e.id and cm.courseid = 1;
+-------------------------------------+
| group_concat(e.name separator "; ") |
+-------------------------------------+
| Teacher 1                           |
+-------------------------------------+
1 row in set (0.33 sec)

That's all that is happening in your query....

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:

Daniel,

Sorry about that - I misread the main point of your report.

When I try it, I get:

mysql> SELECT c.id, c.title, (SELECT GROUP_CONCAT(e.name SEPARATOR "; ") FROM course_membership cm, entity e WHERE cm.entityid=e.id AND cm.courseid=c.id) AS teachers FROM course c LIMIT 2,5;
+----+-----------+-----------+
| id | title     | teachers  |
+----+-----------+-----------+
|  3 | Course 1C | Teacher 1 |
|  4 | Course 2A | Teacher 2 |
|  5 | Course 2B | Teacher 2 |
+----+-----------+-----------+
3 rows in set (0.00 sec)

That looks correct to me - this is something that's fixed in 4.1.2.