BugReport: Issues with COALESCE function

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) .

Start by loading the attached tidb/tpcd.sql data and running both queries against TiDB 8.3.0 to confirm the differing results. Compare the NOT IN and EXCEPT behavior, then determine whether the issue belongs in TiDB rather than this documentation repository; done requires a confirmed diagnosis and a clear documentation or issue update.