Bug #15560 GROUP_CONCAT on subquery with GROUP_CONCAT usually returns NULL
Submitted: 7 Dec 2005 21:16 Modified: 6 Apr 2006 13:14
Reporter: Daniel Grace Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.13a-nt, 4.1.15-standard-log, 5.0.16-standard-log OS:Microsoft Windows (WinXP and Linux 2.4.21)
Assigned to: Evgeny Potemkin

[7 Dec 2005 21:16] Daniel Grace
Description:
Tested on:
MySQL 4.1.15-standard-log + Linux 2.4.21 (RHAS 3)
MySQL 4.1.13a-nt + Windows XP Professional SP2

When running a query that contains GROUP_CONCAT((SELECT GROUP_CONCAT...)), the result is NULL (as if the expression being grouped on had no rows) except in the ROLLUP part of the result (if there is one)

Query:
SELECT parent.id, GROUP_CONCAT((SELECT GROUP_CONCAT(child.id) FROM child WHERE child.pid=parent.id)) children
FROM parent
GROUP BY parent.id WITH ROLLUP;

Actual Result:
id 	children
1 	NULL
2 	NULL
3 	NULL
NULL 	1,2,3,4,5,6

Expected Result:
id 	children
1 	1
2 	2,3
3 	4,5,6
NULL 	1,2,3,4,5,6

How to repeat:
CREATE TABLE parent (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(id)
) Type=MyISAM;

CREATE TABLE child (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	pid INT UNSIGNED NOT NULL,
	PRIMARY KEY(id),
	KEY(pid)
) Type=MyISAM;

INSERT INTO parent (id) VALUES (1), (2), (3);
INSERT INTO child (pid) VALUES (1), (2), (2), (3), (3), (3);

SELECT * FROM parent;
SELECT * FROM child;

/* Works as documented, but doesn't have a list of *all* children at the end */
SELECT parent.id, (SELECT GROUP_CONCAT(id) FROM child WHERE child.pid=parent.id) children
FROM parent
GROUP BY parent.id WITH ROLLUP;

/* Doesn't work */
SELECT parent.id, GROUP_CONCAT((SELECT GROUP_CONCAT(child.id) FROM child WHERE child.pid=parent.id)) children
FROM parent
GROUP BY parent.id WITH ROLLUP;

/* Works */
SELECT parent.id, GROUP_CONCAT("Garbage") children
FROM parent
GROUP BY parent.id WITH ROLLUP;
[8 Dec 2005 8:44] Vasily Kishkin
Verified on 4.1.16 both on Windows 2003 and on Linux Suse 9.3. The result of the query:

id 	children
1 	NULL
2 	NULL
3 	NULL
NULL 	1,2,3,4,5,6
[8 Dec 2005 22:32] Daniel Grace
On a whim I gave it a try on MySQL 5.0.16 using freshly-created tables with the same results.
[13 Mar 2006 15:59] Evgeny Potemkin
The GROUP_CONCAT is using it's internal temporary table. When ROLLUP is present, it creates second copy of Item_func_group_concat. That copy receives same list of fields/functions that original group_concat. When the copy sets up it resets result_fields of functions to it's temporary table. As a result of this a data from functions flows directly to the ROLLUP copy, and the orginal group_concat functions shows wrong result.
[13 Mar 2006 16:01] Evgeny Potemkin
Any function that uses temporary table fo it's work, such as GROUP_CONCAT() or COUNT(DISTINCT ...) is affected when the parameter is a function.
[15 Mar 2006 9:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3855
[18 Mar 2006 13:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3951
[29 Mar 2006 19:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4291
[30 Mar 2006 8:16] Evgeny Potemkin
The GROUP_CONCAT uses its own temporary table. When ROLLUP is present
it creates the second copy of Item_func_group_concat. This copy receives the
same list of arguments that original group_concat does. When the copy is
set up the result_fields of functions from the argument list are reset to the
temporary table of this copy.
As a result of this action data from functions flow directly to the ROLLUP copy
and the original group_concat functions shows wrong result.
Since queries with COUNT(DISTINCT ...) use temporary tables to store
the results the COUNT function they are also affected by this bug.

Fixed in 4.1.19, cset 1.2453.22.1
[30 Mar 2006 15:37] Evgeny Potemkin
Fixed in 5.0.21
[30 Mar 2006 17:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4331
[6 Apr 2006 13:14] Jon Stephens
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:

Documented bugfix in 4.1.19 and 5.0.21 changelogs. Closed.