Description:
On MySQL 9.5.0, two semantically equivalent queries return different row counts.
The original query filters rows with:
WHERE t1.c11 IN (...)
The follow-up query only adds a redundant OR FALSE:
WHERE t1.c11 IN (...) OR FALSE
For any SQL truth value P, P OR FALSE is equivalent to P in a WHERE clause.
Therefore, both queries should return the same rows. However, after rebuilding the
round17 schema and data, the original query returns 0 rows while the OR FALSE
query returns 4 rows.
The execution plans differ in the IN-subquery handling:
Original query:
-> Nested loop semijoin (FirstMatch)
Query with OR FALSE:
-> Filter: <in_optimizer>(t1.c11,<exists>(select #5))
This suggests an incorrect result caused by the optimizer transformation of the
IN subquery into a semijoin.
How to repeat:
DROP DATABASE IF EXISTS rift_pair816_min;
CREATE DATABASE rift_pair816_min;
USE rift_pair816_min;
CREATE TABLE t1 (
a INT
);
CREATE TABLE t2 (
s SET('a')
);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES ('a');
-- These two predicates are logically equivalent:
-- P
-- P OR FALSE
--
-- Expected: both counts are equal.
-- Observed on MySQL 9.5.0: original_count = 0, mutated_count = 1.
SELECT COUNT(*) AS original_count
FROM t2
WHERE s IN (SELECT 0 FROM t1);
SELECT COUNT(*) AS mutated_count
FROM t2
WHERE s IN (SELECT 0 FROM t1) OR FALSE;
Description: On MySQL 9.5.0, two semantically equivalent queries return different row counts. The original query filters rows with: WHERE t1.c11 IN (...) The follow-up query only adds a redundant OR FALSE: WHERE t1.c11 IN (...) OR FALSE For any SQL truth value P, P OR FALSE is equivalent to P in a WHERE clause. Therefore, both queries should return the same rows. However, after rebuilding the round17 schema and data, the original query returns 0 rows while the OR FALSE query returns 4 rows. The execution plans differ in the IN-subquery handling: Original query: -> Nested loop semijoin (FirstMatch) Query with OR FALSE: -> Filter: <in_optimizer>(t1.c11,<exists>(select #5)) This suggests an incorrect result caused by the optimizer transformation of the IN subquery into a semijoin. How to repeat: DROP DATABASE IF EXISTS rift_pair816_min; CREATE DATABASE rift_pair816_min; USE rift_pair816_min; CREATE TABLE t1 ( a INT ); CREATE TABLE t2 ( s SET('a') ); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES ('a'); -- These two predicates are logically equivalent: -- P -- P OR FALSE -- -- Expected: both counts are equal. -- Observed on MySQL 9.5.0: original_count = 0, mutated_count = 1. SELECT COUNT(*) AS original_count FROM t2 WHERE s IN (SELECT 0 FROM t1); SELECT COUNT(*) AS mutated_count FROM t2 WHERE s IN (SELECT 0 FROM t1) OR FALSE;