Bug #113433 Incorrect query results involving the date type.
Submitted: 15 Dec 2023 1:57 Modified: 15 Dec 2023 8:06
Reporter: huazai huazai Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.35 OS:Any
Assigned to: CPU Architecture:Any
Tags: year

[15 Dec 2023 1:57] huazai huazai
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)
[15 Dec 2023 8:06] MySQL Verification Team
Hello huazai,

Thank you for the report and feedback.
IMHO this is duplicate of Bug #113369, please see Bug #113369.
Even without the partition we can observe the same behavior.

regards,
Umesh