#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.
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.
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 16:01:09 +0000
@@ -46,3 +46,78 @@ 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 = 99;
+yyyy
+1999
+SELECT yyyy FROM t1 WHERE yyyy = 'test';
+yyyy
+0000
+Warnings:
+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'
+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 16:01:09 +0000
@@ -30,3 +30,46 @@ 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 = 99;
+
+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 16:01:09 +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
@@ -1169,7 +1149,7 @@ static longlong
get_year_value(THD *thd, Item ***item_arg, Item **cache_arg,
Item *warn_item, bool *is_null)
{
- longlong value= 0;
+ longlong value= 0, orig_value= 0;
Item *item= **item_arg;
value= item->val_int();
@@ -1178,6 +1158,21 @@ get_year_value(THD *thd, Item ***item_ar
return ~(ulonglong) 0;
/*
+ Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+ for the current thread but it still may change during the execution.
+ */
+ if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM ||
+ ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC))
+ {
+ Item_cache_int *cache= new Item_cache_int(MYSQL_TYPE_YEAR);
+ /* Mark the cache as non-const to prevent re-caching. */
+ cache->set_used_tables(1);
+ cache->store(item, value);
+ *cache_arg= cache;
+ *item_arg= cache_arg;
+ }
+
+ /*
Coerce value to the 19XX form in order to correctly compare
YEAR(2) & YEAR(4) types.
*/
@@ -1186,6 +1181,8 @@ 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 +1612,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 16:01:09 +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-20091207160109-igwkgic25cbtgi02.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (gshchepa:3237) Bug#49480 | Gleb Shchepa | 7 Dec |
