Description:
When the year type is compared with the int type, the results are inconsistent.
How to repeat:
1.Operation Procedure
CREATE TABLE `tt` (
`id_col` int NOT NULL,
`bigint_col` bigint NOT NULL,
`year_col` year DEFAULT NULL,
KEY `ndx_year_col` (`year_col` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT into tt values (61,-9223372036854775808,'1913');
SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col;
SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col AND A.id_col = 61;
SELECT A.year_col, A.id_col FROM tt A WHERE 61 < A.year_col;
2.MySQL 8.0.35 execution result
mysql> use test;
Database changed
mysql> CREATE TABLE `tt` (
-> `id_col` int NOT NULL,
-> `bigint_col` bigint NOT NULL,
-> `year_col` year DEFAULT NULL,
-> KEY `ndx_year_col` (`year_col` DESC)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT into tt values (61,-9223372036854775808,'1913');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col;
+----------+--------+
| year_col | id_col |
+----------+--------+
| 1913 | 61 |
+----------+--------+
1 row in set (0.00 sec)
mysql> SELECT A.year_col, A.id_col FROM tt A WHERE A.id_col < A.year_col AND A.id_col = 61;
Empty set (0.00 sec)
mysql> SELECT A.year_col, A.id_col FROM tt A WHERE 61 < A.year_col;
Empty set (0.00 sec)