Approved.
--
Øystein Grøvlen
On 09/ 1/10 11:27 PM, Evgeny Potemkin wrote:
> #At file:///work/bzrroot/56271-bug-5.5-bugfixing/ based on
> revid:alik@stripped
>
> 3199 Evgeny Potemkin 2010-09-01
> Bug#56271: Wrong comparison result with STR_TO_DATE function
> The Item_func_str_to_date class wasn't providing correct integer DATETIME
> representation as expected. This led to wrong comparison result and didn't
> allowed the STR_TO_DATE function to be used with indexes.
>
> val_int and result_as_longlong methods were added to the
> Item_func_str_to_date
> class.
> @ mysql-test/r/select.result
> A test case result adjusted after fixing bug#56271.
> @ mysql-test/r/type_datetime.result
> Added a test case for the bug#56271.
> @ mysql-test/t/type_datetime.test
> Added a test case for the bug#56271.
> @ sql/item_timefunc.cc
> Bug#56271: Wrong comparison result with STR_TO_DATE function
> val_int and result_as_longlong methods were added to the
> Item_func_str_to_date
> class.
> @ sql/item_timefunc.h
> Bug#56271: Wrong comparison result with STR_TO_DATE function
> val_int and result_as_longlong methods were added to the
> Item_func_str_to_date
> class.
>
> modified:
> mysql-test/r/select.result
> mysql-test/r/type_datetime.result
> mysql-test/t/type_datetime.test
> sql/item_timefunc.cc
> sql/item_timefunc.h
> === modified file 'mysql-test/r/select.result'
> --- a/mysql-test/r/select.result 2010-08-25 19:00:38 +0000
> +++ b/mysql-test/r/select.result 2010-09-01 21:27:24 +0000
> @@ -4182,16 +4182,11 @@ Warning 1292 Truncated incorrect datetim
> select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
> str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'
> 0
> -Warnings:
> -Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34:00'
> select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
> and '2007/10/20';
> str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
> and '2007/10/20'
> -0
> -Warnings:
> -Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/09/01' at row
> 1
> -Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/10/20' at row
> 1
> +1
> set SQL_MODE=DEFAULT;
> select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
> str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'
>
> === modified file 'mysql-test/r/type_datetime.result'
> --- a/mysql-test/r/type_datetime.result 2010-05-05 09:28:37 +0000
> +++ b/mysql-test/r/type_datetime.result 2010-09-01 21:27:24 +0000
> @@ -655,5 +655,30 @@ Note 1003 select `test`.`t1`.`Id` AS `Id
> DROP TABLE t1;
> SET NAMES latin1;
> #
> +# Bug#56271: Wrong comparison result with STR_TO_DATE function
> +#
> +CREATE TABLE t1 (
> +`year` int(4) NOT NULL,
> +`month` int(2) NOT NULL
> +);
> +INSERT INTO t1 VALUES (2010,3),(2010,4),(2009,8),(2008,9);
> +SELECT *
> +FROM t1
> +WHERE STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y')>=
> +STR_TO_DATE('1/1/2010', '%m/%d/%Y');
> +year month
> +2010 3
> +2010 4
> +create table t2(f1 datetime primary key);
> +insert into t2 select STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') from
> t1;
> +select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y');
> +f1
> +2010-04-01 00:00:00
> +t2 should be const
> +explain select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y');
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t2 const PRIMARY PRIMARY 8 const 1 Using index
> +DROP TABLE t1,t2;
> +#
> # End of 5.5 tests
> #
>
> === modified file 'mysql-test/t/type_datetime.test'
> --- a/mysql-test/t/type_datetime.test 2010-05-05 09:28:37 +0000
> +++ b/mysql-test/t/type_datetime.test 2010-09-01 21:27:24 +0000
> @@ -462,5 +462,28 @@ DROP TABLE t1;
> SET NAMES latin1;
>
> --echo #
> +--echo # Bug#56271: Wrong comparison result with STR_TO_DATE function
> +--echo #
> +CREATE TABLE t1 (
> + `year` int(4) NOT NULL,
> + `month` int(2) NOT NULL
> +);
> +
> +INSERT INTO t1 VALUES (2010,3),(2010,4),(2009,8),(2008,9);
> +
> +SELECT *
> +FROM t1
> +WHERE STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y')>=
> +STR_TO_DATE('1/1/2010', '%m/%d/%Y');
> +
> +create table t2(f1 datetime primary key);
> +insert into t2 select STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') from
> t1;
> +select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y');
> +--echo t2 should be const
> +explain select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y');
> +
> +DROP TABLE t1,t2;
> +
> +--echo #
> --echo # End of 5.5 tests
> --echo #
>
> === modified file 'sql/item_timefunc.cc'
> --- a/sql/item_timefunc.cc 2010-07-09 12:28:51 +0000
> +++ b/sql/item_timefunc.cc 2010-09-01 21:27:24 +0000
> @@ -3453,6 +3453,29 @@ String *Item_func_str_to_date::val_str(S
> }
>
>
> +longlong Item_func_str_to_date::val_int()
> +{
> + DBUG_ASSERT(fixed == 1);
> + MYSQL_TIME ltime;
> +
> + if (Item_func_str_to_date::get_date(<ime, TIME_FUZZY_DATE))
> + return 0;
> +
> + if (const_item)
> + {
> + switch (cached_field_type) {
> + case MYSQL_TYPE_DATE:
> + return TIME_to_ulonglong_date(<ime);
> + case MYSQL_TYPE_TIME:
> + return TIME_to_ulonglong_time(<ime);
> + default:
> + return TIME_to_ulonglong_datetime(<ime);
> + }
> + }
> + return TIME_to_ulonglong_datetime(<ime);
> +}
> +
> +
> bool Item_func_last_day::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
> {
> if (get_arg0_date(ltime, fuzzy_date& ~TIME_FUZZY_DATE) ||
>
> === modified file 'sql/item_timefunc.h'
> --- a/sql/item_timefunc.h 2010-03-31 14:05:33 +0000
> +++ b/sql/item_timefunc.h 2010-09-01 21:27:24 +0000
> @@ -1052,6 +1052,8 @@ public:
> {
> return tmp_table_field_from_field_type(table, 1);
> }
> + longlong val_int();
> + bool result_as_longlong() { return TRUE; }
> };
>
>
>
>
>
>
>
--
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway
