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: | |
| 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
[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.