Bug #116720 Issues with the combination of CASE and DATE.

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

Hello PQ L,

Thank you for the report and feedback.

regards,
Umesh