Unexpected Filtering Behavior When Using XOR with Subquery in WHERE Clause in TiDB

[aspiringOrange]

Bug Report

1. Minimal reproduce step (Required)

The following SQL statements create a table, insert sample data, and execute queries that demonstrate inconsistent behavior:

CREATE TABLE `t1` (
  `c1` int
);

INSERT INTO `t1` VALUES (6);

select  
  ref_1.c1 as c_1, 
  1 xor ((((ref_1.c1) <> ( 
        select  
            (ref_1.c1) as c_1
          from 
            t1 as ref_62
          where false
          order by c_1 desc
           limit 1)) 
      and ((null) 
        and (((ref_1.c1)) between (1) and (2))))) as w_2
from 
  t1 as ref_1;

Output:

+------+------+
| c_1  | w_2  |
+------+------+
|    6 |    1 |
+------+------+
1 row in set (0.00 sec)

Now, executing the same expression in the WHERE clause:

SELECT  
  ref_1.c1 AS c_1, 
  1 XOR ((((ref_1.c1) <> ( 
        SELECT  
            (ref_1.c1) AS c_1
          FROM 
            t1 AS ref_62
          WHERE false
          ORDER BY c_1 DESC
          LIMIT 1)) 
      AND ((NULL) 
        AND (((ref_1.c1)) BETWEEN (1) AND (2))))) AS w_2
FROM 
  t1 AS ref_1
WHERE 1 XOR ((((ref_1.c1) <> ( 
        SELECT  
            (ref_1.c1) AS c_1
          FROM 
            t1 AS ref_62
          WHERE false
          ORDER BY c_1 DESC
          LIMIT 1)) 
      AND ((NULL) 
        AND (((ref_1.c1)) BETWEEN (1) AND (2)))));

Unexpected Output:
Empty set (0.00 sec)

2. What did you expect to see? (Required)

Since the first query computes w_2 = 1, the filtering condition WHERE w_2 should not filter out all rows.

The second query should return the same row {6, 1} as the first query.

3. What did you see instead (Required)

The second query unexpectedly returns an empty set, despite the computed w_2 value being 1.

4. What is your TiDB version? (Required)

8.0.11-TiDB
master b6141ec

GoVersion: go1.23.4
Race Enabled: false
Check Table Before Drop: false
Store: unistore |