Bug Report

[MyKWK]

BUG Report

TiDB 8.3.0

Original Query

select all l_extendedprice , l_orderkey , l_discount
from lineitem
where (coalesce(l_quantity,
l_suppkey,
0.21501538554113775,
l_receiptdate) )
<= l_returnflag
group by l_extendedprice, l_orderkey, l_discount;
return : 6005 rows

Rewritten Query

SELECT l_extendedprice, l_orderkey, l_discount FROM lineitem
WHERE CASE
WHEN l_quantity IS NOT NULL THEN CAST(l_quantity AS decimal(15,2))
WHEN l_suppkey IS NOT NULL THEN CAST(l_suppkey AS unsigned)
else 0.21501538554113775
END <= l_returnflag
GROUP BY l_orderkey, l_extendedprice, l_discount
return 0 rows

Analysis

In the rewritten query, the COALESCE function is replaced with a CASE expression without altering its semantics. In the original query, COALESCE filters through values to identify the first non-NULL value. Due to the presence of a constant before the final parameter, the last argument in the original expression is redundant. This behavior aligns with the semantics conveyed by the equivalent CASE expression. Furthermore, the use of the CAST function within the CASE expression does not introduce any semantic changes. As such, the two formulations are logically equivalent.

How to repeat ?

The exported file for the database is in the attachment. : (https://github.com/LLuopeiqi/newtpcd/blob/main/tidb/tpcd.sql) .