Bug #49478 year(2) datatype index anomalies

Description:
year(2) datatype is very odd.  Firstly, it's completely lossy, and you cannot use mysqldump/restore to restore it properly.  This fact should be documented.

Then, lookups are strange, depending on indexes.  Testcase output:

mysql> #following two should return the same
mysql> select * from `t1` force index(`a`) where `a`=27;
Empty set (0.00 sec)

mysql> select * from `t1` ignore index(`a`) where `a`=27;
+----+---+
| a  | b |
+----+---+
| 27 | 1 |
| 27 | 1 |
+----+---+
2 rows in set (0.00 sec)

mysql>
mysql> #following two should return the same
mysql> select * from `t1` force index(`a`) where `a`=1927;
+----+---+
| a  | b |
+----+---+
| 27 | 1 |
+----+---+
1 row in set (0.00 sec)

mysql> select * from `t1` ignore index(`a`) where `a`=1927;
+----+---+
| a  | b |
+----+---+
| 27 | 1 |
| 27 | 1 |
+----+---+
2 rows in set (0.00 sec)

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` year(2),`b` int,primary key(`b`,`a`),key(`a`))engine=myisam;
insert into `t1` set `a`=1927,`b`=1;
insert into `t1` set `a`=2127,`b`=1;
select * from `t1`;

#following two should return the same
select * from `t1` force index(`a`) where `a`=27; 
select * from `t1` ignore index(`a`) where `a`=27; 

#following two should return the same
select * from `t1` force index(`a`) where `a`=1927;
select * from `t1` ignore index(`a`) where `a`=1927;

Final test, try mysqldump and restore the table.  There will be a duplicate key error.

Suggested fix:
personally I think year(2) datatype should be gotten rid of totally.

maybe this is a duplicate of recently fixed bug #43668 ??

Thank you for the report.

Verified as described.

See also bug #40171

Please retriage based on R/E evaluation. We plan create a separate worklog to solve several related bugs in this area in upcoming releases.

I also vote to obsolete this silly datatype.

Posted by developer:
 
The YEAR(2) data type was deprecated in MySQL 5.6 and removed from MySQL 5.7.  For this reason, I am closing this bug as "won't fix".