From: Date: April 26 2007 10:40pm Subject: bk commit into 5.0 tree (evgen:1.2440) BUG#16377 List-Archive: http://lists.mysql.com/commits/25561 X-Bug: 16377 Message-Id: <20070426204038.7435D22CB9E@moonbone.moonbone.local> Below is the list of changes that have just been committed into a local 5.0 repository of evgen. When evgen does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-04-27 00:40:35+04:00, evgen@stripped +6 -0 Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. The BETWEEN function was comparing DATE/DATETIME values either as ints or as strings. Both methods have their disadvantages and may lead to a wrong result. Now BETWEEN function checks whether all of its arguments has the STRING result types and at least one of them is a DATE/DATETIME item. If so it sets up two Arg_comparator obects to compare with the compare_datetime() comparator and uses them to compare such items. Added two Arg_comparator object members and one flag to the Item_func_between class for the correct DATE/DATETIME comparison. The Item_func_between::fix_length_and_dec() function now detects whether it's used for DATE/DATETIME comparison and sets up newly added Arg_comparator objects to do this. The Item_func_between::val_int() now uses Arg_comparator objects to perform correct DATE/DATETIME comparison. The owner variable of the Arg_comparator class now can be set to NULL if the caller wants to handle NULL values by itself. Now the Item_date_add_interval::get_date() function ajusts cached_field type according to the detected type. mysql-test/r/query_cache.result@stripped, 2007-04-27 00:18:34+04:00, evgen@stripped +3 -9 A test case result corrected after the fix for bug#16377. mysql-test/r/type_datetime.result@stripped, 2007-04-27 00:18:30+04:00, evgen@stripped +35 -0 Added a test case for the bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. mysql-test/t/type_datetime.test@stripped, 2007-04-27 00:18:05+04:00, evgen@stripped +19 -0 Added a test case for the bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. sql/item_cmpfunc.cc@stripped, 2007-04-27 00:34:54+04:00, evgen@stripped +67 -23 Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. The Item_func_between::fix_length_and_dec() function now detects whether it's used for DATE/DATETIME comparison and sets up newly added Arg_comparator objects to do this. The Item_func_between::val_int() now uses Arg_comparators to perform correct DATE/DATETIME comparison. The owner variable of the Arg_comparator class now can be set to NULL if the caller wants to handle NULL values by itself. sql/item_cmpfunc.h@stripped, 2007-04-27 00:36:55+04:00, evgen@stripped +6 -1 Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. Added two Arg_comparator object members and one flag to the Item_func_between class for the correct DATE/DATETIME comparison. sql/item_timefunc.cc@stripped, 2007-04-27 00:21:37+04:00, evgen@stripped +21 -0 Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. Now the Item_date_add_interval::get_date() function ajusts cached_field type according to the detected type. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: evgen # Host: moonbone.local # Root: /mnt/gentoo64/work/16377-bug-5.0-opt-mysql --- 1.247/sql/item_cmpfunc.cc 2007-04-27 00:13:10.000000000 +0400 +++ 1.248/sql/item_cmpfunc.cc 2007-04-27 00:34:54.000000000 +0400 @@ -692,7 +692,7 @@ b= (Item **)&b_cache; } } - is_nulls_eq= owner->functype() == Item_func::EQUAL_FUNC; + is_nulls_eq= test(owner && owner->functype() == Item_func::EQUAL_FUNC); func= &Arg_comparator::compare_datetime; return 0; } @@ -700,6 +700,21 @@ } +void Arg_comparator::set_datetime_cmp_func(Item **a1, Item **b1) +{ + thd= current_thd; + /* A caller will handle null values by itself. */ + owner= NULL; + a= a1; + b= b1; + a_type= (*a)->field_type(); + b_type= (*b)->field_type(); + a_cache= 0; + b_cache= 0; + is_nulls_eq= FALSE; + func= &Arg_comparator::compare_datetime; +} + /* Retrieves correct DATETIME value from given item. @@ -807,7 +822,8 @@ a_value= get_datetime_value(thd, &a, &a_cache, *b, &is_null); if (!is_nulls_eq && is_null) { - owner->null_value= 1; + if (owner) + owner->null_value= 1; return -1; } @@ -815,11 +831,13 @@ b_value= get_datetime_value(thd, &b, &b_cache, *a, &is_null); if (is_null) { - owner->null_value= is_nulls_eq ? 0 : 1; + if (owner) + owner->null_value= is_nulls_eq ? 0 : 1; return is_nulls_eq ? 1 : -1; } - owner->null_value= 0; + if (owner) + owner->null_value= 0; /* Compare values. */ if (is_nulls_eq) @@ -1674,8 +1692,11 @@ void Item_func_between::fix_length_and_dec() { - max_length= 1; - THD *thd= current_thd; + max_length= 1; + THD *thd= current_thd; + int i; + bool datetime_found= FALSE; + compare_as_dates= TRUE; /* As some compare functions are generated after sql_yacc, @@ -1690,26 +1711,29 @@ return; /* - Make a special case of compare with date/time and longlong fields. - They are compared as integers, so for const item this time-consuming - conversion can be done only once, not for every single comparison + Detect the comparison of DATE/DATETIME items. + At least one of items should be a DATE/DATETIME item and other items + should return the STRING result. */ - if (args[0]->real_item()->type() == FIELD_ITEM && - thd->lex->sql_command != SQLCOM_CREATE_VIEW && - thd->lex->sql_command != SQLCOM_SHOW_CREATE) + for (i= 0; i < 3; i++) { - Field *field=((Item_field*) (args[0]->real_item()))->field; - if (field->can_be_compared_as_longlong()) + if (args[i]->is_datetime()) { - /* - The following can't be recoded with || as convert_constant_item - changes the argument - */ - if (convert_constant_item(thd, field,&args[1])) - cmp_type=INT_RESULT; // Works for all types. - if (convert_constant_item(thd, field,&args[2])) - cmp_type=INT_RESULT; // Works for all types. + datetime_found= TRUE; + continue; } + if (args[i]->result_type() == STRING_RESULT) + continue; + compare_as_dates= FALSE; + break; + } + if (!datetime_found) + compare_as_dates= FALSE; + + if (compare_as_dates) + { + ge_cmp.set_datetime_cmp_func(args, args + 1); + le_cmp.set_datetime_cmp_func(args, args + 2); } } @@ -1717,7 +1741,27 @@ longlong Item_func_between::val_int() { // ANSI BETWEEN DBUG_ASSERT(fixed == 1); - if (cmp_type == STRING_RESULT) + if (compare_as_dates) + { + int ge_res, le_res; + + ge_res= ge_cmp.compare(); + if ((null_value= args[0]->null_value)) + return 0; + le_res= le_cmp.compare(); + + if (!args[1]->null_value && !args[2]->null_value) + return (longlong) ((ge_res >= 0 && le_res <=0) != negated); + else if (args[1]->null_value) + { + null_value= le_res > 0; // not null if false range. + } + else + { + null_value= ge_res < 0; + } + } + else if (cmp_type == STRING_RESULT) { String *value,*a,*b; value=args[0]->val_str(&value0); --- 1.149/sql/item_cmpfunc.h 2007-04-27 00:13:10.000000000 +0400 +++ 1.150/sql/item_cmpfunc.h 2007-04-27 00:36:55.000000000 +0400 @@ -91,6 +91,7 @@ static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b, ulonglong *const_val_arg); + void set_datetime_cmp_func(Item **a1, Item **b1); static arg_cmp_func comparator_matrix [5][2]; friend class Item_func; @@ -579,8 +580,12 @@ public: Item_result cmp_type; String value0,value1,value2; + /* TRUE <=> arguments will be compared as dates. */ + bool compare_as_dates; + /* Comparators used for DATE/DATETIME comparison. */ + Arg_comparator ge_cmp, le_cmp; Item_func_between(Item *a, Item *b, Item *c) - :Item_func_opt_neg(a, b, c) {} + :Item_func_opt_neg(a, b, c), compare_as_dates(FALSE) {} longlong val_int(); optimize_type select_optimize() const { return OPTIMIZE_KEY; } enum Functype functype() const { return BETWEEN; } --- 1.141/sql/item_timefunc.cc 2007-02-22 17:59:55.000000000 +0300 +++ 1.142/sql/item_timefunc.cc 2007-04-27 00:21:37.000000000 +0400 @@ -2173,6 +2173,27 @@ default: goto null_date; } + + /* Adjust cached_field_type according to the detected type. */ + if (cached_field_type == MYSQL_TYPE_STRING) + { + switch (ltime->time_type) + { + case MYSQL_TIMESTAMP_DATE: + cached_field_type= MYSQL_TYPE_DATE; + break; + case MYSQL_TIMESTAMP_DATETIME: + cached_field_type= MYSQL_TYPE_DATETIME; + break; + case MYSQL_TIMESTAMP_TIME: + cached_field_type= MYSQL_TYPE_TIME; + break; + default: + /* Shouldn't get here. */ + DBUG_ASSERT(0); + break; + } + } return 0; // Ok invalid_date: --- 1.36/mysql-test/r/type_datetime.result 2007-04-27 00:10:40.000000000 +0400 +++ 1.37/mysql-test/r/type_datetime.result 2007-04-27 00:18:30.000000000 +0400 @@ -231,3 +231,38 @@ 2001-04-15 2001-04-15 00:00:00 987282000 987282000 0 1 0 2001-05-20 2001-05-20 01:01:01 990309661 990306000 0 0 1 drop table t1; +create table t1 (f1 date, f2 datetime, f3 timestamp); +insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); +insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); +insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); +insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00'); +insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01'); +select f2 from t1 where f2 between '2001-2-5' and '01-04-14'; +f2 +2001-02-05 00:00:00 +2001-03-09 01:01:01 +select f1, f2, f3 from t1 where f1 between f2 and f3; +f1 f2 f3 +2001-02-05 2001-02-05 00:00:00 2001-02-05 01:01:01 +2001-03-10 2001-03-09 01:01:01 2001-03-10 01:01:01 +2001-04-15 2001-04-15 00:00:00 2001-04-15 00:00:00 +select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and +cast(f3 as date); +f1 f2 f3 +2001-02-05 2001-02-05 00:00:00 2001-02-05 01:01:01 +2001-03-10 2001-03-09 01:01:01 2001-03-10 01:01:01 +2001-04-15 2001-04-15 00:00:00 2001-04-15 00:00:00 +select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01'; +f2 +2001-01-01 01:01:01 +2001-02-05 00:00:00 +2001-03-09 01:01:01 +select f2, f3 from t1 where '01-03-10' between f2 and f3; +f2 f3 +2001-03-09 01:01:01 2001-03-10 01:01:01 +select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15"; +f2 +2001-04-15 00:00:00 +SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(); +1 +drop table t1; --- 1.22/mysql-test/t/type_datetime.test 2007-04-27 00:10:40.000000000 +0400 +++ 1.23/mysql-test/t/type_datetime.test 2007-04-27 00:18:05.000000000 +0400 @@ -161,3 +161,22 @@ f1 > f2, f1 = f2, f1 < f2 from t1; drop table t1; + +# +# Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. +# +create table t1 (f1 date, f2 datetime, f3 timestamp); +insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); +insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); +insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); +insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00'); +insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01'); +select f2 from t1 where f2 between '2001-2-5' and '01-04-14'; +select f1, f2, f3 from t1 where f1 between f2 and f3; +select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and + cast(f3 as date); +select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01'; +select f2, f3 from t1 where '01-03-10' between f2 and f3; +select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15"; +SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(); +drop table t1; --- 1.78/mysql-test/r/query_cache.result 2007-02-14 16:44:30.000000000 +0300 +++ 1.79/mysql-test/r/query_cache.result 2007-04-27 00:18:34.000000000 +0400 @@ -945,25 +945,19 @@ 0 Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' -Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 0 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' -Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' +Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 0 SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 -Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' -Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 0 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0