Bug #116727 Issues with independent subqueries.

• Submitted: 20 Nov 8:27
• Modified: 20 Nov 8:50
• Reporter: PQ L
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.4.2, 8.0.40
• OS: Any
• CPU Architecture: Any

[20 Nov 8:27] PQ L

Description:
The original SQL and the rewritten SQL are identical queries, except that the rewritten SQL includes an additional condition, WHERE L_COMMITDATE IS NOT NULL, in the subquery. However, in the original query, this condition is inherently filtered, making the logic of both queries equivalent.Unexpectedly, the number of returned rows is different, indicating the presence of a bug.

How to repeat:
This link provides the exported sql file for the database:
https://github.com/LLuopeiqi/newtpcd/blob/main/mysql/tpcd.sql

--original
SELECT L_SHIPINSTRUCT, L_EXTENDEDPRICE, L_COMMENT
FROM LINEITEM
WHERE L_SHIPMODE NOT IN (
    SELECT L_COMMITDATE
    FROM LINEITEM
);
return 0 row

--rewritten
SELECT L_SHIPINSTRUCT, L_EXTENDEDPRICE, L_COMMENT
FROM LINEITEM
WHERE L_SHIPMODE NOT IN (
    SELECT L_COMMITDATE
    FROM LINEITEM
    WHERE L_COMMITDATE IS NOT NULL
);
return 5905 rows

[20 Nov 8:50] MySQL Verification Team

Hello PQ L,

Thank you for the report and feedback.

regards,
Umesh