Hi Ole John,
thank you for fixing this problem!
Bugfix is approved, there are only some typos and terminology comments.
On 11.01.11 10.00, Ole John Aske wrote:
> #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on
> revid:nirbhay.choubey@stripped
>
> 3534 Ole John Aske 2011-01-11
> Fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination
> with IS NULL'
>
> As this bug is a duplicate of bug#49322, it also include tescases
includes test cases
> covering this bugreport
>
> Qualifying an OUTER JOIN with the condition 'WHERE<column> IS NULL',
> where<column> is declared as 'NOT NULL' causes the
> 'not_exists_optimize' to be enabled by the optimizer.
>
> In evaluate_join_record() the 'not_exists_optimize' caused
> 'NESTED_LOOP_NO_MORE_ROWS' to be returned immediately
> when a matching row was found.
>
> However, as the 'not_exists_optimize' is derived from
> 'JOIN_TAB::select_cond', the usual rules for condition guards
> also applies for 'not_exist_optimize'. It is therefore incorrect
> to check 'not_exists_optimize' without ensuring that all guards
> protecting it is 'open'.
>
> This fix uses the fact that 'not_exists_optimize' is derived from
> a 'is_null' condition term in 'tab->select_cond'. Futhrermore,
an is_null predicate ... Furthermore
> 'is_null' will evaluate to 'false' for any 'non-null' rows
> once all guards protecting the is_null is open.
>
> We can use this knowledge as an implicit guard check for the
> 'not_exists_optimize' by moving 'if (...not_exists_optimize)'
> inside the handling of 'select_cond==false'. It will then
> not take effect before its guards are open.
>
> We also add an assert which requires that a
> 'not_exists_optimize' always comes together with
> a select_cond. (containing 'is_null').
>
> modified:
> mysql-test/r/join_outer.result
> mysql-test/t/join_outer.test
> sql/sql_select.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 2011-01-11 09:00:00 +0000
> @@ -1427,4 +1427,146 @@ 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#58490: Incorrect result in multi level OUTER JOIN
> +# in combination with IS NULL
> +#
> +CREATE TABLE t1 (i INT NOT NULL);
> +INSERT INTO t1 VALUES (0), (2),(3),(4);
> +CREATE TABLE t2 (i INT NOT NULL);
> +INSERT INTO t2 VALUES (0),(1), (3),(4);
> +CREATE TABLE t3 (i INT NOT NULL);
> +INSERT INTO t3 VALUES (0),(1),(2), (4);
> +CREATE TABLE t4 (i INT NOT NULL);
> +INSERT INTO t4 VALUES (0),(1),(2),(3) ;
> +SELECT * FROM
> +t1 LEFT JOIN
> +( t2 LEFT JOIN
> +( t3 LEFT JOIN
> +t4
> +ON t4.i = t3.i
> +)
> +ON t3.i = t2.i
> +)
> +ON t2.i = t1.i
> +;
> +i i i i
> +0 0 0 0
> +2 NULL NULL NULL
> +3 3 NULL NULL
> +4 4 4 NULL
> +SELECT * FROM
> +t1 LEFT JOIN
> +( t2 LEFT JOIN
> +( t3 LEFT JOIN
> +t4
> +ON t4.i = t3.i
> +)
> +ON t3.i = t2.i
> +)
> +ON t2.i = t1.i
> +WHERE t4.i IS NULL;
> +i i i i
> +2 NULL NULL NULL
> +3 3 NULL NULL
> +4 4 4 NULL
> +SELECT * FROM
> +t1 LEFT JOIN
> +( ( t2 LEFT JOIN
> +t3
> +ON t3.i = t2.i
> +)
> +)
> +ON t2.i = t1.i
> +WHERE t3.i IS NULL;
> +i i i
> +2 NULL NULL
> +3 3 NULL
> +SELECT * FROM
> +t1 LEFT JOIN
> +( ( t2 LEFT JOIN
> +t3
> +ON t3.i = t2.i
> +)
> +JOIN t4
> +ON t4.i=t2.i
> +)
> +ON t2.i = t1.i
> +WHERE t3.i IS NULL;
> +i i i i
> +2 NULL NULL NULL
> +3 3 NULL 3
> +4 NULL NULL NULL
> +SELECT * FROM
> +t1 LEFT JOIN
> +( ( t2 LEFT JOIN
> +t3
> +ON t3.i = t2.i
> +)
> +JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
> +ON t4a.i=t2.i
> +)
> +ON t2.i = t1.i
> +WHERE t3.i IS NULL;
> +i i i i i
> +2 NULL NULL NULL NULL
> +3 3 NULL 3 3
> +4 NULL NULL NULL NULL
> +SELECT * FROM
> +t1 LEFT JOIN
> +( ( t2 LEFT JOIN
> +t3
> +ON t3.i = t2.i
> +)
> +JOIN (t4 AS t4a, t4 AS t4b)
> +ON t4a.i=t2.i
> +)
> +ON t2.i = t1.i
> +WHERE t3.i IS NULL;
> +i i i i i
> +2 NULL NULL NULL NULL
> +3 3 NULL 3 0
> +3 3 NULL 3 1
> +3 3 NULL 3 2
> +3 3 NULL 3 3
> +4 NULL NULL NULL NULL
> +DROP TABLE t1,t2,t3,t4;
> +#
> +# Bug#49322(Duplicate): Server is adding extra NULL row
> +# on processing a WHERE clause
> +#
> +CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
> +INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
> +CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
> +INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
> +CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
> +INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
> +SELECT TABLE1.pk FROM k TABLE1
> +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
> +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
> +pk
> +2
> +4
> +2
> +4
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +SELECT TABLE1.pk FROM k TABLE1
> +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
> +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
> +WHERE TABLE1.pk IS NULL;
> +pk
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +NULL
> +DROP TABLE h,m,k;
> 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 2011-01-11 09:00:00 +0000
> @@ -1010,4 +1010,128 @@ GROUP BY t2.f1, t2.f2;
>
> DROP TABLE t1,t2;
>
> +
> +--echo #
> +--echo # Bug#58490: Incorrect result in multi level OUTER JOIN
> +--echo # in combination with IS NULL
> +--echo #
> +
> +CREATE TABLE t1 (i INT NOT NULL);
> +INSERT INTO t1 VALUES (0), (2),(3),(4);
> +CREATE TABLE t2 (i INT NOT NULL);
> +INSERT INTO t2 VALUES (0),(1), (3),(4);
> +CREATE TABLE t3 (i INT NOT NULL);
> +INSERT INTO t3 VALUES (0),(1),(2), (4);
> +CREATE TABLE t4 (i INT NOT NULL);
> +INSERT INTO t4 VALUES (0),(1),(2),(3) ;
> +
> +SELECT * FROM
> + t1 LEFT JOIN
> + ( t2 LEFT JOIN
> + ( t3 LEFT JOIN
> + t4
> + ON t4.i = t3.i
> + )
> + ON t3.i = t2.i
> + )
> + ON t2.i = t1.i
> + ;
> +
> +SELECT * FROM
> + t1 LEFT JOIN
> + ( t2 LEFT JOIN
> + ( t3 LEFT JOIN
> + t4
> + ON t4.i = t3.i
> + )
> + ON t3.i = t2.i
> + )
> + ON t2.i = t1.i
> + WHERE t4.i IS NULL;
> +
> +
> +# Most simplified testcase to reproduce the bug.
> +# (Has to be at least a two level nested outer join)
> +SELECT * FROM
> + t1 LEFT JOIN
> + ( ( t2 LEFT JOIN
> + t3
> + ON t3.i = t2.i
> + )
> + )
> + ON t2.i = t1.i
> + WHERE t3.i IS NULL;
> +
> +
> +# Extended testing:
> +# We then add some equi-join inside the query above:
> +# (There Used to be some problems here with first
> +# proposed patch for this bug)
> +SELECT * FROM
> + t1 LEFT JOIN
> + ( ( t2 LEFT JOIN
> + t3
> + ON t3.i = t2.i
> + )
> + JOIN t4
> + ON t4.i=t2.i
> + )
> + ON t2.i = t1.i
> + WHERE t3.i IS NULL;
> +
> +SELECT * FROM
> + t1 LEFT JOIN
> + ( ( t2 LEFT JOIN
> + t3
> + ON t3.i = t2.i
> + )
> + JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
> + ON t4a.i=t2.i
> + )
> + ON t2.i = t1.i
> + WHERE t3.i IS NULL;
> +
> +SELECT * FROM
> + t1 LEFT JOIN
> + ( ( t2 LEFT JOIN
> + t3
> + ON t3.i = t2.i
> + )
> + JOIN (t4 AS t4a, t4 AS t4b)
> + ON t4a.i=t2.i
> + )
> + ON t2.i = t1.i
> + WHERE t3.i IS NULL;
> +
> +
> +DROP TABLE t1,t2,t3,t4;
> +
> +## Bug#49322& bug#58490 are duplicates. However, we include testcases
> +## for both.
> +--echo #
> +--echo # Bug#49322(Duplicate): Server is adding extra NULL row
> +--echo # on processing a WHERE clause
> +--echo #
> +
> +CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
> +INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
> +
> +CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
> +INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
> +CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
> +INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
> +
> +# Baseline query wo/ 'WHERE ... IS NULL' - was correct
> +SELECT TABLE1.pk FROM k TABLE1
> +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
> +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
> +
> +# Adding 'WHERE ... IS NULL' -> incorrect result
> +SELECT TABLE1.pk FROM k TABLE1
> +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
> +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
> +WHERE TABLE1.pk IS NULL;
> +
> +DROP TABLE h,m,k;
> +
> --echo End of 5.1 tests
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-12-28 23:47:05 +0000
> +++ b/sql/sql_select.cc 2011-01-11 09:00:00 +0000
> @@ -11500,17 +11500,40 @@ evaluate_join_record(JOIN *join, JOIN_TA
> first_unmatched->found= 1;
> for (JOIN_TAB *tab= first_unmatched; tab<= join_tab; tab++)
> {
> - if (tab->table->reginfo.not_exists_optimize)
> - return NESTED_LOOP_NO_MORE_ROWS;
> /* Check all predicates that has just been activated. */
> /*
> Actually all predicates non-guarded by first_unmatched->found
> will be re-evaluated again. It could be fixed, but, probably,
> it's not worth doing now.
> */
> + /*
> + not_exists_optimize has been created from a
> + select_cond containing 'is_null'. This 'is_null'
> + condition is still present on any 'tab' with
is_null predicate
> + 'not_exists_optimize'. Furthermore, the usual rules
> + for condition guards also applies for
> + 'not_exists_optimize' -> When 'is_null==false' we
> + know all cond. guards are open and we can apply
> + the 'not_exists_optimize'.
> + */
> + DBUG_ASSERT(!(tab->table->reginfo.not_exists_optimize&&
> + !tab->select_cond));
> +
> if (tab->select_cond&& !tab->select_cond->val_int())
> {
> /* The condition attached to table tab is false */
> +
> + if (tab->table->reginfo.not_exists_optimize)
> + {
> + /*
> + When not_exists_optimize is set: No need to further
> + explore more records of 'tab' for this partial result.
records -> rows
> + Any found 'tab' matches are known to evaluate to 'false'.
> + Returning .._NO_MORE_ROWS will skip rem. 'tab' records.
rem. 'tab' records -> remaining 'tab' rows.
> + */
> + return NESTED_LOOP_NO_MORE_ROWS;
> + }
> +
> if (tab == join_tab)
> found= 0;
> else
>
>
>
>
Thanks,
Roy
