List:Commits« Previous MessageNext Message »
From:Øystein Grøvlen Date:September 6 2010 7:51am
Subject:Re: bzr commit into mysql-5.5-bugfixing branch (epotemkin:3199) Bug#56271
View as plain text  
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(&ltime, TIME_FUZZY_DATE))
> +    return 0;
> +
> +  if (const_item)
> +  {
> +    switch (cached_field_type) {
> +    case MYSQL_TYPE_DATE:
> +      return TIME_to_ulonglong_date(&ltime);
> +    case MYSQL_TYPE_TIME:
> +      return TIME_to_ulonglong_time(&ltime);
> +    default:
> +      return TIME_to_ulonglong_datetime(&ltime);
> +    }
> +  }
> +  return TIME_to_ulonglong_datetime(&ltime);
> +}
> +
> +
>   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
Thread
bzr commit into mysql-5.5-bugfixing branch (epotemkin:3199) Bug#56271Evgeny Potemkin1 Sep
  • Re: bzr commit into mysql-5.5-bugfixing branch (epotemkin:3199) Bug#56271Øystein Grøvlen6 Sep