MySQL Bugs: #39342: YEAR '=' comparison fails when index is present

Bug #39342	YEAR '=' comparison fails when index is present
Submitted:	9 Sep 2008 14:14	Modified:	9 Jan 2009 13:57
Reporter:	John Embretsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.7	OS:	Any
Assigned to:	Lars-Erik BjÃ¸rk	CPU Architecture:	Any
Tags:	F_ENCODING

Description:
With storage engine Falcon, equals (=) comparison of values of type YEAR does not work when an index is present on the YEAR column(s).

Example query, table t1 (int, year) contains (1, 2008): 
mysql> SELECT * FROM t1 WHERE b_year = 2008;
Empty set (0.00 sec)

The query returns nothing even though t1 contains a row with the value 2008 in the b_year column. If an index is not present, the query returns the expected row.

Results are the same whether values are stored as numbers (e.g. 2008) or strings (e.g. '2008'), 4-digit or 2-digit.

LIKE comparison seems to work always, however:

mysql> SELECT * FROM t1 WHERE b_year LIKE '2008';
+-------+--------+
| a_int | b_year |
+-------+--------+
|     1 |   2008 |
+-------+--------+
1 row in set (0.00 sec)

Storage engines InnoDB, MyISAM and Maria do not exhibit this behavior - instead they return the expected data (one row) from the example described above.

How to repeat:
mysql> CREATE TABLE t1 (a_int INT, b_year YEAR) Engine = 'Falcon';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1, 2008);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE b_year = 2008;
+-------+--------+
| a_int | b_year |
+-------+--------+
|     1 |   2008 |
+-------+--------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM t1 WHERE b_year = 2008;
Empty set (0.00 sec)

mysql> SELECT * FROM t1;
+-------+--------+
| a_int | b_year |
+-------+--------+
|     1 |   2008 |
+-------+--------+
1 row in set (0.00 sec)

Suggested fix:
Make '=' comparison work with data type YEAR when index is present.

Otherwise, update documentation to mention that this is not supposed to work, and the reason why.
Related docs:
http://dev.mysql.com/doc/refman/6.0/en/year.html
http://dev.mysql.com/doc/refman/6.0/en/date-and-time-types.html
http://dev.mysql.com/doc/refman/6.0/en/se-falcon-limits.html
http://dev.mysql.com/doc/refman/6.0/en/storage-requirements.html

Thank you for the bug report. Verified as described on bzr server and older released version 6.0.2.

Same issue with larger than operator, but not with less than operator:

--starting with no index in b_year column...

mysql> SELECT * FROM t1 WHERE b_year < 2009;
+-------+--------+
| a_int | b_year |
+-------+--------+
|     1 |   2008 |
+-------+--------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM t1 WHERE b_year < 2009;
Empty set (0.00 sec)

mysql> SELECT * FROM t1 WHERE b_year > 2007;
+-------+--------+
| a_int | b_year |
+-------+--------+
|     1 |   2008 |
+-------+--------+
1 row in set (0.00 sec)

Attached regression test case for this issue.

Attachment: falcon_bug_39342.test (application/octet-stream, text), 1.46 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56660

2872 lars-erik.bjork@sun.com	2008-10-21
      This is a fix for bug#39342 (YEAR '=' comparison fails when index is 
      present)
      
      According to the reference documentation, YEARSs can be declared as
      YEAR(2) or YEAR(4) to specify a display width of two or four 
      characters. It turns out that YEARs are stored differently in Falcon 
      depending on this. In the case of a YEAR(4), when searching
      an index, the search keys given where off by -1900 years, and the 
      search did therefore not find the expected result. 
      This patch ensures that both types of YEARs are stored equally 
      inside Falcon, in a format that matches the search keys given by 
      the server. The change is limited to StorageInterface::encodeRecord
      and StorageInterface::decodeRecord.
      
      A regression test is also added as a part of this commit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56687

2873 lars-erik.bjork@sun.com	2008-10-21
      This commit introduces two new tests for the YEAR type in Falcon
      and is a part of the patch for bug#39342.
      
      The first test tests the 2 digit version of the YEAR type. This
      test will currently break due to a bug in the server (40171).
      
      The second test tests the 4 digit version of the YEAR type. This
      test should pass with the fix provided in another commit for this 
      patch.
      
      Both tests test 2 digit, 4 digit, and character input in combination
      with indexes and LIMITs. Also some functions are tested. I encourage
      the reviewer to pay most attention to the result file for the 2 digit
      test, as it is hard to confirm the correctness of the expected output
      on a test that currently is not runnable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57061

2873 lars-erik.bjork@sun.com	2008-10-26
      This commit introduces a new tests for the YEAR type in Falcon and is
      a part of the patch for bug#39342.
      
      The test tests the 4 digit version of the YEAR type. This test should
      pass with the fix provided in another commit for this patch.  The test
      tests 2 digit, 4 digit, and character input in combination with indexes
      and LIMITs. Also some functions are tested.
      
      I have also created a similar test for the 2 digit version of YEAR. This test
      will currently break due to a bug in the server (40171), and is
      therefore not part of this commit, but will be attached to the bug
      report (40171).

Pushed into 6.0.8-alpha  (revid:lars-erik.bjork@sun.com-20081026075906-tmkwkwb5cglle335) (version source revid:alik@mysql.com-20081027140343-anwg01vprlohj34t) (pib:5)

A note has been added to the 6.0.8 changelog: 

When using a Falcon table, equals (=) comparison of values of columns of type YEAR does not work when an index is present on the YEAR column(s)