From: Roy Lyseng
Date: December 14 2010 1:00pm
Subject: Re: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#57034
List-Archive: http://lists.mysql.com/commits/126767
Message-Id: <4D076A8A.4010402@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Ole John,

thank you for fixing this problem.

There's only a minor comment below.

But please also add the test cases for bug#48046 and bug#49600 as well.

On 03.12.10 17.55, Ole John Aske wrote:
> #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:georgi.kodinov@stripped
>
>   3477 Ole John Aske	2010-12-03
>        Fix for bug#57034: incorrect OUTER JOIN result when joined on unique key.
>
>        The value of 'null_value' is not valid until *after* ::store_value() or ::cmp() has
>        been called for the Item object.
>
>        Fix is to check swap order of ::store_value()/::cmp() and checking of Item::null_value.
>        This pattern is widely used other places inside item_cmpfunc.cc
>
>      modified:
>        mysql-test/r/join_outer.result
>        mysql-test/t/join_outer.test
>        sql/item_cmpfunc.cc
> === modified file 'mysql-test/r/join_outer.result'
> --- a/mysql-test/r/join_outer.result	2010-10-29 08:23:06 +0000
> +++ b/mysql-test/r/join_outer.result	2010-12-03 16:55:30 +0000
> @@ -1427,4 +1427,24 @@ WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AN
>   GROUP BY t2.f1, t2.f2;
>   f1	f1	f2
>   DROP TABLE t1,t2;
> +#
> +# Bug#57034 incorrect OUTER JOIN result when joined on unique key
> +#
> +CREATE TABLE t1 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);
> +INSERT INTO t1 VALUES (1,NULL,2), (2,0,0);
> +CREATE TABLE t2 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);

The CREATE TABLE lines are too long.
> +INSERT INTO t2 VALUES (1,0,1), (2,0,2);
> +EXPLAIN
> +SELECT * FROM t1 LEFT JOIN t2
> +ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int
> +WHERE t1.pk=1;
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
> +1	SIMPLE	t2	const	col_int_unique	col_int_unique	5	const	1	
> +SELECT * FROM t1 LEFT JOIN t2
> +ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int
> +WHERE t1.pk=1;
> +pk	col_int	col_int_unique	pk	col_int	col_int_unique
> +1	NULL	2	NULL	NULL	NULL
> +DROP TABLE t1,t2;
>   End of 5.1 tests
>
> === modified file 'mysql-test/t/join_outer.test'
> --- a/mysql-test/t/join_outer.test	2010-10-29 08:23:06 +0000
> +++ b/mysql-test/t/join_outer.test	2010-12-03 16:55:30 +0000
> @@ -1010,4 +1010,24 @@ GROUP BY t2.f1, t2.f2;
>
>   DROP TABLE t1,t2;
>
> +--echo #
> +--echo # Bug#57034 incorrect OUTER JOIN result when joined on unique key
> +--echo #
> +
> +CREATE TABLE t1 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);
> +INSERT INTO t1 VALUES (1,NULL,2), (2,0,0);
> +CREATE TABLE t2 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);
> +INSERT INTO t2 VALUES (1,0,1), (2,0,2);
> +
> +EXPLAIN
> +SELECT * FROM t1 LEFT JOIN t2
> +  ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int
> +  WHERE t1.pk=1;
> +
> +SELECT * FROM t1 LEFT JOIN t2
> +  ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int
> +  WHERE t1.pk=1;
> +
> +DROP TABLE t1,t2;
> +
>   --echo End of 5.1 tests
>
> === modified file 'sql/item_cmpfunc.cc'
> --- a/sql/item_cmpfunc.cc	2010-09-09 12:48:06 +0000
> +++ b/sql/item_cmpfunc.cc	2010-12-03 16:55:30 +0000
> @@ -5604,15 +5604,15 @@ longlong Item_equal::val_int()
>       return 0;
>     List_iterator_fast<Item_field>  it(fields);
>     Item *item= const_item ? const_item : it++;
> +  eval_item->store_value(item);
>     if ((null_value= item->null_value))
>       return 0;
> -  eval_item->store_value(item);
>     while ((item_field= it++))
>     {
>       /* Skip fields of non-const tables. They haven't been read yet */
>       if (item_field->field->table->const_table)
>       {
> -      if ((null_value= item_field->null_value) || eval_item->cmp(item_field))
> +      if (eval_item->cmp(item_field) || (null_value= item_field->null_value))
>           return 0;
>       }
>     }
Thanks,
Roy