Bug #40171 Type YEAR as 2 digit number gives incorrect search results
Submitted: 20 Oct 2008 11:02 Modified: 9 Dec 2009 14:10
Reporter: Lars-Erik Bjørk Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0/5.1/6.0.8-alpha OS:MacOS
Assigned to: CPU Architecture:Any

[20 Oct 2008 11:02] Lars-Erik Bjørk
Description:
I do the following:

mysql> CREATE TABLE t1 (a_int INT, b_year YEAR(2)) Engine = 'myisam';
Query OK, 0 rows affected (0.68 sec)

mysql> INSERT INTO t1 VALUES (2, 01), (3, 02), (4, 03), (5,04), (6,05), (7,76), (8,77), (9,78), (10,79);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

According to the reference manual,  the YEARS can be given as two-digit numbers in the range 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999. 

Then I issue the following query:

mysql> select * from t1 where b_year < 78;
+-------+--------+
| a_int | b_year |
+-------+--------+
|     2 |     01 | 
|     3 |     02 | 
|     4 |     03 | 
|     5 |     04 | 
|     6 |     05 | 
|     7 |     76 | 
|     8 |     77 | 
+-------+--------+
7 rows in set (0.00 sec)

According to the reference manual, I should only be returned 77 and 76
 
If I add an index on the YEAR field, it works for '<' searches, but not for '>' searches:

mysql> ALTER TABLE t1 ADD INDEX index_year (b_year);
Query OK, 9 rows affected (0.17 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from t1 where b_year < 78;
+-------+--------+
| a_int | b_year |
+-------+--------+
|     7 |     76 | 
|     8 |     77 | 
+-------+--------+
2 rows in set (0.00 sec)

mysql> select * from t1 where b_year > 01;
+-------+--------+
| a_int | b_year |
+-------+--------+
|     3 |     02 | 
|     4 |     03 | 
|     5 |     04 | 
|     6 |     05 | 
|     7 |     76 | 
|     8 |     77 | 
|     9 |     78 | 
|    10 |     79 | 
+-------+--------+
8 rows in set (0.00 sec)

This is the case when using innodb, myisam and maria. Not tested on falcon, as falcon YEAR implementation is currently broken.

However, when the table is createad like this:

mysql> CREATE TABLE t1 (a_int INT, b_year YEAR(4)) Engine = 'myisam';

the above seems to work

How to repeat:
See description
[20 Oct 2008 12:46] MySQL Verification Team
Thank you for the bug report. Verified as described.
[9 Dec 2009 14:10] Evgeny Potemkin
Duplicate of the bug#43668.