Description:
These two queries are equivalent because they implement the same filtering logic, despite differences in syntax.
In the original query, COALESCE(-1054112160, lineitem.l_shipdate) evaluates to -1054112160 because it is the first non-NULL value. This value is then compared to lineitem.l_commitdate to filter rows.
In the rewritten query, the CASE WHEN statement explicitly checks whether -1054112160 is not NULL. Since this condition is always true, the THEN branch is executed, returning CAST(-1054112160 AS signed). This result is effectively the same as -1054112160, and it is compared with lineitem.l_commitdate for filtering.
Both queries filter rows based on whether -1054112160 is less than lineitem.l_commitdate. The differences in syntax do not alter the logic, making the queries 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 lineitem.l_partkey as ref0, lineitem.l_extendedprice as ref1
from lineitem
where coalesce(-1054112160, lineitem.l_shipdate) < (lineitem.l_commitdate) ;
return 5905 rows
--rewritten
SELECT l_partkey AS ref0, l_extendedprice AS ref1 FROM lineitem
WHERE CASE WHEN -1054112160 IS NOT NULL
THEN CAST(-1054112160 AS signed)
ELSE l_shipdate END < l_commitdate ;
return 0 row
