MySQL Bugs: #49480: WHERE using YEAR columns returns unexpected results

Bug #49480	WHERE using YEAR columns returns unexpected results
Submitted:	6 Dec 2009 1:19	Modified:	12 Mar 2010 16:38
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.1.42, 5.1.43	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	regression

Description:
After latest pushes into mysql-5.1 tree, queries which use YEAR columns in WHERE clauses started working differently, and the results don't seem to be correct.

Below are the results of 4 selects provided in 'How to repeat' section:

Table contents:
---------------

mysql> SELECT * FROM t2;
+------+------+
| c1   | c2   |
+------+------+
| 1901 | NULL |
| 1999 |   99 |
| NULL |   00 |
+------+------+
3 rows in set (0.00 sec)

Current mysql-5.1 tree (of Dec 2, 5.1.43):
-----------------------------------------

mysql> SELECT * FROM t2 WHERE c1=NULL;
+------+------+
| c1   | c2   |
+------+------+
| 1901 | NULL |
| 1999 |   99 |
| NULL |   00 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2 WHERE c1=NOW();
+------+------+
| c1   | c2   |
+------+------+
| NULL |   00 |
+------+------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t2 WHERE c2=99;
+------+------+
| c1   | c2   |
+------+------+
| 1901 | NULL |
+------+------+
1 row in set (0.00 sec)

Previous tree (of Nov 17, 5.1.42):
----------------------------------

mysql> SELECT * FROM t2 WHERE c1=NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM t2 WHERE c1=NOW();
Empty set (0.00 sec)

mysql> SELECT * FROM t2 WHERE c2=99;
+------+------+
| c1   | c2   |
+------+------+
| 1999 |   99 |
+------+------+
1 row in set (0.00 sec)

How to repeat:
USE test;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(c1 YEAR(4), c2 YEAR(2));
INSERT INTO t2 VALUES (1901,NULL),(1999,1999),(NULL,2000);
SELECT * FROM t2;
SELECT * FROM t2 WHERE c1=NULL;
SELECT * FROM t2 WHERE c1=NOW();
SELECT * FROM t2 WHERE c2=99;

I'm adding 5.1.42 into the list of versions: the big push which brought the change happened on Dec 2 when the version was still 5.1.42, clone-off happened later on the same day.

Bugfix for bug#43668 looks related.

# Test case #2:

USE test;
DROP TABLE IF EXISTS t4;
CREATE TABLE t4(c1 YEAR(2));
INSERT INTO t4 VALUES (1970),(2000),(2155),(30);
SELECT * FROM t4;
SELECT c1 FROM t4 WHERE c1 < '55';
SELECT c1 FROM t4 WHERE c1 > 30;

Results:

5.1.43/42 (since Dec 2):

mysql> SELECT * FROM t4;
+------+
| c1   |
+------+
|   70 |
|   00 |
|   55 |
|   30 |
+------+
4 rows in set (0.00 sec)

mysql> SELECT c1 FROM t4 WHERE c1 < '55';
Empty set (0.00 sec)

mysql> SELECT c1 FROM t4 WHERE c1 > 30;
+------+
| c1   |
+------+
|   70 |
|   00 |
|   55 |
|   30 |
+------+
4 rows in set (0.00 sec)

5.1.41/42 (before Dec 2):

mysql> SELECT * FROM t4;
+------+
| c1   |
+------+
|   70 |
|   00 |
|   55 |
|   30 |
+------+
4 rows in set (0.00 sec)

mysql> SELECT c1 FROM t4 WHERE c1 < '55';
+------+
| c1   |
+------+
|   00 |
|   30 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT c1 FROM t4 WHERE c1 > 30;
+------+
| c1   |
+------+
|   70 |
|   55 |
+------+
2 rows in set (0.00 sec)

Thank you for the report.

Verified as described.

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/93033

