Description:
I'm using GROUP_CONCAT to turn multiple rows in a left join into a comma-separated string, which works nicely. If any values in the right-hand table exist, they're concatenated, while left-hand rows with no right-hand value remain NULL as expected.
However, when I use that query in a subselect, the query hangs - listing processes shows it sleeping.
Using IFNULL to turn the null values from GROUP_CONCAT into empty strings works around the problem.
Expected behaviour: the NULL values are preserved in the derived table produced by the subquery.
How to repeat:
Here's the test table structure I was using:
DROP TABLE IF EXISTS `b`;
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`a_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `a` VALUES (1);
INSERT INTO `a` VALUES (2);
INSERT INTO `a` VALUES (3);
CREATE TABLE `b` (
`b_id` tinyint(4) NOT NULL default '0',
`b_a` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`b_id`),
KEY (`b_a`),
CONSTRAINT `fk_b_a` FOREIGN KEY (`b_a`) REFERENCES `a` (`a_id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `b` VALUES (1,1);
INSERT INTO `b` VALUES (2,1);
INSERT INTO `b` VALUES (3,1);
INSERT INTO `b` VALUES (4,2);
INSERT INTO `b` VALUES (5,2);
This is the original query using GROUP_CONCAT:
SELECT a.*,
GROUP_CONCAT(b.b_id SEPARATOR ',') as b_list
FROM (a LEFT JOIN (b) on a.a_id = b.b_a)
GROUP BY a.a_id
producing
+------+--------+
| a_id | b_list |
+------+--------+
| 1 | 1,2,3 |
| 2 | 4,5 |
| 3 | NULL |
+------+--------+
Wrapping the query in a subselect hangs the query:
SELECT * FROM
(SELECT a.*,
GROUP_CONCAT(b.b_id SEPARATOR ',') as b_list
FROM (a LEFT JOIN (b) on a.a_id = b.b_a)
GROUP BY a.a_id ) AS xyz
The server hasn't hung, though, it appears the query is just sleeping:
[root@allie root]# mysqladmin processlist
+----+------------+-----------------------------+------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+-----------------------------+------------------+---------+------+-------+------------------+
| 85 | root | localhost | tim_test | Sleep | 13 | | |
| 88 | root | localhost | | Query | 0 | | show processlist |
+----+------------+-----------------------------+------------------+---------+------+-------+------------------+
If I do this:
SELECT * FROM
(SELECT a.*,
IFNULL(GROUP_CONCAT(b.b_id SEPARATOR ','),'') as b_list
FROM (a LEFT JOIN (b) on a.a_id = b.b_a)
GROUP BY a.a_id ) AS xuz
or delete row 3 from A, the query works again.
Running this query, with GROUP_CONCAT changed to another aggregate function, doesn't cause the problem:
SELECT * FROM
(SELECT a.*,
SUM(b.b_id SEPARATOR ',') as b_list
FROM (a LEFT JOIN (b) on a.a_id = b.b_a)
GROUP BY a.a_id ) AS xuz
Suggested fix:
I'd expect the NULL values to be preserved in the subquery's derived table.
