| Bug #83005 | When the Optimiser is using index for group-by it often gives wrong results | ||
|---|---|---|---|
| Submitted: | 15 Sep 2016 6:56 | Modified: | 9 Jan 2017 17:51 |
| Reporter: | Yoseph Phillips | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.6.10, 5.6.33, 5.7.15, 5.5.52 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[15 Sep 2016 6:56]
Yoseph Phillips
[15 Sep 2016 8:59]
MySQL Verification Team
Hello Yoseph, Thank you for the report and test case. Verified as described with 5.5.52/5.6.33 builds. Thanks, Umesh
[15 Sep 2016 9:00]
MySQL Verification Team
test results
Attachment: 83005.results (application/octet-stream, text), 45.94 KiB.
[9 Jan 2017 17:51]
Paul DuBois
Posted by developer: Noted in 5.6.36, 5.7.18, 8.0.1 changelogs. A query could produce incorrect results if the WHERE clause contained a dependent subquery, the table had a secondary index on the columns in the select list followed by the columns in the subquery, and GROUP BY or DISTINCT permitted the query to use a Loose Index Scan.
[3 May 2017 3:47]
Andrew nicols
We're also seeing this in Moodle. Sample test case: -- Create the test dataset. CREATE DATABASE testing; CONNECT testing; DROP TABLE IF EXISTS `example`; CREATE TABLE `example` ( `id` bigint(10) NOT NULL AUTO_INCREMENT, `idnumber` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `relatedid` bigint(10) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `example_idx` (`relatedid`,`idnumber`) ) ENGINE=InnoDB AUTO_INCREMENT=181000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Example'; INSERT INTO example(relatedid) values (185000); INSERT INTO example(relatedid) values (185000); INSERT INTO example(relatedid) values (185000); INSERT INTO example(relatedid) values (185000); -- And performing the following query: SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001); The expected result is always 1. Any of the following will cause the correct result to be returned always: * swap the order of the unique key around (idnumber, then relatedid) * disabling use_index_extensions * including the first row in the table in the IN Here are the various explains: -- Default installation with no changes. mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001); +----------------------------+ | COUNT(DISTINCT(relatedid)) | +----------------------------+ | 0 | +----------------------------+ 1 row in set (0.00 sec) -- Incorrect result. mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001); +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+ | 1 | SIMPLE | example | range | PRIMARY,example_idx | example_idx | 8 | NULL | 5 | Using where; Using index for group-by (scanning) | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+ 1 row in set (0.00 sec) -- Include the first row in the IN: mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181000); +----------------------------+ | COUNT(DISTINCT(relatedid)) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) -- Correct result. mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181000); +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+ | 1 | SIMPLE | example | range | PRIMARY,example_idx | example_idx | 8 | NULL | 5 | Using where; Using index for group-by (scanning) | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+ 1 row in set (0.00 sec) -- Now disable use_index_extensions: mysql> SET optimizer_switch='use_index_extensions=off'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001); +----------------------------+ | COUNT(DISTINCT(relatedid)) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) -- Correct result. mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001); +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+ | 1 | SIMPLE | example | index | PRIMARY,example_idx | example_idx | 311 | NULL | 4 | Using where; Using index | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) -- Re-enable use_index_extensions and swap order of keys: mysql> ALTER TABLE example DROP KEY example_idx; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE example ADD CONSTRAINT example_idx UNIQUE (idnumber,relatedid); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001); +----------------------------+ | COUNT(DISTINCT(relatedid)) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) -- Correct result. mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001); +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+ | 1 | SIMPLE | example | index | PRIMARY,example_idx | example_idx | 311 | NULL | 4 | Using where; Using index | +----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) -- Swap the key back. mysql> ALTER TABLE example DROP INDEX example_idx; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE example ADD CONSTRAINT example_idx UNIQUE (relatedid, idnumber); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 -- Now delete the current first row. mysql> DELETE FROM example WHERE id = 181000; Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181004, 181001); +----------------------------+ | COUNT(DISTINCT(relatedid)) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) Verified that this is still an issue on: * 5.6.36 * 5.7.18
