#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:georgi.kodinov@stripped
3477 Ole John Aske 2010-12-02
Updated fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination with IS NULL'
After some more testing, and discussion with Roy L. which has also tested my fix on the next-mr branch
the DBUG_ASSERT(rc==NESTED_LOOP_OK) a line ~11553 was removed.
This was replaced by 'if (rc == NESTED_LOOP_NO_MORE_ROWS) found= false;'.
Furthermore testcases for the duplicated bug#49332 has been added.
--- original description ---
Qualifying an OUTER JOIN with '<column> IS NULL' where <column> is declared as 'NOT NULL'
causes the 'not_exists_optimize' to be enabled.
In evaluate_join_record() the 'not_exists_optimize' caused 'NESTED_LOOP_NO_MORE_ROWS' to be
returned immediately when a matching row was found.
This happened before all 'first_unmatched->found' had been properly evaluated for all
embedding outer joins. This may cause incorrect calls to
evaluate_null_complemented_join_record() after we had returned back to sub_select().
This fix ensures that evaluate_join_record() completes the itteration of the 'first_unmatched-loop',
evaluates all conditions for the unmatched JOIN_TAB's and set 'first_unmatched->found' properly before
possibly returning NESTED_LOOP_NO_MORE_ROWS iff 'not_exists_optimize' was in effect.
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 2010-12-02 12:09:47 +0000
@@ -1427,4 +1427,86 @@ 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
+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 2010-12-02 12:09:47 +0000
@@ -1010,4 +1010,73 @@ 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;
+
+DROP TABLE t1,t2,t3,t4;
+
+## Bug#49332 & 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-10-29 08:23:06 +0000
+++ b/sql/sql_select.cc 2010-12-02 12:09:47 +0000
@@ -11473,6 +11473,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
return NESTED_LOOP_ERROR;
}
+ enum enum_nested_loop_state rc= NESTED_LOOP_OK;
if (!select_cond || select_cond_result)
{
/*
@@ -11496,7 +11497,8 @@ evaluate_join_record(JOIN *join, JOIN_TA
for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
{
if (tab->table->reginfo.not_exists_optimize)
- return NESTED_LOOP_NO_MORE_ROWS;
+ rc= NESTED_LOOP_NO_MORE_ROWS;
+
/* Check all predicates that has just been activated. */
/*
Actually all predicates non-guarded by first_unmatched->found
@@ -11515,7 +11517,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
not to the last table of the current nest level.
*/
join->return_tab= tab;
- return NESTED_LOOP_OK;
+ return rc;
}
}
}
@@ -11530,6 +11532,16 @@ evaluate_join_record(JOIN *join, JOIN_TA
}
/*
+ Setting NESTED_LOOP_NO_MORE_ROWS (if not_exists_optimize)
+ also implies a 'not found' condition. However we could not
+ set this inside the loop above as it would prematurely
+ have terminated the 'first_unmatched' / 'first_unmatched->found'
+ calculations above.
+ */
+ if (rc == NESTED_LOOP_NO_MORE_ROWS)
+ found= false;
+
+ /*
It was not just a return to lower loop level when one
of the newly activated predicates is evaluated as false
(See above join->return_tab= tab).
@@ -11541,7 +11553,6 @@ evaluate_join_record(JOIN *join, JOIN_TA
if (found)
{
- enum enum_nested_loop_state rc;
/* A match from join_tab is found for the current partial join. */
rc= (*join_tab->next_select)(join, join_tab+1, 0);
if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
@@ -11569,7 +11580,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
join->thd->row_count++;
join_tab->read_record.unlock_row(join_tab);
}
- return NESTED_LOOP_OK;
+ return rc;
}
Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20101202120947-ub7q353mmhjm4np3.bundle
