Bug #49910 Behavioural change in SELECT/WHERE on YEAR(4) data type

• Submitted: 24 Dec 2009 6:13
• Modified: 9 Feb 2011 5:01
• Reporter: Nirbhay Choubey
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.1.42, next-mr
• OS: Any
• Assigned to: Gleb Shchepa
• CPU Architecture: Any
• Tags: regression, regression

[24 Dec 2009 6:13] Nirbhay Choubey

Description:
The following behavioural change/regression noticed from 5.1.41 to 5.1.42, (needs to be documented if not a bug).

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;

The above select gives the following output on 5.1.42:
+------+------+------+------+
| c1   | c2   | c1   | c2   |
+------+------+------+------+
| 2001 | 0000 | 2001 | 2000 |
+------+------+------+------+
1 row in set (0.00 sec)

Whereas on 5.1.41, the above SELECT returns an 'empty set'.

This issue is related to BUG#49480.

How to repeat:
See the description above.

[24 Dec 2009 6:30] Sveta Smirnova

Thank you for the report.

Verified as described.

[13 Mar 2010 23:16] MySQL Verification Team

This patch effectively fixes the bug without regressions. All the tests pass.

=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc 2010-02-26 13:16:46 +0000
+++ sql/item_cmpfunc.cc 2010-03-13 15:54:50 +0000
@@ -1191,11 +1191,13 @@
     Coerce value to the 19XX form in order to correctly compare
     YEAR(2) & YEAR(4) types.
   */
-  if (value < 70)
-    value+= 100;
-  if (value <= 1900)
-    value+= 1900;
-
+  if ((item->type() != Item::FIELD_ITEM) || ( ((Item_field *)item)->field->field_length != 4))
+  {
+    if (value < 70)
+      value+= 100;
+    if (value <= 1900)
+      value+= 1900;
+  }
   /* Convert year to DATETIME of form YYYY-00-00 00:00:00 (YYYY0000000000). */
   value*= 10000000000LL;

Test case from this bug works correctly, plus few others.

[13 Mar 2010 23:16] MySQL Verification Team

This patch effectively fixes the bug without regressions. All the tests pass.

=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc 2010-02-26 13:16:46 +0000
+++ sql/item_cmpfunc.cc 2010-03-13 15:54:50 +0000
@@ -1191,11 +1191,13 @@
     Coerce value to the 19XX form in order to correctly compare
     YEAR(2) & YEAR(4) types.
   */
-  if (value < 70)
-    value+= 100;
-  if (value <= 1900)
-    value+= 1900;
-
+  if ((item->type() != Item::FIELD_ITEM) || ( ((Item_field *)item)->field->field_length != 4))
+  {
+    if (value < 70)
+      value+= 100;
+    if (value <= 1900)
+      value+= 1900;
+  }
   /* Convert year to DATETIME of form YYYY-00-00 00:00:00 (YYYY0000000000). */
   value*= 10000000000LL;

Test case from this bug works correctly, plus few others.

[15 Mar 2010 17:02] Gleb Shchepa

Sinisa,

as far patched code

  SELECT t1.c1 AS t1c1, t1.c2 AS t1c2, t2.c1 AS t2c1, t2.c2 AS t2c2 FROM t1, t2 HAVING t1c1=t2c1 AND t1c2=t2c2

fails like unpatched

SELECT * FROM t1, t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2 ,

I suggest a small modification to you fix:

=== modified file 'sql/item_cmpfunc.cc'
--- old/sql/item_cmpfunc.cc	2010-03-14 16:01:45 +0000
+++ new/sql/item_cmpfunc.cc	2010-03-15 16:57:50 +0000
@@ -1191,11 +1191,13 @@ get_year_value(THD *thd, Item ***item_ar
     Coerce value to the 19XX form in order to correctly compare
     YEAR(2) & YEAR(4) types.
   */
-  if (value < 70)
-    value+= 100;
-  if (value <= 1900)
-    value+= 1900;
-
+  if ((item->real_item()->type() != Item::FIELD_ITEM) || ( ((Item_field *)item->real_item())->field->field_length != 4))
+  {
+    if (value < 70)
+      value+= 100;
+    if (value <= 1900)
+      value+= 1900;
+  }
   /* Convert year to DATETIME of form YYYY-00-00 00:00:00 (YYYY0000000000). */
   value*= 10000000000LL;

[15 Mar 2010 17:05] MySQL Verification Team

Gleb,

Yes, I agree 100% with your variant. I should have thought about real_item ... ;o)

