Description:
Incorrect query results when combining CASE and DATE.
The first query uses COALESCE and NOT IN to compare l_shipmode and l_commitdate, while the second query uses CASE WHEN and NOT =. However, their essence is the same: if l_shipmode is not NULL, its value is compared to l_commitdate; if they are equal, the row is excluded. If l_shipmode is NULL, then return NULL. The two SQL queries are logically equivalent, but they return different results, 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 all lineitem.l_shipinstruct , lineitem.l_extendedprice , lineitem.l_comment
from lineitem
where (coalesce(lineitem.l_shipmode, null)) not in (lineitem.l_commitdate)
return 5805 rows
--rewritten
SELECT l_shipinstruct , l_extendedprice , l_comment FROM lineitem
WHERE
NOT CASE
WHEN l_shipmode IS NOT NULL
THEN CAST(l_shipmode AS CHAR)
ELSE
NULL
END = l_commitdate ;
return 0 row
