Description:
When querying a YEAR column with an integer IN list, MySQL fails to correctly convert individual integers to the corresponding YEAR values。
mysql> select * from test where year in (1);
+------+--------+
| year | status |
+------+--------+
| 2001 | 2 |
| 2001 | 3 |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from test where year in (2);
+------+--------+
| year | status |
+------+--------+
| 2002 | 1 |
+------+--------+
1 row in set (0.00 sec)
mysql> select * from test where year in (1,2);
Empty set (0.00 sec)
mysql> select * from test where year in ('1','2');
+------+--------+
| year | status |
+------+--------+
| 2001 | 2 |
| 2001 | 3 |
| 2002 | 1 |
+------+--------+
Expected behavior: IN (1,2) should return the union of the results of IN (1) and IN (2).
Observed behavior: The query returns an empty set because the integer values in the IN list are not individually converted to YEAR values as expected, leading to a mismatch with the results of single-value IN comparisons.
How to repeat:
CREATE TABLE test (
year YEAR NOT NULL,
status INT NOT NULL,
KEY idx_year (year),
KEY idx_year_status (year, status)
)DEFAULT CHARSET=utf8mb4;
INSERT INTO test (year, status) VALUES
('2000', 1), ('2001', 3), ('2001', 2), ('2002', 1), ('2003', 3),
('2005', 1), ('2008', 2), ('2010', 3), ('2015', 2), ('2069', 1),
('2098', 3), ('2099', 2), ('2100', 3), ('0000', 3);
select * from test where year in (1);
select * from test where year in (2);
select * from test where year in (1,2);
select * from test where year in ('1','2');
Description: When querying a YEAR column with an integer IN list, MySQL fails to correctly convert individual integers to the corresponding YEAR values。 mysql> select * from test where year in (1); +------+--------+ | year | status | +------+--------+ | 2001 | 2 | | 2001 | 3 | +------+--------+ 2 rows in set (0.00 sec) mysql> select * from test where year in (2); +------+--------+ | year | status | +------+--------+ | 2002 | 1 | +------+--------+ 1 row in set (0.00 sec) mysql> select * from test where year in (1,2); Empty set (0.00 sec) mysql> select * from test where year in ('1','2'); +------+--------+ | year | status | +------+--------+ | 2001 | 2 | | 2001 | 3 | | 2002 | 1 | +------+--------+ Expected behavior: IN (1,2) should return the union of the results of IN (1) and IN (2). Observed behavior: The query returns an empty set because the integer values in the IN list are not individually converted to YEAR values as expected, leading to a mismatch with the results of single-value IN comparisons. How to repeat: CREATE TABLE test ( year YEAR NOT NULL, status INT NOT NULL, KEY idx_year (year), KEY idx_year_status (year, status) )DEFAULT CHARSET=utf8mb4; INSERT INTO test (year, status) VALUES ('2000', 1), ('2001', 3), ('2001', 2), ('2002', 1), ('2003', 3), ('2005', 1), ('2008', 2), ('2010', 3), ('2015', 2), ('2069', 1), ('2098', 3), ('2099', 2), ('2100', 3), ('0000', 3); select * from test where year in (1); select * from test where year in (2); select * from test where year in (1,2); select * from test where year in ('1','2');