From: Evgeny Potemkin
Date: September 7 2010 6:52am
Subject: Re: bzr commit into mysql-5.5-bugfixing branch (epotemkin:3199)
 Bug#56271
List-Archive: http://lists.mysql.com/commits/117666
Message-Id: <4C85E139.4060304@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT

Hi Oystein,

You're right, I missed this case. It turns out that it's one of MySQL gotcha's: 
<time> + INTERVAL... known to be broken. It's "fixed" by returning NULL in such 
cases. So I will follow.

Regards, Evgen.

On 09/06/10 22:38, Øystein Grøvlen wrote:
> Hi Evgeny,
>
> I have one question to the effects of the new patch on the following test case:
>
> On 09/06/2010 03:21 PM, Evgeny Potemkin wrote:
>
>> === modified file 'mysql-test/r/func_time.result'
>> --- a/mysql-test/r/func_time.result 2010-02-24 13:52:27 +0000
>> +++ b/mysql-test/r/func_time.result 2010-09-06 13:21:23 +0000
>> @@ -1199,7 +1199,7 @@ DROP TABLE t1,t2;
>> set time_zone= @@global.time_zone;
>> select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
>> str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
>> -NULL
>> +0000-00-00 22:10:00
>
> This behavior, in itself, seems OK to me, but it is inconsistent with other
> similar cases. For example:
>
>  > SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
> STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
> 0000-00-00 22:10:00
>  > CREATE TABLE td (t TIME, d DATETIME);
>  > INSERT INTO td SELECT STR_TO_DATE('10:00 PM', '%h:%i %p'), NULL;
>  > SELECT * FROM td;
> t d
> 22:00:00 NULL
>  > SELECT t + INTERVAL 10 MINUTE FROM td;
> t + INTERVAL 10 MINUTE
> NULL
> Warnings:
> Warning 1264 Out of range value for column 't' at row 1
>  > UPDATE td SET d = t + INTERVAL 10 MINUTE;
> Warnings:
> Warning 1264 Out of range value for column 't' at row 1
>  > SELECT * FROM td;
> t d
> 22:00:00 NULL
>
> Do you consider this a separate bug?
> Consistent behavior has value in itself, so may we should fix all cases or none
> here ...
>
> I am bit worried about so many changes in behavior in a bug fix that was
> originally about a regression.
>
> --
> Øystein
>
>
>> create table t1 (field DATE);
>> insert into t1 values ('2006-11-06');
>> select * from t1 where field< '2006-11-06 04:08:36.0';
>>
>> === modified file 'mysql-test/r/parser.result'
>> --- a/mysql-test/r/parser.result 2010-06-09 08:46:24 +0000
>> +++ b/mysql-test/r/parser.result 2010-09-06 13:21:23 +0000
>> @@ -555,10 +555,10 @@ ERROR 42000: Incorrect parameters in the
>> DROP TABLE IF EXISTS t1;
>> SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
>> STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
>> -NULL
>> +0000-00-00 22:10:00
>> SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1)
>> MINUTE;
>> STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE
>> -NULL
>> +0000-00-00 22:01:00
>> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
>> "1997-12-31 23:59:59" + INTERVAL 1 SECOND
>> 1998-01-01 00:00:00
>>
>> === 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-06 13:21:23 +0000
>> @@ -4171,9 +4171,10 @@ str_to_date('2007-10-00','%Y-%m-%d') bet
>> set SQL_MODE=TRADITIONAL;
>> select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
>> str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
>> -0
>> +NULL
>> Warnings:
>> Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34'
>> +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function
>> str_to_date
>> select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
>> str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'
>> 0
>> @@ -4181,17 +4182,16 @@ Warnings:
>> Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34'
>> 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
>> +NULL
>> Warnings:
>> -Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34:00'
>> +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function
>> str_to_date
>> 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
>> +NULL
>> 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
>> +Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date
>> 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/strict.result'
>> --- a/mysql-test/r/strict.result 2010-07-30 15:28:36 +0000
>> +++ b/mysql-test/r/strict.result 2010-09-06 13:21:23 +0000
>> @@ -206,12 +206,11 @@ INSERT INTO t1 (col1) VALUES (STR_TO_DAT
>> INSERT INTO t1 (col2) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
>> INSERT INTO t1 (col3) VALUES (STR_TO_DATE('15.10.2004 10.15','%d.%m.%Y %H.%i'));
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
>> -Warnings:
>> -Note 1265 Data truncated for column 'col1' at row 1
>> +ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
>> -ERROR 22007: Incorrect date value: '2004-00-31 15:30:00' for column 'col1' at
>> row 1
>> +ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
>> -ERROR 22007: Incorrect date value: '2004-10-00 15:30:00' for column 'col1' at
>> row 1
>> +ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
>> ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at
>> row 1
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
>> @@ -221,12 +220,13 @@ ERROR 22007: Incorrect date value: '2003
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
>> ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
>> -ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
>> +ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
>> +ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
>> -ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column
>> 'col2' at row 1
>> +ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
>> -ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column
>> 'col2' at row 1
>> +ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
>> ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2'
>> at row 1
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
>> @@ -236,13 +236,13 @@ ERROR 22007: Incorrect datetime value: '
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
>> ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
>> -ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col2' at row 1
>> +ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
>> -ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column
>> 'col3' at row 1
>> +ERROR HY000: Incorrect datetime value: '31.10.0000 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
>> -ERROR 22007: Incorrect datetime value: '2004-00-31 15:30:00' for column
>> 'col3' at row 1
>> +ERROR HY000: Incorrect datetime value: '31.0.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
>> -ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column
>> 'col3' at row 1
>> +ERROR HY000: Incorrect datetime value: '0.10.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
>> ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3'
>> at row 1
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i'));
>> @@ -252,7 +252,7 @@ ERROR 22007: Incorrect datetime value: '
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
>> ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function
>> str_to_date
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
>> -ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col3' at row 1
>> +ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date
>> drop table t1;
>> CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
>> INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
>> @@ -1108,6 +1108,9 @@ Warnings:
>> Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function
>> str_to_date
>> Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function
>> str_to_date
>> Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function
>> str_to_date
>> +Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function
>> str_to_date
>> +Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function
>> str_to_date
>> +Warning 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function
>> str_to_date
>> drop table t1;
>> create table t1 (col1 char(3), col2 integer);
>> insert into t1 (col1) values (cast(1000 as char(3)));
>>
>> === 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-06 13:21:23 +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/strict.test'
>> --- a/mysql-test/t/strict.test 2010-03-18 10:38:29 +0000
>> +++ b/mysql-test/t/strict.test 2010-09-06 13:21:23 +0000
>> @@ -192,11 +192,11 @@ INSERT INTO t1 (col3) VALUES (STR_TO_DAT
>> # All test cases expected to fail should return
>> # SQLSTATE 22007<invalid date value>
>>
>> +--error 1411
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
>> -
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
>> --error 1292
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
>> @@ -206,18 +206,18 @@ INSERT INTO t1 (col1) VALUES(STR_TO_DATE
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
>> --error 1411
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
>>
>> ## Test INSERT with STR_TO_DATE into DATETIME
>> # All test cases expected to fail should return
>> # SQLSTATE 22007<invalid datetime value>
>>
>> +--error 1411
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
>> -
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
>> --error 1292
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
>> @@ -227,18 +227,18 @@ INSERT INTO t1 (col2) VALUES(STR_TO_DATE
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
>> --error 1411
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
>>
>> ## Test INSERT with STR_TO_DATE into TIMESTAMP
>> # All test cases expected to fail should return
>> # SQLSTATE 22007<invalid datetime value>
>>
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i'));
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i'));
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i'));
>> --error 1292
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i'));
>> @@ -248,7 +248,7 @@ INSERT INTO t1 (col3) VALUES(STR_TO_DATE
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i'));
>> --error 1411
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
>> ---error 1292
>> +--error 1411
>> INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
>>
>> drop table t1;
>>
>> === 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-06 13:21:23 +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-common/my_time.c'
>> --- a/sql-common/my_time.c 2010-07-09 12:28:51 +0000
>> +++ b/sql-common/my_time.c 2010-09-06 13:21:23 +0000
>> @@ -756,7 +756,7 @@ uint year_2000_handling(uint year)
>> SYNOPSIS
>> calc_daynr()
>> year Year (exact 4 digit year, no year conversions)
>> - month Month
>> + month Month (should be> 0 )
>> day Day
>>
>> NOTES: 0000-00-00 is a valid date, and will return 0
>> @@ -774,7 +774,6 @@ long calc_daynr(uint year,uint month,uin
>>
>> if (y == 0&& month == 0&& day == 0)
>> DBUG_RETURN(0); /* Skip errors */
>> - /* Cast to int to be able to handle month == 0 */
>> delsum= (long) (365 * y + 31 *((int) month - 1) + (int) day);
>> if (month<= 2)
>> y--;
>>
>> === 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-06 13:21:23 +0000
>> @@ -3368,6 +3368,8 @@ void Item_func_str_to_date::fix_length_a
>> cached_field_type= MYSQL_TYPE_DATETIME;
>> max_length= MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
>> cached_timestamp_type= MYSQL_TIMESTAMP_NONE;
>> + sql_mode= (current_thd->variables.sql_mode&
>> + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE));
>> if ((const_item= args[1]->const_item()))
>> {
>> char format_buff[64];
>> @@ -3418,6 +3420,7 @@ bool Item_func_str_to_date::get_date(MYS
>> if (extract_date_time(&date_time_format, val->ptr(), val->length(),
>> ltime, cached_timestamp_type, 0, "datetime") ||
>> ((fuzzy_date& TIME_NO_ZERO_DATE)&&
>> + cached_timestamp_type != MYSQL_TIMESTAMP_TIME&&
>> (ltime->year == 0 || ltime->month == 0 || ltime->day == 0)))
>> goto null_date;
>> if (cached_timestamp_type == MYSQL_TIMESTAMP_TIME&& ltime->day)
>> @@ -3433,6 +3436,14 @@ bool Item_func_str_to_date::get_date(MYS
>> return 0;
>>
>> null_date:
>> + if (fuzzy_date& TIME_NO_ZERO_DATE)
>> + {
>> + char buff[128];
>> + strmake(buff, val->ptr(), min(val->length(), sizeof(buff)-1));
>> + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
>> + ER_WRONG_VALUE_FOR_TYPE, ER(ER_WRONG_VALUE_FOR_TYPE),
>> + "datetime", buff, "str_to_date");
>> + }
>> return (null_value=1);
>> }
>>
>> @@ -3442,7 +3453,7 @@ String *Item_func_str_to_date::val_str(S
>> DBUG_ASSERT(fixed == 1);
>> MYSQL_TIME ltime;
>>
>> - if (Item_func_str_to_date::get_date(&ltime, TIME_FUZZY_DATE))
>> + if (Item_func_str_to_date::get_date(&ltime, TIME_FUZZY_DATE | sql_mode))
>> return 0;
>>
>> if (!make_datetime((const_item ? cached_format_type :
>> @@ -3453,6 +3464,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 | sql_mode))
>> + 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-06 13:21:23 +0000
>> @@ -1039,6 +1039,7 @@ class Item_func_str_to_date :public Item
>> date_time_format_types cached_format_type;
>> timestamp_type cached_timestamp_type;
>> bool const_item;
>> + ulonglong sql_mode;
>> public:
>> Item_func_str_to_date(Item *a, Item *b)
>> :Item_str_func(a, b), const_item(false)
>> @@ -1052,6 +1053,8 @@ public:
>> {
>> return tmp_table_field_from_field_type(table, 1);
>> }
>> + longlong val_int();
>> + bool result_as_longlong() { return TRUE; }
>> };
>>
>>
>>
>> === modified file 'sql/sql_time.cc'
>> --- a/sql/sql_time.cc 2010-03-31 14:05:33 +0000
>> +++ b/sql/sql_time.cc 2010-09-06 13:21:23 +0000
>> @@ -865,7 +865,8 @@ bool date_add_interval(MYSQL_TIME *ltime
>> extra_sec= microseconds/1000000L;
>> microseconds= microseconds%1000000L;
>>
>> - sec=((ltime->day-1)*3600*24L+ltime->hour*3600+ltime->minute*60+
>> + sec=((ltime->day ? (ltime->day-1)*3600*24L : 0 ) +
>> + ltime->hour*3600+ltime->minute*60+
>> ltime->second +
>> sign* (longlong) (interval.day*3600*24L +
>> interval.hour*LL(3600)+interval.minute*LL(60)+
>> @@ -886,7 +887,10 @@ bool date_add_interval(MYSQL_TIME *ltime
>> ltime->second= (uint) (sec % 60);
>> ltime->minute= (uint) (sec/60 % 60);
>> ltime->hour= (uint) (sec/3600);
>> - daynr= calc_daynr(ltime->year,ltime->month,1) + days;
>> + if (ltime->year> 0&& ltime->month> 0)
>> + daynr= calc_daynr(ltime->year, ltime->month, 1) + days;
>> + else
>> + daynr= days;
>> /* Day number from year 0 to 9999-12-31 */
>> if ((ulonglong) daynr> MAX_DAY_NUMBER)
>> goto invalid_date;
>>
>>
>>
>>
>>
>
>
>