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

[8 Nov 2017 18:34] Bear Limvere
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)
[9 Nov 2017 6:37] MySQL Verification Team
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.