3237 Gleb Shchepa	2009-12-07
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      A few problems were found in the fix for bug 43668:
      1) Comparison of the YEAR column with NULL always returned TRUE;
      2) Comparison of the YEAR column with constants returned
         unpredictable result;
      3) Unnecessary conversion warnings when comparing a non-integer
         constant with a NULL value in the YEAR column;
      
      The problems described above have been resolved.
      
      To implement later (there is no regression): constant conversion
      cache has to be used when comparing the YEAR column with a
      non-integer constant.
     @ mysql-test/r/type_year.result
        Test case for bug #49480.
     @ mysql-test/t/type_year.test
        Test case for bug #49480.
     @ sql/item_cmpfunc.cc
        - The get_year_value() function has been modified to make its
          return value compatible with the get_datetime_value() return
          value (i.e. to convert numeric values into the YYYY0000000000
          (YYYY-00-00 00:00:00) form.
          From now this function is able to accept any input item
          with a type that doesn't belong to Item::is_datetime() types
          (i.e. other than MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME and
          MYSQL_TYPE_TIMESTAMP).
        
        - The Arg_comparator::set_cmp_func method has been modified to
          use the get_year_value function if get_datetime_value() is not
          applicable.
        
        - The Arg_comparator::compare_year method has been removed
          since get_year_value() is compatible with the old
          Arg_comparator::compare_datetime method that doesn't have
          problems #1-#3 (see whole patch entry commentary).
     @ sql/item_cmpfunc.h
        Unnecessary Arg_comparator::year_as_datetime and
        Arg_comparator::compare_year() declarations have been removed.

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/93077

3237 Gleb Shchepa	2009-12-07
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      A few problems were found in the fix for bug 43668:
      1) Comparison of the YEAR column with NULL always returned TRUE;
      2) Comparison of the YEAR column with constants returned
         unpredictable result;
      3) Unnecessary conversion warnings when comparing a non-integer
         constant with a NULL value in the YEAR column;
      
      The problems described above have been resolved.
      Also constant conversion cache optimization has been used when 
      comparing the YEAR column with a non-integer constant.
     @ mysql-test/r/type_year.result
        Test case for bug #49480.
     @ mysql-test/t/type_year.test
        Test case for bug #49480.
     @ sql/item_cmpfunc.cc
        - The get_year_value() function has been modified to make its
          return value compatible with the get_datetime_value() return
          value (i.e. to convert numeric values into the YYYY0000000000
          (YYYY-00-00 00:00:00) form.
          From now this function is able to accept any input item
          with a type that doesn't belong to Item::is_datetime() types
          (i.e. other than MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME and
          MYSQL_TYPE_TIMESTAMP).
          Also constant conversion cache optimization has been added.
        
        
        - The Arg_comparator::set_cmp_func method has been modified to
          use the get_year_value function if get_datetime_value() is not
          applicable.
        
        - The Arg_comparator::compare_year method has been removed
          since get_year_value() is compatible with the old
          Arg_comparator::compare_datetime method that doesn't have
          problems #1-#3 (see whole patch entry commentary).
     @ sql/item_cmpfunc.h
        Unnecessary Arg_comparator::year_as_datetime and
        Arg_comparator::compare_year() declarations have been removed.

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/93080

3237 Gleb Shchepa	2009-12-07
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      A few problems were found in the fix for bug 43668:
      1) Comparison of the YEAR column with NULL always returned TRUE;
      2) Comparison of the YEAR column with constants returned
         unpredictable result;
      3) Unnecessary conversion warnings when comparing a non-integer
         constant with a NULL value in the YEAR column;
      
      The problems described above have been resolved.
      Also constant conversion cache optimization has been used when 
      comparing the YEAR column with a non-integer constant.
     @ mysql-test/r/type_year.result
        Test case for bug #49480.
     @ mysql-test/t/type_year.test
        Test case for bug #49480.
     @ sql/item_cmpfunc.cc
        - The get_year_value() function has been modified to make its
          return value compatible with the get_datetime_value() return
          value (i.e. to convert numeric values into the YYYY0000000000
          (YYYY-00-00 00:00:00) form.
          From now this function is able to accept any input item
          with a type that doesn't belong to Item::is_datetime() types
          (i.e. other than MYSQL_TYPE_DATE, MYSQL_TYPE_DATETIME and
          MYSQL_TYPE_TIMESTAMP).
          Also constant conversion cache optimization has been added.
        
        
        - The Arg_comparator::set_cmp_func method has been modified to
          use the get_year_value function if get_datetime_value() is not
          applicable.
        
        - The Arg_comparator::compare_year method has been removed
          since get_year_value() is compatible with the old
          Arg_comparator::compare_datetime method that doesn't have
          problems #1-#3 (see whole patch entry commentary).
     @ sql/item_cmpfunc.h
        Unnecessary Arg_comparator::year_as_datetime and
        Arg_comparator::compare_year() declarations have been removed.

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/93166

3237 Gleb Shchepa	2009-12-08
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      A few problems were found in the fix for bug 43668:
      1) Comparison of the YEAR column with NULL always returned TRUE;
      2) Comparison of the YEAR column with constants always returned
         unpredictable result;
      3) Unnecessary conversion warnings when comparing a non-integer
         constant with a NULL value in the YEAR column;
      
      The problems described above have been resolved.
      
      Also a comparison problem of two YEAR columns has been solved
      (not a regression).
     @ mysql-test/r/type_year.result
        Test case for bug #49480.
     @ mysql-test/t/type_year.test
        Test case for bug #49480.
     @ sql/item_cmpfunc.cc
        - The get_year_value() function has been modified to make its
          return value compatible with the get_datetime_value() return
          value (i.e. to convert numeric values into the YYYY0000000000
          (YYYY-00-00 00:00:00) form.
          As far as:
          a) we have no direct way to distinguish YEAR(2) from YEAR(4) and
          b) zero value returned by val_int() means 2000 for YEAR(2) and
             0000 for YEAR(4),
          a var_str() trick has been added to return acceptable year
          value (before the regression the YEAR(2) comparison with 0
          was incorrect and after the regression it was fixed, but
          the YEAR(4) comparison was broken).
        
        - The Arg_comparator::set_cmp_func method has been modified to
          use the get_year_value function if get_datetime_value() is not
          applicable.
          From now only 2 cases have a special processing there:
          * both comparing items have MYSQL_TYPE_YEAR field type or
          * one item have is MYSQL_TYPE_YEAR and other one is
            is_datetime()-compliant.
        
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - The Arg_comparator::compare_year method has been removed
          since get_year_value() is compatible with the old
          Arg_comparator::compare_datetime method that doesn't have
          problems #1-#3 (see whole patch entry commentary).
     @ sql/item_cmpfunc.h
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - Unnecessary Arg_comparator::year_as_datetime and
          Arg_comparator::compare_year() declarations have been
          removed.

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/93277

