| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.13a-nt, 4.1.15-standard-log, 5.0.16-standard-log | OS: | Windows (WinXP and Linux 2.4.21) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[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.
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;