diff options
| -rw-r--r-- | mysql-test/r/join_nested.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/join_nested.test | 27 | ||||
| -rw-r--r-- | sql/sql_select.cc | 3 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 4 |
4 files changed, 55 insertions, 3 deletions
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 4d952f399e6..ae15ac446e5 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1184,3 +1184,27 @@ a b a1 b 4 2 2 2 5 3 NULL NULL DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +INSERT INTO t3 VALUES (2); +INSERT INTO t1 VALUES (2); +SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a; +a a a +1 NULL NULL +2 2 2 +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; +a a a +1 NULL NULL +2 2 2 +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; +a a a +1 NULL NULL +DELETE FROM t2; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; +a a a +1 NULL NULL +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 8247a12fc56..8fd3958b509 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -700,3 +700,30 @@ SELECT t2.a,t2.b,t3.a1,t3.b WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL); DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int); + +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +INSERT INTO t3 VALUES (2); +INSERT INTO t1 VALUES (2); + +#check proper syntax for nested outer joins + +SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a=t3.a) ON t1.a=t3.a; + +#must be equivalent to: + +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; + +#check that everything is al right when all tables contain not more than 1 row +#(bug #4922) + +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; +DELETE FROM t2; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t2.a=t3.a ON t1.a=t3.a; + +DROP TABLE t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 756b5f3c017..3a98e913abd 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1860,7 +1860,8 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds, if (s->dependent & ~(found_const_table_map)) continue; if (table->file->records <= 1L && - !(table->file->table_flags() & HA_NOT_EXACT_COUNT)) + !(table->file->table_flags() & HA_NOT_EXACT_COUNT) && + !table->pos_in_table_list->embedding) { // system table int tmp= 0; s->type=JT_SYSTEM; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index af6694c6318..f99416cc8c6 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4593,7 +4593,7 @@ join_table: '(' using_list ')' { add_join_on($3,$7); $$=$3; } - | table_ref LEFT opt_outer JOIN_SYM table_factor ON expr + | table_ref LEFT opt_outer JOIN_SYM table_ref ON expr { add_join_on($5,$7); $5->outer_join|=JOIN_TYPE_LEFT; $$=$5; } | table_ref LEFT opt_outer JOIN_SYM table_factor { @@ -4608,7 +4608,7 @@ join_table: $6->outer_join|=JOIN_TYPE_LEFT; $$=$6; } - | table_ref RIGHT opt_outer JOIN_SYM table_factor ON expr + | table_ref RIGHT opt_outer JOIN_SYM table_ref ON expr { LEX *lex= Lex; if (!($$= lex->current_select->convert_right_join())) |