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)
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)