Invalid query result
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
High
|
Unassigned | ||
5.5 |
Invalid
|
Undecided
|
Unassigned | ||
5.6 |
Fix Released
|
High
|
Unassigned |
Bug Description
Reduced test case so select query doesn't make much sense:
CREATE TABLE IF NOT EXISTS `dummy22` (
`classifiedId` int(10) unsigned NOT NULL,
`companyId` int(10) unsigned NOT NULL,
`ownerCargoGr
PRIMARY KEY (`classifiedId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=
INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGrou
(499355, 5397, 12),
(499677, 408, 24);
Run the following query:
SELECT GROUP_CONCAT(
It should return only the second row, however you will get both rows returned as result on 5.6.23-72.1-log (I have also tested on 5.5... there the result is correct, only second row).
NOTE: removing one of either group_concat, coalesce or order by will yield correct result; changing order by to primary will also yield correct result
I was able to reproduce this on upstream MySQL 5.6.22, for example:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.22 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `dummy22` ( utf8_unicode_ ci;
-> `classifiedId` int(10) unsigned NOT NULL,
-> `companyId` int(10) unsigned NOT NULL,
-> `ownerCargoGroupId` int(10) unsigned DEFAULT NULL,
-> PRIMARY KEY (`classifiedId`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=
Query OK, 0 rows affected (0.47 sec)
mysql> pId`)
mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGrou
VALUES
-> (499355, 5397, 12),
-> (499677, 408, 24);
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT GROUP_CONCAT( companyId) as grouper, COALESCE( ownerCargoGroup Id, 0)
as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyI
d desc;
+---------+-------+
| grouper | cargo |
+---------+-------+
| 5397 | 12 |
| 408 | 24 |
+---------+-------+
2 rows in set (0.11 sec)
mysql> SELECT GROUP_CONCAT( companyId) as grouper, COALESCE( ownerCargoGroup Id, 0)
as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24;
+---------+-------+
| grouper | cargo |
+---------+-------+
| 408 | 24 |
+---------+-------+
1 row in set (0.00 sec)
mysql> explain SELECT GROUP_CONCAT( companyId) as grouper, COALESCE( ownerCargoGro ------- -----+- ------- -+----- --+---- ------- ----+-- ------- +------ ---+--- --- +------ ------- ------- ------- ------+ ------- -----+- ------- -+----- --+---- ------- ----+-- ------- +------ ---+--- --- +------ ------- ------- ------- ------+ ------- -----+- ------- -+----- --+---- ------- ----+-- ------- +------ ---+--- --- +------ ------- ------- ------- ------+
upId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY
companyId desc;
+----+-
+------
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-
+------
| 1 | SIMPLE | dummy22 | index | PRIMARY | PRIMARY | 4 | NULL
| 2 | Using temporary; Using filesort |
+----+-
+------
1 row in set (0.02 sec)
mysql> explain SELECT GROUP_CONCAT( companyId) as grouper, COALESCE( ownerCargoGro ------- -----+- ------- -+----- --+---- ------- ----+-- ------- +------ ---+--- --- ------- -----+- ------- -+----- --+---- ------- ----+-- ------- +------ ---+--- --- ------- -----+- ------- -+----- --+---- ------- ----+-- ------- +------ ---+--- ---
upId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24;
+----+-
+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-
+------+-------+
| 1 | SIMPLE | dummy22 | index | PRIMARY | PRIMARY | 4 | NULL
| 2 | NULL |
+----+-
+------+-------+
1 row in set (0.00 sec)
So, it's upstream MySQL bug. Would you like to report it to http:// bugs.mysql. comk yourself to get a credit, or should I do this for you?