MySQL Bugs: #77116: Invalid query result

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:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.*, 5.6.24	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

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,
  `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),
(499677, 408, 24);

Run the following query:
SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId desc;

It should return only the second row, however you will get both rows returned as result.

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

How to repeat:
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),
(499677, 408, 24);

SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 0) as cargo FROM dummy22 GROUP BY classifiedId HAVING cargo = 24 ORDER BY companyId desc;

Hello Hrvoje Novosel,

Thank you for the report and test case.
Observed this with 5.6.24.

Thanks,
Umesh

// 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)

// 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)

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)

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)