Bug #74403 Wrong result from query when semijoin optimization is disabled

• Submitted: 15 Oct 2014 13:23
• Modified: 23 Nov 2015 16:44
• Reporter: Knut Anders Hatlen
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.21
• OS: Any
• CPU Architecture: Any

[15 Oct 2014 13:23] Knut Anders Hatlen

Description:
The query "select 1 from t where (select 1,2 from t where x=2) in (select 1,2 from t)" returns wrong results if the table t is non-empty, the subquery on the left side of the IN predicate returns an empty result, and the semijoin optimization is disabled. The query should return an empty result in that case, but instead returns one row. With default optimizations, the query returns the expected result.

How to repeat:
set optimizer_switch='semijoin=off';
create table t(x int) engine=innodb;
insert into t values (1);
select 1 from t where (select 1,2 from t where x=2) in (select 1,2 from t);

Expected result: Empty
Actual result: One row

[23 Nov 2015 16:44] Paul DuBois

Noted in 5.7.11, 5.8.0 changelogs.

IN-to-EXISTS subquery transformation could yield incorrect results
for queries for which the table was nonempty, the subquery on the
left side of the IN predicate produced an empty result, and semi-join
optimization was disabled.