diff options
| -rw-r--r-- | mysql-test/r/join_outer.result | 142 | ||||
| -rw-r--r-- | mysql-test/t/join_outer.test | 124 | ||||
| -rw-r--r-- | sql/sql_select.cc | 27 |
3 files changed, 291 insertions, 2 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index ecd53003513..2320d399078 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1502,4 +1502,146 @@ WHERE 7; col_datetime_key NULL DROP TABLE BB; +# +# 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 diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 72d27d3571a..991854cfb78 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1092,4 +1092,128 @@ FROM BB table1 RIGHT JOIN BB table2 DROP TABLE BB; + +--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 diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4fa469ce652..860b41731b1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -11767,17 +11767,40 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, 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' + predicate is still present on any 'tab' with + '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 rows of 'tab' for this partial result. + Any found 'tab' matches are known to evaluate to 'false'. + Returning .._NO_MORE_ROWS will skip rem. 'tab' rows. + */ + return NESTED_LOOP_NO_MORE_ROWS; + } + if (tab == join_tab) found= 0; else |