MySQL Bugs: #88412: SELECT DISTINCT returns "empty set" when data exists.

Bug #88412	SELECT DISTINCT returns "empty set" when data exists.
Submitted:	8 Nov 2017 18:34	Modified:	9 Nov 2017 6:37
Reporter:	Bear Limvere	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.7.20	OS:	CentOS (6.7 (Final))
Assigned to:		CPU Architecture:	Any
Tags:	distinct

Description:
SELECT DISTINCT returns "empty set" when SELECT using primary key values in
an IN() condition statement when a unique index of two non-primary columns
is defined for the InnoDB table. It does not change the behavior if the two
columns in the UNIQUE KEY are BIGINT/VARCHAR or BIGINT/BIGINT.

SELECT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
-- +----------+
-- | group_id |
-- +----------+
-- |   500001 |
-- |   500001 |
-- |   500001 |
-- |   500001 |
-- +----------+
-- 4 rows in set

> SELECT DISTINCT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
-- Empty set (0.00 sec)

This works correctly in versions 5.6.27 and 5.7.10
but breaks in 5.7.20

SHOW VARIABLES LIKE '%version%';
-- +-------------------------+------------------------------+
-- | Variable_name           | Value                        |
-- +-------------------------+------------------------------+
-- | innodb_version          | 5.7.20                       |
-- | protocol_version        | 10                           |
-- | tls_version             | TLSv1,TLSv1.1                |
-- | version                 | 5.7.20                       |
-- | version_comment         | MySQL Community Server (GPL) |
-- | version_compile_machine | x86_64                       |
-- | version_compile_os      | linux-glibc2.12              |
-- +-------------------------+------------------------------+

How to repeat:
CREATE DATABASE atest;
USE atest;

DROP TABLE IF EXISTS test;
CREATE TABLE test (
	item_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	group_id BIGINT(20) UNSIGNED NOT NULL,
	display_item_num VARCHAR(64) NOT NULL,
	PRIMARY KEY (item_id)
);

BEGIN;
INSERT INTO test VALUES
	(200000001,100001,'25'),
	(200000002,500001,'123-143-1'),
	(200000003,500001,'123-147-1'),
	(200000004,500001,'abc2'),
	(200000005,500001,'abc3'),
	(200000006,500001,'abc4'),
	(200000007,500001,'abc5'),
	(200000008,500001,'abc6'),
	(200000009,500001,'abc7'),
	(200000010,500004,'abc8');
COMMIT;

SELECT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
-- +----------+
-- | group_id |
-- +----------+
-- |   500001 |
-- |   500001 |
-- |   500001 |
-- |   500001 |
-- +----------+
-- 4 rows in set

SELECT DISTINCT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
-- +----------+
-- | group_id |
-- +----------+
-- |   500001 |
-- +----------+
-- 1 row in set

ALTER TABLE test ADD UNIQUE KEY group_item (group_id, display_item_num);
-- Query OK, 0 rows affected (0.02 sec)
-- Records: 0  Duplicates: 0  Warnings: 0

SELECT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
-- +----------+
-- | group_id |
-- +----------+
-- |   500001 |
-- |   500001 |
-- |   500001 |
-- |   500001 |
-- +----------+
-- 4 rows in set

SELECT DISTINCT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
-- Empty set (0.00 sec)

Hello Bear Limvere,

Thank you for the report and test case.
Confirmed with optimizer Developer and concluded that this is a duplicate of Bug #87207 and per change log fixed in 5.6.39, 5.7.21, 8.0.4.

"Incorrect results could occur on a table with a unique index when the
optimizer chose a loose index scan even though the unique index had
no index extensions."

Confirmed with 5.7.21 build that it is fixed:

== 5.7.21
 
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.21-linux-x86_64: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
 
Copyright (c) 2000, 2017, 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> uCREATE DATABASE atest;
USE atest;
 
DROP TABLE IF EXISTS test;
CREATE TABLE test (
        item_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        group_id BERROR 1064 (42000): IGINYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uCREATE DATABASE atest' at line 1T(
20) UNSIGNED NOT NULL,
        display_item_num VARCHAR(64) NOT NULL,
        PRIMARY KEY (item_id)
);
 
BEGIN;
INSERT INTO test VALUES
        (200000001,100001,'25'),
        (20000mysql> 0002,5000USE atest;
ERROR 1049 (42000): Unknown database 'atest'
mysql>
mysql> DROP TABLE IF EXISTS test;
ERROR 1046 (3D000): No database selected
mysql> CREATE TABLE test (
    -> item_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> group_id BIGINT(20) UNSIGNED NOT NULL,
    -> display_item_num VARCHAR(64) NOT NULL,
    -> PRIMARY KEY (item_id)
    -> );
ERROR 1046 (3D000): No database selected
mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO test VALUES
    -> (200000001,100001,'25'),
    -> (200000002,500001,'123-143-1'),
    -> (200000003,500001,'123-147-1'),
    -> (200000004,500001,'abc2'),
    -> (200000005,500001,'abc3'),
    -> (200000006,500001,'abc4'),
    -> (200000007,500001,'abc5'),
    -> (200000008,500001,'abc6'),
    -> (200000009,500001,'abc7'),
    -> (200000010,500004,'abc8');
ERROR 1046 (3D000): No database selected
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE DATABASE atest;
USE atest;
DROP TABLE IF EXISTS test;
CREATE TABLE test (
        item_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        group_id BIGINT(20) UNSIGNED NOT NULL,
        display_item_num VARCHAR(64) NOT NULL,
        PRIMARY KEY (item_id)
);
 
BEGIN;
INSERT INTO test VALUES
        (200000001,100001,'25'),
        (200000002,500001,'123-143-1'),
        (200000003,500001,'123-147-1'),
        (200000004,500001,'abc2'),
Query OK, 1 row affected (0.00 sec)
 
mysql> USE atest;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> CREATE TABLE test (
    -> item_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> group_id BIGINT(20) UNSIGNED NOT NULL,
    -> display_item_num VARCHAR(64) NOT NULL,
    -> PRIMARY KEY (item_id)
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO test VALUES
    -> (200000001,100001,'25'),
    -> (200000002,500001,'123-143-1'),
    -> (200000003,500001,'123-147-1'),
    -> (200000004,500001,'abc2'),
    -> (200000005,500001,'abc3'),
    -> (200000006,500001,'abc4'),
    -> (200000007,500001,'abc5'),
    -> (200000008,500001,'abc6'),
    -> (200000009,500001,'abc7'),
    -> (200000010,500004,'abc8');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0
 
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> SELECT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
+----------+
| group_id |
+----------+
|   500001 |
|   500001 |
|   500001 |
|   500001 |
+----------+
4 rows in set (0.00 sec)
 
mysql> SELECT DISTINCT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
+----------+
| group_id |
+----------+
|   500001 |
+----------+
1 row in set (0.00 sec)
 
mysql> ALTER TABLE test ADD UNIQUE KEY group_item (group_id, display_item_num);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SELECT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
+----------+
| group_id |
+----------+
|   500001 |
|   500001 |
|   500001 |
|   500001 |
+----------+
4 rows in set (0.00 sec)
 
mysql> SELECT DISTINCT group_id FROM test WHERE item_id IN (200000005, 200000006, 200000007, 200000008);
+----------+
| group_id |
+----------+
|   500001 |
+----------+
1 row in set (0.00 sec)
cat docs/INFO_SRC
commit: ddfbfcfdfcb4f77f72e459a293686a2be27af112
date: 2017-11-08 17:46:12 +0530
build-date: 2017-11-08 18:00:52 +0100
short: ddfbfcf
branch: mysql-5.7

MySQL source 5.7.21

Thanks,
Umesh