List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:December 7 2009 10:03am
Subject:bzr commit into mysql-5.1-bugteam branch (gshchepa:3237) Bug#49480
View as plain text  
#At file:///mnt/sda7/work/49480/ based on revid:satya.bn@stripped

 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.

    modified:
      mysql-test/r/type_year.result
      mysql-test/t/type_year.test
      sql/item_cmpfunc.cc
      sql/item_cmpfunc.h
=== modified file 'mysql-test/r/type_year.result'
--- a/mysql-test/r/type_year.result	2007-03-29 04:08:30 +0000
+++ b/mysql-test/r/type_year.result	2009-12-07 10:03:42 +0000
@@ -46,3 +46,79 @@ a
 2001
 drop table t1;
 End of 5.0 tests
+#
+# Bug #49480: WHERE using YEAR columns returns unexpected results
+#
+CREATE TABLE t1(yyyy YEAR(4), yy YEAR(2));
+INSERT INTO t1 VALUES (1901,NULL),(1999,1999),(NULL,2000),('test', 'test');
+Warnings:
+Warning	1366	Incorrect integer value: 'test' for column 'yyyy' at row 4
+Warning	1366	Incorrect integer value: 'test' for column 'yy' at row 4
+SELECT * FROM t1;
+yyyy	yy
+1901	NULL
+1999	99
+NULL	00
+0000	00
+SELECT * FROM t1 WHERE yyyy = NULL;
+yyyy	yy
+SELECT * FROM t1 WHERE yy = NULL;
+yyyy	yy
+SELECT yy FROM t1 WHERE yy < NULL;
+yy
+SELECT yy FROM t1 WHERE yy > NULL;
+yy
+SELECT * FROM t1 WHERE yyyy = NOW();
+yyyy	yy
+SELECT yy FROM t1 WHERE yy = 99;
+yy
+99
+SELECT yy FROM t1 WHERE 99 = yy;
+yy
+99
+SELECT yyyy FROM t1 WHERE yyyy = 'test';
+yyyy
+0000
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'test'
+Warning	1292	Truncated incorrect INTEGER value: 'test'
+Warning	1292	Truncated incorrect INTEGER value: 'test'
+SELECT yy FROM t1 WHERE yy = 'test';
+yy
+00
+00
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'test'
+Warning	1292	Truncated incorrect INTEGER value: 'test'
+Warning	1292	Truncated incorrect INTEGER value: 'test'
+SELECT yyyy FROM t1 WHERE yyyy = '1999';
+yyyy
+1999
+SELECT yy FROM t1 WHERE yy = '1999';
+yy
+99
+SELECT yyyy FROM t1 WHERE yyyy = 1999;
+yyyy
+1999
+SELECT yy FROM t1 WHERE yy = 1999;
+yy
+99
+SELECT yyyy FROM t1 WHERE yyyy = 1999.1;
+yyyy
+1999
+SELECT yy FROM t1 WHERE yy = 1999.1;
+yy
+99
+SELECT yyyy FROM t1 WHERE yyyy = 1998.9;
+yyyy
+1999
+SELECT yy FROM t1 WHERE yy = 1998.9;
+yy
+99
+SELECT * FROM t1 WHERE yyyy = yy;
+yyyy	yy
+1999	99
+0000	00
+DROP TABLE t1;
+#
+End of 5.1 tests

=== modified file 'mysql-test/t/type_year.test'
--- a/mysql-test/t/type_year.test	2007-03-29 04:08:30 +0000
+++ b/mysql-test/t/type_year.test	2009-12-07 10:03:42 +0000
@@ -30,3 +30,44 @@ select * from t1;
 drop table t1;
 
 --echo End of 5.0 tests
+
+--echo #
+--echo # Bug #49480: WHERE using YEAR columns returns unexpected results
+--echo #
+
+CREATE TABLE t1(yyyy YEAR(4), yy YEAR(2));
+INSERT INTO t1 VALUES (1901,NULL),(1999,1999),(NULL,2000),('test', 'test');
+SELECT * FROM t1;
+
+SELECT * FROM t1 WHERE yyyy = NULL;
+SELECT * FROM t1 WHERE yy = NULL;
+SELECT yy FROM t1 WHERE yy < NULL;
+SELECT yy FROM t1 WHERE yy > NULL;
+
+SELECT * FROM t1 WHERE yyyy = NOW();
+
+SELECT yy FROM t1 WHERE yy = 99;
+SELECT yy FROM t1 WHERE 99 = yy;
+
+SELECT yyyy FROM t1 WHERE yyyy = 'test';
+SELECT yy FROM t1 WHERE yy = 'test';
+
+SELECT yyyy FROM t1 WHERE yyyy = '1999';
+SELECT yy FROM t1 WHERE yy = '1999';
+
+SELECT yyyy FROM t1 WHERE yyyy = 1999;
+SELECT yy FROM t1 WHERE yy = 1999;
+
+SELECT yyyy FROM t1 WHERE yyyy = 1999.1;
+SELECT yy FROM t1 WHERE yy = 1999.1;
+
+SELECT yyyy FROM t1 WHERE yyyy = 1998.9;
+SELECT yy FROM t1 WHERE yy = 1998.9;
+
+SELECT * FROM t1 WHERE yyyy = yy;
+
+DROP TABLE t1;
+
+--echo #
+
+--echo End of 5.1 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-11-25 09:52:03 +0000
+++ b/sql/item_cmpfunc.cc	2009-12-07 10:03:42 +0000
@@ -959,35 +959,13 @@ int Arg_comparator::set_cmp_func(Item_re
   } else if (type != ROW_RESULT && ((*a)->field_type() == MYSQL_TYPE_YEAR ||
              (*b)->field_type() == MYSQL_TYPE_YEAR))
   {
+    /* TODO: implement caching of converted constants like above */
     is_nulls_eq= is_owner_equal_func();
-    year_as_datetime= FALSE;
-
-    if ((*a)->is_datetime())
-    {
-      year_as_datetime= TRUE;
-      get_value_a_func= &get_datetime_value;
-    } else if ((*a)->field_type() == MYSQL_TYPE_YEAR)
-      get_value_a_func= &get_year_value;
-    else
-    {
-      /*
-        Because convert_constant_item is called only for EXECUTE in PS mode
-        the value of get_value_x_func set in PREPARE might be not
-        valid for EXECUTE.
-      */
-      get_value_a_func= NULL;
-    }
-
-    if ((*b)->is_datetime())
-    {
-      year_as_datetime= TRUE;
-      get_value_b_func= &get_datetime_value;
-    } else if ((*b)->field_type() == MYSQL_TYPE_YEAR)
-      get_value_b_func= &get_year_value;
-    else
-      get_value_b_func= NULL;
-
-    func= &Arg_comparator::compare_year;
+    get_value_a_func= (*a)->is_datetime() ? &get_datetime_value
+                                          : &get_year_value;
+    get_value_b_func= (*b)->is_datetime() ? &get_datetime_value
+                                          : &get_year_value;
+    func= &Arg_comparator::compare_datetime;
     return 0;
   }
 
@@ -1147,7 +1125,7 @@ get_datetime_value(THD *thd, Item ***ite
 
 
 /*
-  Retrieves YEAR value of 19XX form from given item.
+  Retrieves YEAR value of 19XX-00-00 00:00:00 form from given item.
 
   SYNOPSIS
     get_year_value()
@@ -1159,7 +1137,9 @@ get_datetime_value(THD *thd, Item ***ite
 
   DESCRIPTION
     Retrieves the YEAR value of 19XX form from given item for comparison by the
-    compare_year() function.
+    compare_datetime() function.
+    Converts year to DATETIME of form YYYY-00-00 00:00:00 for the compatibility
+    with the get_datetime_value function result.
 
   RETURN
     obtained value
@@ -1186,6 +1166,9 @@ get_year_value(THD *thd, Item ***item_ar
   if (value <= 1900)
     value+= 1900;
 
+  /* Convert year to DATETIME of form YYYY-00-00 00:00:00. */
+  value*= 10000000000LL;
+
   return value;
 }
 
@@ -1615,67 +1598,6 @@ int Arg_comparator::compare_e_row()
 }
 
 
