BugReport: Issues with COALESCE function

version

8.3.0

Original sql

SELECT DISTINCT l_shipinstruct, l_extendedprice, l_comment
FROM lineitem
WHERE l_shipmode NOT IN (
    SELECT l_commitdate
    FROM lineitem
    WHERE l_commitdate IS NOT NULL
) AND l_shipmode IS NOT NULL ;

return 0 row

Rewritten sql

SELECT DISTINCT l_shipinstruct, l_extendedprice, l_comment
FROM lineitem
WHERE l_shipmode IS NOT NULL
AND l_commitdate IS NOT NULL
EXCEPT
SELECT DISTINCT l_shipinstruct, l_extendedprice, l_comment
FROM lineitem
WHERE l_shipmode IN (
    SELECT l_commitdate
    FROM lineitem
)AND l_shipmode IS NOT NULL
AND l_commitdate IS NOT NULL;

return 5895 row

Analysis

These two queries are logically equivalent, although they are written differently.

The original query uses a NOT IN subquery to filter rows where l_shipmode is not in the set of l_commitdate (where l_commitdate is not NULL) and where l_shipmode is not NULL. Specifically, the query:

Uses the subquery SELECT l_commitdate FROM lineitem WHERE l_commitdate IS NOT NULL to get all non-NULL values of l_commitdate.
Excludes rows where l_shipmode is in the result set of this subquery, ensuring that l_shipmode is not in any of those l_commitdate values.
The condition l_shipmode IS NOT NULL ensures that only rows where l_shipmode is not NULL are returned.

The rewritten query uses an EXCEPT subquery to achieve the same logic:

The first part of the query returns all rows where both l_shipmode and l_commitdate are not NULL.
The second part (through EXCEPT) returns all rows where l_shipmode is in the set of l_commitdate, and both l_shipmode and l_commitdate are not NULL.
The EXCEPT operator excludes the results of the second part, effectively returning all rows where l_shipmode is not in the l_commitdate set and both columns are not NULL.

The two SQL queries are logically equivalent, but they return different results, indicating the presence of a bug.

How to repeat

The exported file for the database is in the attachment. : (https://github.com/LLuopeiqi/newtpcd/blob/main/tidb/tpcd.sql) .

pingcap / docs