Invalid query result

[Hrvoje Novosel]

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

[Valerii Kravchuk (valerii-kravchuk)]

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` (
    -> `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.47 sec)

mysql>
mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`)
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(ownerCargoGroupId, 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(ownerCargoGroupId, 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?

[Valerii Kravchuk (valerii-kravchuk)]

Confirmed for recent Percona Server, surely:

[openxs@centos ~]$ mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-72.2-log Percona Server (GPL), Release 72.2, Revision 8d0f85b

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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.18 sec)

mysql>
mysql> INSERT INTO `dummy22` (`classifiedId`, `companyId`, `ownerCargoGroupId`) VALUES
    -> (499355, 5397, 12),
    -> (499677, 408, 24);
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> analyze table `dummy22`;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.dummy22 | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.03 sec)

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.04 sec)

mysql> SELECT GROUP_CONCAT(companyId) as grouper, COALESCE(ownerCargoGroupId, 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(ownerCargoGroupId, 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.01 sec)

[Hrvoje Novosel (t-iyfo-b)]

reported it
http://bugs.mysql.com/bug.php?id=77116

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-903