Incorrect Filtering Behavior When Using COALESCE in WHERE Clause in TiDB

[aspiringOrange]

Bug Report

1. Minimal reproduce step (Required)

The following SQL statements create tables, insert sample data, and execute queries that demonstrate unexpected behavior:

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

INSERT INTO `t1` VALUES (1);

CREATE TABLE `t2` (
  `c2` int
);

INSERT INTO `t2` VALUES (-71);

Query1:

select  
  coalesce(NULL | (SIGN(STRCMP(case when ( (ref_0.c1) is null) then ('b') else ('b') end, 'a'))), 
                         length(nullif(CHAR(ref_1.c2), 'KV')))    as w_1
from 
  (t1 as ref_0
    right join t2 as ref_1
    on (ref_0.c1 = ref_1.c2))
where coalesce(NULL | (SIGN(STRCMP(case when ( (ref_0.c1) is null) then ('b') else ('b') end, 'a'))), 
                         length(nullif(CHAR(ref_1.c2), 'KV'))) > 0;

Output:

+------+
| w_1  |
+------+
|    4 |
+------+
1 row in set, 2 warnings (0.00 sec)

Query2:

select  
  coalesce(NULL | (SIGN(STRCMP(case when ( (ref_0.c1) is null) then ('b') else ('b') end, 'a'))), 
                         length(nullif(CHAR(ref_1.c2), 'KV')))    as w_1
from 
  (t1 as ref_0
    right join t2 as ref_1
    on (ref_0.c1 = ref_1.c2))
where coalesce(NULL | (SIGN(STRCMP(case when ( (ref_0.c1) is null) then ('b') else ('b') end, 'a'))), 
                         length(nullif(CHAR(ref_1.c2), 'KV'))) > 1;

Output:
Empty set, 2 warnings (0.01 sec)

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

Since the first query returns w_1 = 4, and 4 > 0 and 4 > 1 are both TRUE, the second query should also return at least the same row.

The filtering condition w_1 > 1 should not remove all results, given that w_1 = 4 is valid.

3. What did you see instead (Required)

The second query unexpectedly returns an empty set, even though the computed w_1 value (4) satisfies the filtering condition > 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 |