From: Roy Lyseng Date: January 11 2011 12:55pm Subject: Re: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#58490 List-Archive: http://lists.mysql.com/commits/128419 Message-Id: <4D2C5338.2010700@oracle.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------070503090603050802050108" --------------070503090603050802050108 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 IS NULL', > where 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 --------------070503090603050802050108--