Bug Report: Issues with CASE operation

[LLuopeiqi]

BugReport: Issues with CASE operation

version

8.3.0

Original sql

select all lineitem.l_shipmode as ref0, lineitem.l_tax as ref1 from lineitem 
where (lineitem.l_shipinstruct) > 100
group by lineitem.l_shipmode, lineitem.l_tax;

return 0 row

Rewritten sql

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 72 row

Analysis

These two queries are logically equivalent, although they are written differently.

Original Query: The original query filters rows using the condition lineitem.l_shipinstruct > 100 and groups them by lineitem.l_shipmode and lineitem.l_tax. Since 100 is a constant, it is directly used to compare with lineitem.l_shipinstruct.

Rewritten Query: In the rewritten query, a CASE expression is used for conditional checking. It first checks if 100 is NULL, which it isn't, so it directly returns 100. If 100 were NULL (which is not the case here), it would perform further checks and ultimately choose lineitem.l_discount or lineitem.l_commitdate. Therefore, although the rewritten query introduces the CASE statement, the logic is equivalent to the original query, as both compare lineitem.l_shipinstruct with 100.

The two SQL queries are logically equivalent. Unexpectedly, the number of returned rows is different, indicating the presence of a bug.

How to repeat

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