Invalid query result

Bug #1457113 reported by Hrvoje Novosel
6
This bug affects 1 person
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,
  `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

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

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?

Changed in percona-server:
status: New → Incomplete
tags: added: upstream
Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

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)

Revision history for this message
Hrvoje Novosel (t-iyfo-b) wrote :
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.