bzr commit into mysql-5.1-bugteam branch (gshchepa:3414) Bug#49910

[Gleb Shchepa]

#At file:///mnt/sda7/work/mysql-5.1-bugteam/ based on revid:aelkin@stripped

 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.

    modified:
      mysql-test/r/type_year.result
      mysql-test/t/type_year.test
      sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/type_year.result'
--- a/mysql-test/r/type_year.result	2010-01-13 10:28:42 +0000
+++ b/mysql-test/r/type_year.result	2010-03-22 08:33:25 +0000
@@ -309,4 +309,36 @@ yyyy	c4
 2069	2069
 DROP TABLE t2, t4;
 #
+# Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
+#
+CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4));
+INSERT INTO t1 (s) VALUES ('bad');
+Warnings:
+Warning	1364	Field 'y' doesn't have a default value
+INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001);
+SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y;
+y	s	y	s
+0000	bad	0000	bad
+0000	0	0000	bad
+0000	bad	0000	0
+0000	0	0000	0
+2000	2000	2000	2000
+2001	2001	2001	2001
+SELECT * FROM t1 WHERE t1.y = 0;
+y	s
+0000	bad
+0000	0
+SELECT * FROM t1 WHERE t1.y = 2000;
+y	s
+2000	2000
+SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y;
+ta_y	s	tb_y	s
+0000	bad	0000	bad
+0000	0	0000	bad
+0000	bad	0000	0
+0000	0	0000	0
+2000	2000	2000	2000
+2001	2001	2001	2001
+DROP TABLE t1;
+#
 End of 5.1 tests

=== modified file 'mysql-test/t/type_year.test'
--- a/mysql-test/t/type_year.test	2010-01-13 10:28:42 +0000
+++ b/mysql-test/t/type_year.test	2010-03-22 08:33:25 +0000
@@ -134,5 +134,21 @@ SELECT * FROM t4 WHERE yyyy > 123;
 DROP TABLE t2, t4;
 
 --echo #
+--echo # Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type
+--echo #
+
+CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4));
+INSERT INTO t1 (s) VALUES ('bad');
+INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001);
+
+SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y;
+SELECT * FROM t1 WHERE t1.y = 0;
+SELECT * FROM t1 WHERE t1.y = 2000;
+
+SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y;
+
+DROP TABLE t1;
+
+--echo #
 
 --echo End of 5.1 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-03-14 16:01:45 +0000
+++ b/sql/item_cmpfunc.cc	2010-03-22 08:33:25 +0000
@@ -1190,12 +1190,21 @@ get_year_value(THD *thd, Item ***item_ar
   /*
     Coerce value to the 19XX form in order to correctly compare
     YEAR(2) & YEAR(4) types.
+    Here we are converting all item values but YEAR(4) fields since
+      1) YEAR(4) already has a regular YYYY form and
+      2) we don't want to convert zero/bad YEAR(4) values to the
+         value of 2000.
   */
-  if (value < 70)
-    value+= 100;
-  if (value <= 1900)
-    value+= 1900;
-
+  Item *real_item= item->real_item();
+  if (!(real_item->type() == Item::FIELD_ITEM &&
+        ((Item_field *)real_item)->field->type() == MYSQL_TYPE_YEAR &&
+        ((Item_field *)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;
 


Attachment: [text/bzr-bundle] bzr/[e-mail]