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.

Page generated in 0.067 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.