3237 Gleb Shchepa	2009-12-09
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      A few problems were found in the fix for bug 43668:
      1) Comparison of the YEAR column with NULL always returned TRUE;
      2) Comparison of the YEAR column with constants always returned
         unpredictable result;
      3) Unnecessary conversion warnings when comparing a non-integer
         constant with a NULL value in the YEAR column;
      
      The problems described above have been resolved with an
      exception: zero YEAR(2) column value comparison with 00 or
      2000 still fail, so MIN/MAX on YEAR(2) column containing zero
      value still fail.
      
      Also a comparison problem of two YEAR columns has been solved
      (not a regression).
     @ mysql-test/r/type_year.result
        Test case for bug #49480.
     @ mysql-test/t/type_year.test
        Test case for bug #49480.
     @ sql/field.h
        The Field_year::max_display_length method has been overloaded
        to return 2 on YEAR(2) fields (was 4).
     @ sql/item_cmpfunc.cc
        - The get_year_value() function has been modified to make its
          return value compatible with the get_datetime_value() return
          value (i.e. to convert numeric values into the YYYY0000000000
          (YYYY-00-00 00:00:00) form.
          As far as:
          a) we have no direct way to distinguish YEAR(2) from YEAR(4) and
          b) zero value returned by val_int() means 0000 (invalid value)
             for YEAR(4) and either 0000 or 2000 for YEAR(2),
          a max_length trick has been added to return acceptable year
          value (before the regression the YEAR(2) comparison with 0
          was incorrect and after the regression it was fixed, but
          the YEAR(4) comparison was broken).
          However, this fixes YEAR(4) regression only, so MIN/MAX and
          comparisons on YEAR(2) columns containing zero value still fail.
        
        
        - The Arg_comparator::set_cmp_func method has been modified to
          use the get_year_value function if get_datetime_value() is not
          applicable.
          From now only 2 cases have a special processing there:
          * both comparing items have MYSQL_TYPE_YEAR field type or
          * one item have is MYSQL_TYPE_YEAR and other one is
            is_datetime()-compliant.
        
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - The Arg_comparator::compare_year method has been removed
          since get_year_value() is compatible with the old
          Arg_comparator::compare_datetime method that doesn't have
          problems #1-#3 (see whole patch entry commentary).
     @ sql/item_cmpfunc.h
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - Unnecessary Arg_comparator::year_as_datetime and
          Arg_comparator::compare_year() declarations have been
          removed.

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/93396