Please, reduce E/R values dramatically, then commit it and push it in 5.1.

I hope you agree ...

[19 Mar 2010 20:41] Bugs System

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

3412 Gleb Shchepa	2010-03-20
      Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type                                  
      (Original patch by Sinisa Milivojevic)
      
      The YEAR(4) value of 2000 was equal to the "bad" YEAR(4) value of 0000.                              
      
      The get_year_value() function has been modified to not adjust bad                                    
      YEAR(4) value to 2000.   
     @ mysql-test/r/type_year.result
        Test case for bug #49910.
     @ mysql-test/t/type_year.test
        Test case for bug #49910.
     @ sql/item_cmpfunc.cc
        Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type                                  
        
        The get_year_value() function has been modified to not adjust bad                                    
        YEAR(4) value to 2000.

[22 Mar 2010 9:33] Bugs System

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

3414 Gleb Shchepa	2010-03-22
      Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type                                  
      (Original patch by Sinisa Milivojevic)
      
      The YEAR(4) value of 2000 was equal to the "bad" YEAR(4) value of 0000.                              
      
      The get_year_value() function has been modified to not adjust bad                                    
      YEAR(4) value to 2000.
     @ mysql-test/r/type_year.result
        Test case for bug #49910.
     @ mysql-test/t/type_year.test
        Test case for bug #49910.
     @ sql/item_cmpfunc.cc
        Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type                                  
        
        The get_year_value() function has been modified to not adjust bad                                    
        YEAR(4) value to 2000.

[26 Mar 2010 8:21] Bugs System

Pushed into 5.5.4-m3 (revid:[e-mail]-20100326080914-2pz8ns984e0spu03) (version source revid:[e-mail]-20100322132851-8j3m42x4ldi1kca5) (merge vers: 5.5.3-m2) (pib:16)

[26 Mar 2010 8:25] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100326081116-m3v4l34yhr43mtsv) (version source revid:[e-mail]-20100325072612-4sds00ix8ajo1e84) (pib:16)

[26 Mar 2010 8:29] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100326081944-qja07qklw1p2w7jb) (version source revid:[e-mail]-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

[6 Apr 2010 7:58] Bugs System

Pushed into 5.1.46 (revid:[e-mail]-20100405111026-7kz1p8qlzglqgfmu) (version source revid:[e-mail]-20100322090656-w4ixy7p67fb3vr29) (merge vers: 5.1.46) (pib:16)

[12 Apr 2010 22:24] Paul DuBois

Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

The YEAR values 2000 and 0000 could be treated as equal.

[17 Jun 2010 11:44] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:22] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

[17 Jun 2010 13:10] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[8 Feb 2011 11:57] Bugs System

Pushed into mysql-5.1 5.1.56 (revid:[e-mail]-20110208115233-t04e8k0pwhwtbc1u) (version source revid:[e-mail]-20110208115233-t04e8k0pwhwtbc1u) (merge vers: 5.1.56) (pib:24)

[8 Feb 2011 14:09] Bugs System

Pushed into mysql-5.5 5.5.10 (revid:[e-mail]-20110208140736-1173xnoipufbhowh) (version source revid:[e-mail]-20110208135903-jhzy6wq16b2fx7pg) (merge vers: 5.5.10) (pib:24)

[8 Feb 2011 16:45] Bugs System

Pushed into mysql-trunk 5.6.2 (revid:[e-mail]-20110208155412-tfy4l5hqxi0g7o41) (version source revid:[e-mail]-20110208154951-gzqgr74u4bndswi8) (merge vers: 5.6.2) (pib:24)