List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 11 2011 12:55pm
Subject:Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#58490
View as plain text  
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

Thread
bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#58490Ole John Aske11 Jan
  • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#58490Roy Lyseng11 Jan
  • Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#58490Guilhem Bichot17 Jan