Hi Martin,
It would be better fix the bug in this way:
=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc 2010-12-28 23:47:05 +0000
+++ sql/item_cmpfunc.cc 2011-01-17 20:58:43 +0000
@@ -913,7 +913,7 @@
cache_converted_constant can't be used here because it can't
correctly convert a DATETIME value from string to int representation.
*/
- Item_cache_int *cache= new Item_cache_int();
+ Item_cache_int *cache= new Item_cache_int(MYSQL_TYPE_DATETIME);
/* Mark the cache as non-const to prevent re-caching. */
cache->set_used_tables(1);
if (!(*a)->is_datetime())
Regards, Evgen.
On 01/10/11 15:43, Martin Hansson wrote:
> #At file:///data0/martin/bzrroot/bug59173/5.1-commit/ based on
> revid:vasil.dimov@stripped
>
> 3539 Martin Hansson 2011-01-10
> Bug#59173: Failure to handle DATE(TIME) values where Year, Month or Day is
> ZERO
>
> When dates are represented internally as strings, i.e. when a string constant
> is compared to a date value, both values are converted to long integers,
> ostensibly for fast comparisons. DATE typed integer values are converted to
> DATETIME by multiplying by 1,000,000 (each digit pair representing hour,
> minute and second, respectively). But the mechanism did not distuinguish
> cached INTEGER values, already in correct format, from newly converted
> strings.
>
> Fixed by not multiplying the values for cached integer values.
>
> modified:
> mysql-test/r/type_datetime.result
> mysql-test/t/type_datetime.test
> sql/item_cmpfunc.cc
> === modified file 'mysql-test/r/type_datetime.result'
> --- a/mysql-test/r/type_datetime.result 2010-03-09 10:36:26 +0000
> +++ b/mysql-test/r/type_datetime.result 2011-01-10 12:43:01 +0000
> @@ -637,4 +637,15 @@ CAST(CAST('2008-07-29T10:42:51.1234567'
> 20080729104251.1234560
> Warnings:
> Warning 1292 Truncated incorrect datetime value: '2008-07-29T10:42:51.1234567'
> +#
> +# Bug#59173: Failure to handle DATE(TIME) values where Year, Month or
> +# Day is ZERO
> +#
> +CREATE TABLE t1 (dt1 DATETIME);
> +INSERT INTO t1 (dt1) VALUES ('0000-00-01 00:00:01');
> +DELETE FROM t1 WHERE dt1 = '0000-00-01 00:00:01';
> +# Should be empty
> +SELECT * FROM t1;
> +dt1
> +DROP TABLE t1;
> End of 5.1 tests
>
> === modified file 'mysql-test/t/type_datetime.test'
> --- a/mysql-test/t/type_datetime.test 2009-02-13 18:07:03 +0000
> +++ b/mysql-test/t/type_datetime.test 2011-01-10 12:43:01 +0000
> @@ -445,4 +445,15 @@ SELECT CAST(CAST('00000002006-000008-000
> # show we truncate microseconds from the right
> SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7));
>
> +--echo #
> +--echo # Bug#59173: Failure to handle DATE(TIME) values where Year, Month or
> +--echo # Day is ZERO
> +--echo #
> +CREATE TABLE t1 (dt1 DATETIME);
> +INSERT INTO t1 (dt1) VALUES ('0000-00-01 00:00:01');
> +DELETE FROM t1 WHERE dt1 = '0000-00-01 00:00:01';
> +--echo # Should be empty
> +SELECT * FROM t1;
> +DROP TABLE t1;
> +
> --echo End of 5.1 tests
>
> === modified file 'sql/item_cmpfunc.cc'
> --- a/sql/item_cmpfunc.cc 2010-12-28 23:47:05 +0000
> +++ b/sql/item_cmpfunc.cc 2011-01-10 12:43:01 +0000
> @@ -1105,8 +1105,9 @@ get_datetime_value(THD *thd, Item ***ite
> compare it with 100000000L - any DATE value should be less than it.
> Don't shift cached DATETIME values up for the second time.
> */
> - if (f_type == MYSQL_TYPE_DATE ||
> - (f_type != MYSQL_TYPE_DATETIME&& value< 100000000L))
> + if (item->type() != Item::CACHE_ITEM&&
> + (f_type == MYSQL_TYPE_DATE ||
> + (f_type != MYSQL_TYPE_DATETIME&& value< 100000000L)))
> value*= 1000000L;
> }
> else
>
>
>
>
