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
