Description:
Issues with the COALESCE function.
The two queries are equivalent because they are based on the same logic: filtering lineitem.l_shipinstruct to satisfy the condition of being greater than a specific value, while grouping by lineitem.l_shipmode and lineitem.l_tax. Although the original query directly uses the fixed value 100, and the rewritten query dynamically selects the first non-NULL value using COALESCE, both express the same filtering and grouping intent.
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_shipmode as ref0, lineitem.l_tax as ref1 from lineitem
where (lineitem.l_shipinstruct) > 100
group by lineitem.l_shipmode, lineitem.l_tax;
return 0 row
--rewritten
select all lineitem.l_shipmode as ref0, lineitem.l_tax as ref1 from lineitem
where (lineitem.l_shipinstruct) >
(coalesce(100, lineitem.l_discount, lineitem.l_commitdate))
group by lineitem.l_shipmode, lineitem.l_tax;
return 72 rows
