Bug #77116 | Invalid query result | ||
---|---|---|---|
Submitted: | 21 May 2015 11:43 | Modified: | 22 May 2015 5:40 |
Reporter: | Hrvoje Novosel | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.6.*, 5.6.24 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | regression |
[21 May 2015 11:43]
Hrvoje Novosel
[21 May 2015 12:45]
MySQL Verification Team
Hello Hrvoje Novosel, Thank you for the report and test case. Observed this with 5.6.24. Thanks, Umesh
[21 May 2015 12:45]
MySQL Verification Team
// 5.6.24 - affected mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.6.24 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.24-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ 7 rows in set (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `dummy22` ( -> `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=utf8_unicode_ci; Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) VALUES -> (499355, 5397, 12), -> (499677, 408, 24); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId desc; +---------+-------+ | grouper | cargo | +---------+-------+ | 5397 | 12 | | 408 | 24 | +---------+-------+ 2 rows in set (0.00 sec)
[21 May 2015 12:46]
MySQL Verification Team
// 5.1.75 mysql> use test Database changed mysql> CREATE TABLE IF NOT EXISTS `dummy22` ( -> `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=utf8_unicode_ci; INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) VALUES (499355, 5397, 12), Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) VALUES -> (499355, 5397, 12), -> (499677, 408, 24); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId desc; +---------+-------+ | grouper | cargo | +---------+-------+ | 408 | 24 | +---------+-------+ 1 row in set (0.02 sec) // 5.5.45 mysql> CREATE TABLE IF NOT EXISTS `dummy22` ( -> `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=utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) VALUES -> (499355, 5397, 12), -> (499677, 408, 24); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId desc; +---------+-------+ | grouper | cargo | +---------+-------+ | 408 | 24 | +---------+-------+ 1 row in set (0.00 sec)
[21 May 2015 12:51]
MySQL Verification Team
I'm not seeing this issue on latest 5.6 and 5.7 builds(probably this is fixed/duplicate of some internal bug). // 5.6.26 - commit: 52ccefd205a2a9263a5429dfca1a183f19caf70e date: 2015-05-15 11:58:55 +0300 build-date: 2015-05-16 09:00:52 +0200 short: 52ccefd branch: mysql-5.6 MySQL source 5.6.26 mysql> use test Database changed mysql> CREATE TABLE IF NOT EXISTS `dummy22` ( -> `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=utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) VALUES -> (499355, 5397, 12), -> (499677, 408, 24); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId desc; +---------+-------+ | grouper | cargo | +---------+-------+ | 408 | 24 | +---------+-------+ 1 row in set (0.00 sec) // 5.7.8 - build used commit: 9f898703b3546147f2c1549d64b7fd4120d492aa date: 2015-05-17 07:45:26 +0530 build-date: 2015-05-17 04:23:30 +0200 short: 9f89870 branch: mysql-5.7 MySQL source 5.7.8 mysql> CREATE TABLE IF NOT EXISTS `dummy22` ( -> `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=utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) VALUES -> (499355, 5397, 12), -> (499677, 408, 24); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId desc; +---------+-------+ | grouper | cargo | +---------+-------+ | 408 | 24 | +---------+-------+ 1 row in set (0.00 sec)
[22 May 2015 5:40]
MySQL Verification Team
This issue has already been fixed in the latest 5.6 builds. Once 5.6.25 is released then you can download at http://www.mysql.com/downloads/ // 5.6.25 commit: 9294a4969387cd882ef2ae3ee001aed9ecd13616 date: 2015-05-05 12:50:42 +0200 build-date: 2015-05-05 12:54:12 +0200 short: 9294a49 branch: mysql-5.6.25-release MySQL source 5.6.25 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.25 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.25-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> use test Database changed mysql> CREATE TABLE IF NOT EXISTS `dummy22` ( -> `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=utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql> mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) -> VALUES -> (499355, 5397, 12), -> (499677, 408, 24); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as -> cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId -> desc; +---------+-------+ | grouper | cargo | +---------+-------+ | 408 | 24 | +---------+-------+ 1 row in set (0.00 sec)