Bug #49478 year(2) datatype index anomalies

• Submitted: 5 Dec 2009 23:08
• Modified: 23 Jun 2015 14:12
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Won't fix
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.89, 5.1.41, 5.1.43
• OS: Any
• Assigned to: Gleb Shchepa
• CPU Architecture: Any
• Tags: year

[5 Dec 2009 23:08] Shane Bester

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.

[6 Dec 2009 6:26] MySQL Verification Team

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

[6 Dec 2009 9:39] Sveta Smirnova

Thank you for the report.

Verified as described.

See also bug #40171

[24 Nov 2010 10:40] Manyi Lu

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

[25 Nov 2010 17:36] MySQL Verification Team

I also vote to obsolete this silly datatype.

[23 Jun 2015 14:12] Morgan Tocker

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".