Bug in Cast char to YEAR

[curry-oss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t0(c0 CHAR);
CREATE TABLE t1 LIKE t0;
INSERT INTO t0 VALUES ('4');
INSERT INTO t1 VALUES ('1');

SELECT COUNT(*) FROM t1, t0 WHERE NOT (0.7) > (CAST(t1.c0 AS YEAR));
SELECT (CAST(t1.c0 AS YEAR)) FROM t1;

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

see the following case:

3. What did you see instead (Required)

// MySQL 8.4.1
MySQL [test]> SELECT COUNT(*) FROM t1, t0 WHERE NOT (0.7) > (CAST(t1.c0 AS YEAR));
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

MySQL [test]> SELECT (CAST(t1.c0 AS YEAR)) FROM t1;
+-----------------------+
| (CAST(t1.c0 AS YEAR)) |
+-----------------------+
|                  2001 |
+-----------------------+
1 row in set (0.00 sec)

// TiDB
mysql> SELECT COUNT(*) FROM t1, t0 WHERE NOT (0.7) > (CAST(t1.c0 AS YEAR));
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)
mysql> SELECT (CAST(t1.c0 AS YEAR)) FROM t1;
+-----------------------+
| (CAST(t1.c0 AS YEAR)) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.03 sec)

// Interestingly, if I change '>' to '>=', this SQL can execute correctly
mysql> SELECT COUNT(*) FROM t1, t0 WHERE NOT (0.7) >= (CAST(t1.c0 AS YEAR));
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.05 sec)

4. What is your TiDB version? (Required)

TiDB v8.5.0

[curry-oss]

/label affects-8.5