Description:
Incorrect query results when combining COALESCE and DATE.
The two queries are logically equivalent because they achieve the same goal: explicitly expanding the behavior of the COALESCE function into a CASE WHEN statement. The COALESCE function selects the first non-NULL value from a list, and the rewritten CASE WHEN statement achieves the same logic by explicitly checking conditions:
If l_quantity is not NULL, it takes the value of l_quantity.
If l_quantity is NULL but l_suppkey is not NULL, it takes the value of l_suppkey.
If both are NULL, it defaults to 0.21501538554113775.
However, the final results are different, 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 l_extendedprice , l_orderkey , l_discount
from lineitem join nation on nation.n_name >= lineitem.l_linestatus
where (coalesce(l_quantity,
L_SUPPKEY,
0.21501538554113775,
l_receiptdate) )
<= l_returnflag
group by l_extendedprice, l_orderkey, l_discount;
return 6004 rows
--rewritten
SELECT distinct l_extendedprice, l_orderkey, l_discount
FROM lineitem join nation on nation.n_name >= lineitem.l_linestatus
WHERE
CASE WHEN l_quantity IS NOT NULL THEN l_quantity
WHEN l_suppkey IS NOT NULL THEN l_suppkey
else 0.21501538554113775
END <= l_returnflag
GROUP BY l_orderkey, l_extendedprice, l_discount;
return 0 row
