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

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