MySQL Bugs: #88670: Subquery incorrectly shows duplicate values on subqueries.

Bug #88670	Subquery incorrectly shows duplicate values on subqueries.
Submitted:	27 Nov 2017 22:56	Modified:	17 Aug 2018 23:38
Reporter:	Mark El-Wakil	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.20, 5.6.38	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It appears there is an issue with how MySQL deals with subqueries when the subquery is a unique index.

If you look at the below example:

SELECT p.Id FROM (p) WHERE p.Id IN (select s.Id from s WHERE Id=1 AND u IS NULL) ORDER BY Id desc;

The subquery (table s) is looking at a query that utilizes the index `o`. This query is a unique key.

However, since the unique key allows for NULL values, MySQL allows for duplicate instances of NULL to exist in this table.

In this case, the select query will return multiple values, even though table `p` only has one row.

This does not happen on older instances of MySQL, such a MySQL 5.5.

How to repeat:
DROP TABLE IF EXISTS p;
DROP TABLE IF EXISTS s;

CREATE TABLE `p` (`Id` int(11),PRIMARY KEY (`Id`));
CREATE TABLE `s` (`Id` int(11), `u` int(11), UNIQUE KEY o(Id, u) );

insert into s VALUES (1, NULL),(1, NULL);
insert into p VALUES (1);

SELECT p.Id FROM (p) WHERE p.Id IN (select s.Id from s WHERE Id=1 AND u IS NULL) ORDER BY Id desc;

Note: If the subquery does not utilize this index, only one value is returned:

SELECT p.Id FROM (p) WHERE p.Id IN (select s.Id from s) ORDER BY Id desc;

Suggested fix:
Since this does not happen in MySQL 5.5, I recommend adding an additional check to see whether or not to rewrite the subquery as a join based on whether or not the unique index can contain null values (and therefore, duplicates).

Please let me know if this is unclear, or if there is any other way I can demonstrate this issue.

Hello Mark El-Wakil,

Thank you for the report and test case.

Thanks,
Umesh

Documented fix as follows in the MySQL 5.6.42, 5.7.24, and 8.0.13 changelogs:

    It was possible for a subquery that used a unique key on a
    column allowing NULL to return multiple rows.

Closed.