Bug #116727 Issues with independent subqueries.
Submitted: 20 Nov 2024 8:27 Modified: 20 Nov 2024 8:50
Reporter: PeiQi Luo Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.4.2, 8.0.40 OS:Any
Assigned to: CPU Architecture:Any

[20 Nov 2024 8:27] PeiQi Luo 
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 2024 8:50] MySQL Verification Team 
Hello PQ L,

Thank you for the report and feedback.

regards,
Umesh