diff options
| -rw-r--r-- | mysql-test/r/derived_view.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 22 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 23 | ||||
| -rw-r--r-- | sql/table.cc | 9 |
4 files changed, 58 insertions, 2 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 6061fbb8800..a4f7a71dcb5 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1677,6 +1677,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; b c a +8 c c EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1691,6 +1692,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; b c a +8 c c INSERT INTO t3 VALUES (100), (200); EXPLAIN EXTENDED SELECT t.b, t.c, t1.a @@ -1706,7 +1708,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; b c a -NULL NULL c +8 c c EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1721,7 +1723,7 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; b c a -NULL NULL c +8 c c SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 332d3dadef0..c12bf8ada06 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4588,6 +4588,28 @@ INSERT INTO v2 (f1, f2) VALUES (1, 2); ERROR HY000: Can not modify more than one base table through a join view 'test.v2' drop view v4,v3,v2,v1; drop table t1,t2; +# +# MDEV-3799 fix of above bugfix (MDEV-589) +# Wrong result (NULLs instead of real values) with RIGHT JOIN +# in a FROM subquery and derived_merge=on +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(8); +SELECT * FROM ( +SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2 +) AS alias; +f1 f2 +NULL 7 +NULL 8 +SELECT * FROM ( +SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2 +) AS alias; +f2 f1 +7 NULL +8 NULL +drop tables t1,t2; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 337af624813..3bed7d5dd93 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4526,6 +4526,29 @@ INSERT INTO v2 (f1, f2) VALUES (1, 2); drop view v4,v3,v2,v1; drop table t1,t2; +--echo # +--echo # MDEV-3799 fix of above bugfix (MDEV-589) +--echo # Wrong result (NULLs instead of real values) with RIGHT JOIN +--echo # in a FROM subquery and derived_merge=on +--echo # + +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); + +CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(8); + +SELECT * FROM ( + SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2 +) AS alias; + +SELECT * FROM ( + SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2 +) AS alias; + +drop tables t1,t2; + + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- diff --git a/sql/table.cc b/sql/table.cc index 0247dc167ee..faa248f43f6 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4462,7 +4462,16 @@ TABLE *TABLE_LIST::get_real_join_table() tbl= (tbl->view != NULL ? tbl->view->select_lex.get_table_list() : tbl->derived->first_select()->get_table_list()); + + /* find left table in outer join on this level */ + while(tbl->outer_join & JOIN_TYPE_RIGHT) + { + DBUG_ASSERT(tbl->next_local); + tbl= tbl->next_local; + } + } + return tbl->table; } |