-/**
-  Compare values as YEAR.
-
-  @details
-    Compare items as YEAR for EQUAL_FUNC and for other comparison functions.
-    The YEAR values of form 19XX are obtained with help of the get_year_value()
-    function.
-    If one of arguments is of DATE/DATETIME type its value is obtained
-    with help of the get_datetime_value function. In this case YEAR values
-    prior to comparison are converted to the ulonglong YYYY-00-00 00:00:00
-    DATETIME form.
-    If an argument type neither YEAR nor DATE/DATEIME then val_int function
-    is used to obtain value for comparison.
-
-  RETURN
-    If is_nulls_eq is TRUE:
-       1    if items are equal or both are null
-       0    otherwise
-    If is_nulls_eq is FALSE:
-      -1   a < b
-       0   a == b or at least one of items is null
-       1   a > b
-    See the table:
-    is_nulls_eq | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
-    a_is_null   | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 |
-    b_is_null   | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 |
-    result      | 1 | 0 | 0 |0/1| 0 | 0 | 0 |-1/0/1|
-*/
-
-int Arg_comparator::compare_year()
-{
-  bool a_is_null, b_is_null;
-  ulonglong val1= get_value_a_func ?
-                    (*get_value_a_func)(thd, &a, &a_cache, *b, &a_is_null) :
-                    (*a)->val_int();
-  ulonglong val2= get_value_b_func ?
-                    (*get_value_b_func)(thd, &b, &b_cache, *a, &b_is_null) :
-                    (*b)->val_int();
-  if (!(*a)->null_value)
-  {
-    if (!(*b)->null_value)
-    {
-      if (set_null)
-        owner->null_value= 0;
-      /* Convert year to DATETIME of form YYYY-00-00 00:00:00 when necessary. */
-      if((*a)->field_type() == MYSQL_TYPE_YEAR && year_as_datetime)
-        val1*= 10000000000LL;
-      if((*b)->field_type() == MYSQL_TYPE_YEAR && year_as_datetime)
-        val2*= 10000000000LL;
-
-      if (val1 < val2)	return is_nulls_eq ? 0 : -1;
-      if (val1 == val2)   return is_nulls_eq ? 1 : 0;
-      return is_nulls_eq ? 0 : 1;
-    }
-  }
-  if (set_null)
-    owner->null_value= is_nulls_eq ? 0 : 1;
-  return (is_nulls_eq && (*a)->null_value == (*b)->null_value) ? 1 : 0;
-}
-
-
 void Item_func_truth::fix_length_and_dec()
 {
   maybe_null= 0;

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2009-11-24 15:26:13 +0000
+++ b/sql/item_cmpfunc.h	2009-12-07 10:03:42 +0000
@@ -42,9 +42,6 @@ class Arg_comparator: public Sql_alloc
   bool is_nulls_eq;                // TRUE <=> compare for the EQUAL_FUNC
   bool set_null;                   // TRUE <=> set owner->null_value
                                    //   when one of arguments is NULL.
-  bool year_as_datetime;           // TRUE <=> convert YEAR value to
-                                   // the YYYY-00-00 00:00:00 DATETIME
-                                   // format. See compare_year.
   enum enum_date_cmp_type { CMP_DATE_DFLT= 0, CMP_DATE_WITH_DATE,
                             CMP_DATE_WITH_STR, CMP_STR_WITH_DATE };
   longlong (*get_value_a_func)(THD *thd, Item ***item_arg, Item **cache_arg,
@@ -57,9 +54,9 @@ public:
   String value1, value2;
 
   Arg_comparator(): thd(0), a_cache(0), b_cache(0), set_null(0),
-    year_as_datetime(0), get_value_a_func(0), get_value_b_func(0) {};
+    get_value_a_func(0), get_value_b_func(0) {};
   Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0),
-    a_cache(0), b_cache(0), set_null(0), year_as_datetime(0),
+    a_cache(0), b_cache(0), set_null(0),
     get_value_a_func(0), get_value_b_func(0) {};
 
   int set_compare_func(Item_result_field *owner, Item_result type);
@@ -101,7 +98,6 @@ public:
   int compare_real_fixed();
   int compare_e_real_fixed();
   int compare_datetime();        // compare args[0] & args[1] as DATETIMEs
-  int compare_year();
 
   static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b,
                                                       ulonglong *const_val_arg);


Attachment: [text/bzr-bundle] bzr/gshchepa@mysql.com-20091207100342-31dq11s0sq7q6gu2.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (gshchepa:3237) Bug#49480Gleb Shchepa7 Dec