Unexpected BETWEEN Behavior When Using NULL in TiDB

[aspiringOrange]

Bug Report

1. Minimal reproduce step (Required)

The following SQL statements create a table, insert sample data, and execute a query that demonstrates unexpected behavior:

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

INSERT INTO `t1` VALUES (1);

select  
  ref_1.c1 as c_1, 
  ((ref_1.c1) between (10) and (NULL)) as c_2
from 
    t1 as ref_1

Unexpected Output:

+------+------+
| c_1  | c_2  |
+------+------+
|    1 |    0 |
+------+------+
1 row in set (0.01 sec)

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

According to SQL standard behavior:

The BETWEEN operator should return NULL when one of the boundary values is NULL.

The expected output for 1 BETWEEN 10 AND NULL should be NULL, not 0.

3. What did you see instead (Required)

The query returned 0 instead of NULL.

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 |

[hawkingrei]

I try it in the mysql, it is the same as tidb's result.