Query with NULLIF and CAST in WHERE clause returns incorrect results

[dllggyx]

Bug Report

Hi, I found a logic bug in TiDB 8.5.5

1. Minimal reproduce step

-- create table
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 ( c0 varchar(500)   DEFAULT NULL, c1 decimal(10,0) unsigned  DEFAULT NULL, UNIQUE KEY c1 (c1) );
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( c1 decimal(10,0) unsigned  DEFAULT NULL, UNIQUE KEY c1 (c1) ) ;
INSERT INTO t2 VALUES (NULL),(0000000000);


-- query, expect:{0}, actual: Empty Set
SELECT
  t3._col_27 AS c0 
FROM (
  SELECT
    t_lhs_0.c1 AS _col_26,
    t_rhs_0.c1 AS _col_27
  FROM t0 AS t_lhs_0
  RIGHT JOIN t2 AS t_rhs_0 ON (TRUE)
) AS t3
WHERE
CAST(NULLIF(
  CAST((t3._col_27 IN (FALSE, t3._col_26)) AS CHAR),
  CAST(('a' LIKE t3._col_26) AS CHAR)
) AS DECIMAL(65, 30)) * CAST((-(t3._col_27 >= t3._col_27)) AS DECIMAL(65, 30));

2. What did you expect to see?

The query should return one row with value 0.

3. What did you see instead

The query returned an empty set.

4. What is your TiDB version?

I found this logic bug in TiDB 8.5.5

[ti-chi-bot]

Welcome @dllggyx! It looks like this is your first issue to pingcap/tidb 🎉

[kennedy8312]

/sig execution
/severity critical

[kennedy8312]

tidb-triage-bot rationale

• severity/critical: The query returns incorrect results, which is a core SQL correctness failure.
• sig/execution: The failure is triggered by evaluating a WHERE expression with NULLIF, CAST, and NULL semantics during query execution.