TiDB database documentation. TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
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.
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) .