From: Jorgen Loland
Date: February 14 2011 8:56am
Subject: Re: bzr commit into mysql-5.5 branch (tor.didriksen:3323) Bug#60085
List-Archive: http://lists.mysql.com/commits/131164
Message-Id: <4D58EE2E.2070203@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Tor,

The underlying problem remains: the string-to-int conversion of 
Item_cache_datetime does not work.

Consider this:

CREATE TABLE t1(a date, b int, unique(b), unique(a), key(b)) engine=innodb;
INSERT INTO t1 VALUES ('2011-05-13', 0);
SELECT CAST(a AS unsigned) from t1;
a
20110513

whereas val_int() of the Item_cache_datetime object is "trunkated" to 2011

On 02/11/2011 04:46 PM, Tor Didriksen wrote:
> this patch looks a bit simpler:
>
> === modified file 'sql/item.cc'
> --- sql/item.cc    2011-01-31 10:37:16 +0000
> +++ sql/item.cc    2011-02-11 15:44:19 +0000
> @@ -7594,7 +7594,7 @@
>
>     value_cached= TRUE;
>     // Mark cached string value obsolete
> -  str_value_cached= FALSE;
> +//  str_value_cached= FALSE;
>     /* Assume here that the underlying item will do correct conversion.*/
>     int_value= example->val_int_result();
>     null_value= example->null_value;
>
>
> On Fri, Feb 11, 2011 at 4:38 PM, Jorgen Loland<jorgen.loland@stripped>wrote:
>
>> Hi Tor,
>>
>> I'm afraid you're fixing the symptom instead of the problem.
>>
>> Consider your test case:
>>
>>
>> SELECT 1 FROM t1 WHERE b<  (SELECT CAST(a as date) FROM t1 GROUP BY a);
>>
>> The problem seems to be this
>>
>> 1) Item_cache_datetime is created for item "cast (a as date)".
>>    This is first evaluated by calling val_str() due to
>>    Item_cache_datetime::result_type() == STRING_RESULT
>>
>>    #0  Item_cache_datetime::val_str (this=0x1ad3178, str=0x1af79f8)
>>        at /mysql/mysql-5.5/sql/item.cc:7526
>>    #1  0x00000000007f078f in Item_singlerow_subselect::val_str
>> (this=0x1af79e8,
>>        str=0x1af79f8)
>>        at /mysql/mysql-5.5/sql/item_subselect.cc:605
>>    #2  0x00000000007840d4 in Item::save_in_field (this=0x1af79e8,
>>        field=0x1af8310, no_conversions=true)
>>        at /mysql/mysql-5.5/sql/item.cc:5385
>>    #3  0x000000000077796a in Item::save_in_field_no_warnings
>> (this=0x1af79e8,
>>        field=0x1af8310, no_conversions=true)
>>        at /mysql/mysql-5.5/sql/item.cc:1062
>>
>> 2) The Item_cache_datetime above is compared to column b, which is of type
>> INT.
>>    stored_field_cmp_to_item() decides that STRING and INT comparisons should
>> be
>>    performed by using REAL. By calling Item_cache_datetime::val_real(),
>>    Item_copy_string::val_int() is called:
>>
>>    #0  Item_copy_string::val_int (this=0x1ad5f08)
>>        at /mysql/mysql-5.5/sql/item.cc:3596
>>    #1  0x000000000056d611 in Item::val_int_result (this=0x1ad5f08)
>>        at /mysql/mysql-5.5/sql/item.h:845
>>    #2  0x000000000078ac98 in Item_cache_datetime::cache_value_int
>> (this=0x1ad3178)
>>        at /mysql/mysql-5.5/sql/item.cc:7477
>>    #3  0x000000000078b3c7 in Item_cache_datetime::val_real (this=0x1ad3178)
>>        at /mysql/mysql-5.5/sql/item.cc:7601
>>    #4  0x00000000007f0605 in Item_singlerow_subselect::val_real
>> (this=0x1af7a78)
>>        at /mysql/mysql-5.5/sql/item_subselect.cc:576
>>    #5  0x000000000078a586 in stored_field_cmp_to_item (thd=0x1a36dd0,
>>
>>    But Item_copy_string::val_int() uses my_strntoll() to convert the String
>>    "2011-05-13" to an int. This does not work because my_strntoll() stops
>>    processing when it encounters "-", so the resulting int is 2011 instead
>>    of the expected 20110513. In addition, the call to cache_value_int() sets
>>    str_value_cached=FALSE.
>>
>> 3) When Item_cache_datetime::val_str() is later called,
>> str_value_cached=FALSE.
>>    It tries to convert 2011 to a string using number_to_datetime(). This
>> fails.
>>
>>
>> --
>> Jørgen Løland | Senior Software Engineer | +47 73842138
>> Oracle MySQL
>> Trondheim, Norway
>>
>> --
>> MySQL Code Commits Mailing List
>> For list archives: http://lists.mysql.com/commits
>> To unsubscribe:
>> http://lists.mysql.com/commits?unsub=didrik@stripped
>>
>>
>

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway