Bug #116721 Issues with combining COALESCE and DATE functions.

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

Hello PQ L,

Thank you for the report and feedback.

regards,
Umesh