List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:May 3 2010 3:43pm
Subject:Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3360)
Bug#50774
View as plain text  
Hi Martin,

The fix is correct, but as I already said it doesn't work when index isn't 
available. To check this use extended test case (see below).
Please, extend your fix and recommit.

Regargs, Evgen.

On 04/28/10 12:32, Martin Hansson wrote:
> #At file:///Users/martin/bzr/bug50774/5.1bt-commit/ based on
> revid:vvaintroub@stripped
>
>   3360 Martin Hansson	2010-04-28
>        Bug#50774: failed to get the correct resultset when
>        timestamp values are appended with .0
>
>        The range optimizer's comparison function, used for creating
>        search keys for incomplete DATE/DATETIME/TIMESTAMP values
>        failed to parse string values if they were compared in a
>        TIMESTAMP context. Fixed by adding an or-branch.
>
>      modified:
>        mysql-test/r/type_timestamp.result
>        mysql-test/t/type_timestamp.test
>        sql/item.cc
> === modified file 'mysql-test/r/type_timestamp.result'
> --- a/mysql-test/r/type_timestamp.result	2010-02-09 10:30:50 +0000
> +++ b/mysql-test/r/type_timestamp.result	2010-04-28 08:32:19 +0000
> @@ -527,3 +527,37 @@ f1	f2-f3
>   5	0
>   DROP TABLE t1;
>   End of 5.0 tests
> +#
> +# Bug#50774: failed to get the correct resultset when timestamp values
> +# are appended with .0
> +#
> +CREATE TABLE t1 ( a TIMESTAMP, KEY ( a ) );
> +CREATE TABLE t2 ( a CHAR(25) );
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:01' );
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:02' );
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:03' );
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:04' );
> +INSERT INTO t2 VALUES( '2010-02-01 09:31:02.0' ), ( '2010-02-01 09:31:02.0' );
> +EXPLAIN
> +SELECT * FROM t1 WHERE a>= '2010-02-01 09:31:02.0';
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +x	x	x	range	x	x	x	x	x	x
> +SELECT * FROM t1 WHERE a>= '2010-02-01 09:31:02.0';
> +a
> +2010-02-01 09:31:02
> +2010-02-01 09:31:03
> +2010-02-01 09:31:04
> +EXPLAIN
> +SELECT * FROM t1, t2 WHERE t1.a>= t2.a;
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +x	x	t2	ALL	x	x	x	x	x	x
> +x	x	t1	ALL	x	x	x	x	x	x
> +SELECT * FROM t1, t2 WHERE t1.a>= t2.a;
> +a	a
> +2010-02-01 09:31:02	2010-02-01 09:31:02.0
> +2010-02-01 09:31:03	2010-02-01 09:31:02.0
> +2010-02-01 09:31:04	2010-02-01 09:31:02.0
> +2010-02-01 09:31:02	2010-02-01 09:31:02.0
> +2010-02-01 09:31:03	2010-02-01 09:31:02.0
> +2010-02-01 09:31:04	2010-02-01 09:31:02.0
> +DROP TABLE t1, t2;
>
> === modified file 'mysql-test/t/type_timestamp.test'
> --- a/mysql-test/t/type_timestamp.test	2009-01-31 01:08:41 +0000
> +++ b/mysql-test/t/type_timestamp.test	2010-04-28 08:32:19 +0000
> @@ -357,3 +357,29 @@ SELECT f1,f2-f3 FROM t1;
>   DROP TABLE t1;
>
>   --echo End of 5.0 tests
> +
> +--echo #
> +--echo # Bug#50774: failed to get the correct resultset when timestamp values
> +--echo # are appended with .0
> +--echo #
> +CREATE TABLE t1 ( a TIMESTAMP, KEY ( a ) );
> +CREATE TABLE t2 ( a CHAR(25) );
> +
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:01' );
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:02' );
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:03' );
> +INSERT INTO t1 VALUES( '2010-02-01 09:31:04' );
> +
> +INSERT INTO t2 VALUES( '2010-02-01 09:31:02.0' ), ( '2010-02-01 09:31:02.0' );
Replace ^ with:
INSERT INTO t2 VALUES( '2010-02-01 09:31:02.0' ), ( '2010-02-01 09:31:02.1' );
> +
> +--replace_column 1 x 2 x 3 x 5 x 6 x 7 x 8 x 9 x 10 x
> +EXPLAIN
> +SELECT * FROM t1 WHERE a>= '2010-02-01 09:31:02.0';
> +SELECT * FROM t1 WHERE a>= '2010-02-01 09:31:02.0';
Original bug reports says about 'timestamp.X', not just 'timestamp.0'.
So it's better to check.
EXPLAIN
SELECT * FROM t1 WHERE a>= '2010-02-01 09:31:02.1';
SELECT * FROM t1 WHERE a>= '2010-02-01 09:31:02.1';
> +
> +--replace_column 1 x 2 x 5 x 6 x 7 x 8 x 9 x 10 x
> +EXPLAIN
> +SELECT * FROM t1, t2 WHERE t1.a>= t2.a;
> +SELECT * FROM t1, t2 WHERE t1.a>= t2.a;
Please, add:
ALTER TABLE t1 DROP INDEX a;
and run same tests as above.
> +
> +DROP TABLE t1, t2;
>
> === modified file 'sql/item.cc'
> --- a/sql/item.cc	2010-04-06 07:26:59 +0000
> +++ b/sql/item.cc	2010-04-28 08:32:19 +0000
> @@ -6960,7 +6960,9 @@ int stored_field_cmp_to_item(THD *thd, F
>
>       enum_field_types field_type= field->type();
>
> -    if (field_type == MYSQL_TYPE_DATE || field_type == MYSQL_TYPE_DATETIME)
> +    if (field_type == MYSQL_TYPE_DATE ||
> +        field_type == MYSQL_TYPE_DATETIME ||
> +        field_type == MYSQL_TYPE_TIMESTAMP)
>       {
>         enum_mysql_timestamp_type type= MYSQL_TIMESTAMP_ERROR;
>
>
>
>
>
>
Thread
bzr commit into mysql-5.1-bugteam branch (martin.hansson:3360)Bug#50774Martin Hansson28 Apr
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3360)Bug#50774Evgeny Potemkin3 May
    • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3360)Bug#50774Martin Hansson7 May
      • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3360)Bug#50774Evgeny Potemkin9 May