Description:
The SELECT results are inconsistent when the year(4) type columns are used in NOT IN expression as index range conditions.
mysql> create table t (a year(4), key(a));
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into t values (0), (1), (69), (70), (71);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t where a not in (70);
+------+
| a |
+------+
| 0000 |
| 1971 |
| 2001 |
| 2069 |
+------+
4 rows in set (0.00 sec)
mysql> select * from t where a not in (1, 70);
+------+
| a |
+------+
| 0000 |
| 1970 |
| 1971 |
| 2001 |
| 2069 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t where a not in (70, 71);
+------+
| a |
+------+
| 0000 |
| 2001 |
| 2069 |
+------+
3 rows in set (0.00 sec)
How to repeat:
drop table if exists t;
create table t (a year(4), key(a));
insert into t values (0), (1), (69), (70), (71);
select * from t where a not in (70);
select * from t where a not in (1, 70);
select * from t where a not in (70, 71);
Suggested fix:
The results should either be: (mapping 70-99, 1-69 to 1970-2069)
mysql> select * from t where a not in (70);
+------+
| a |
+------+
| 0000 |
| 1971 |
| 2001 |
| 2069 |
+------+
4 rows in set (0.00 sec)
mysql> select * from t where a not in (1, 70);
+------+
| a |
+------+
| 0000 |
| 1971 |
| 2069 |
+------+
3 rows in set (0.00 sec)
mysql> select * from t where a not in (70, 71);
+------+
| a |
+------+
| 0000 |
| 2001 |
| 2069 |
+------+
3 rows in set (0.00 sec)
Or: (without mapping)
mysql> select * from t where a not in (70);
+------+
| a |
+------+
| 0000 |
| 1970 |
| 1971 |
| 2001 |
| 2069 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t where a not in (1, 70);
+------+
| a |
+------+
| 0000 |
| 1970 |
| 1971 |
| 2001 |
| 2069 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t where a not in (70, 71);
+------+
| a |
+------+
| 0000 |
| 1970 |
| 1971 |
| 2001 |
| 2069 |
+------+
5 rows in set (0.00 sec)