3254 Gleb Shchepa	2009-12-10
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      A few problems were found in the fix for bug 43668:
      1) Comparison of the YEAR column with NULL always returned TRUE;
      2) Comparison of the YEAR column with constants always returned
         unpredictable result;
      3) Unnecessary conversion warnings when comparing a non-integer
         constant with a NULL value in the YEAR column;
      
      The problems described above have been resolved with an
      exception: zero (i.e. invalid) YEAR column value comparison
      with 00 or 2000 still fail (it is not a regression and it was
      not a regression), so MIN/MAX on YEAR column containing zero
      value still fail.
     @ mysql-test/r/type_year.result
        Test case for bug #49480.
     @ mysql-test/t/type_year.test
        Test case for bug #49480.
     @ sql/item_cmpfunc.cc
        - The get_year_value() function has been modified to make its
          return value compatible with the get_datetime_value() return
          value (i.e. to convert numeric values into the YYYY0000000000
          (YYYY-00-00 00:00:00) form.
        
        - The Arg_comparator::set_cmp_func method has been modified to
          use the get_year_value function if get_datetime_value() is not
          applicable.
          From now only 2 cases have a special processing there:
          * both comparing items have MYSQL_TYPE_YEAR field type
                  or
          * one item have is MYSQL_TYPE_YEAR and other one is
            is_datetime()-compliant.
        
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - The Arg_comparator::compare_year method has been removed
          since get_year_value() is compatible with the old
          Arg_comparator::compare_datetime method that doesn't have
          problems #1-#3 (see whole patch entry commentary).
     @ sql/item_cmpfunc.h
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - Unnecessary Arg_comparator::year_as_datetime and
          Arg_comparator::compare_year() declarations have been
          removed.

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/93428

3256 Gleb Shchepa	2009-12-10
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      A few problems were found in the fix for bug 43668:
      1) Comparison of the YEAR column with NULL always returned TRUE;
      2) Comparison of the YEAR column with constants always returned
         unpredictable result;
      3) Unnecessary conversion warnings when comparing a non-integer
         constant with a NULL value in the YEAR column;
      
      The problems described above have been resolved with an
      exception: zero (i.e. invalid) YEAR column value comparison
      with 00 or 2000 still fail (it is not a regression and it was
      not a regression), so MIN/MAX on YEAR column containing zero
      value still fail.
     @ mysql-test/r/type_year.result
        Test case for bug #49480.
     @ mysql-test/t/type_year.test
        Test case for bug #49480.
     @ sql/item_cmpfunc.cc
        - The get_year_value() function has been modified to make its
          return value compatible with the get_datetime_value() return
          value (i.e. to convert numeric values into the YYYY0000000000
          (YYYY-00-00 00:00:00) form.
        
        - The Arg_comparator::set_cmp_func method has been modified to
          use the get_year_value function if get_datetime_value() is not
          applicable.
          From now only 2 cases have a special processing there:
          * both comparing items have MYSQL_TYPE_YEAR field type
                  or
          * one item have is MYSQL_TYPE_YEAR and other one is
            is_datetime()-compliant.
        
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - The Arg_comparator::compare_year method has been removed
          since get_year_value() is compatible with the old
          Arg_comparator::compare_datetime method that doesn't have
          problems #1-#3 (see whole patch entry commentary).
     @ sql/item_cmpfunc.h
        - New helper function try_year_cmp_func() has been
          added for the better code readability to call from
          Arg_comparator::set_cmp_func().
        
        - Unnecessary Arg_comparator::year_as_datetime and
          Arg_comparator::compare_year() declarations have been
          removed.

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/94058

3241 Georgi Kodinov	2009-12-15
      Bug #49480: WHERE using YEAR columns returns unexpected results
      
      Merge the fix from 5.1-bugteam to 5.1-main

Pushed into 5.1.43 (revid:ramil@mysql.com-20091215170821-eexh9rqrnw1ruzh2) (version source
revid:joro@sun.com-20091215085453-zrmxqdap0ezzxfqn) (merge vers: 5.1.43) (pib:14)

Actually pushed into 5.1.42 now.

Noted in 5.1.42 changelog.

Comparisons involving YEAR values could produce incorrect results.

Setting report to NDI pending push to 5.5.x+.

The commit comment says:

"The problems described above have been resolved with an exception: zero (i.e. invalid) YEAR column value comparison with 00 or 2000 still fail (it is not a regression and it was not a regression), so MIN/MAX on YEAR column containing zero value still fail."

Maybe the limitation should be documented somehow, because there is still a slight change in behavior between 5.1.41 and 5.1.42:

DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR NOT NULL);
CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR NOT NULL);
INSERT INTO t1 (c1) VALUES (2001);
INSERT INTO t2 (c1,c2) VALUES (2001,2000);
SELECT * FROM t1, t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;

With 5.1.42, the last SELECT returns a row:

#+------+------+------+------+
#| c1   | c2   | c1   | c2   |
#+------+------+------+------+
#| 2001 | 0000 | 2001 | 2000 |
#+------+------+------+------+
#1 row in set (0.00 sec)

(that is, in c2 0 = 2000)

With 5.1.41, the same select returns an empty result set:

#Empty set (0.00 sec)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)

Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alik@sun.com-20091216183525-vbefoeydwonfxkye) (merge vers: 5.5.0-beta) (pib:15)

Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216183710-1stho81j3b8f0kds) (pib:15)

See also bug #49910

Noted in 5.5.1, 6.0.14 changelogs.

Setting report to NDI pending push to Celosia.

Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:ramil@mysql.com-20091210070323-5sjl4dlghlggkz4d) (merge vers: 5.1.42) (pib:16)

Setting report to NDI pending push to Celosia.

Setting report to Need Merge pending push to Celosia.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

Fixed in earlier 5.1.x, 5.5.x.