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
