Implementation of WL#2486 -

"Process NATURAL and USING joins according to SQL:2003".

* Some of the main problems fixed by the patch:
  - in "select *" queries the * expanded correctly according to
    ANSI for arbitrary natural/using joins
  - natural/using joins are correctly transformed into JOIN ... ON
    for any number/nesting of the joins.
  - column references are correctly resolved against natural joins
    of any nesting and combined with arbitrary other joins.

* This patch also contains a fix for name resolution of items
  inside the ON condition of JOIN ... ON - in this case items must
  be resolved only against the JOIN operands. To support such
  'local' name resolution, the patch introduces a stack of
  name resolution contexts used at parse time.

NOTICE:
- This patch is not complete in the sense that
  - there are 2 test cases that still do not pass -
    one in join.test, one in select.test. Both are marked
    with a comment "TODO: WL#2486".
  - it does not include a new test specific for the task


mysql-test/include/ps_query.inc:
  Adjusted according to standard NATURAL/USING join semantics.,
mysql-test/r/bdb.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/derived.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/errors.result:
  The column as a whole cannot be resolved, so different error message.
mysql-test/r/fulltext.result:
  Adjusted according to standard JOIN ... ON semantics =>
  the ON condition can refer only to the join operands.
mysql-test/r/fulltext_order_by.result:
  More detailed error message.
mysql-test/r/innodb.result:
  Adjusted according to standard NATURAL/USING join semantics.
  This test doesn't pass completetly yet!
mysql-test/r/insert_select.result:
  More detailed error message.
mysql-test/r/join.result:
  Adjusted according to standard NATURAL/USING join semantics.
  
  NOTICE: there is one test case that still fails, and it is
  commeted out and marked with WL#2486 in the test file.
mysql-test/r/join_crash.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/join_nested.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/join_outer.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/multi_update.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/null_key.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/order_by.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/ps_2myisam.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/ps_3innodb.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/ps_4heap.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/ps_5merge.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/ps_6bdb.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/ps_7ndb.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/select.result:
  Adjusted according to standard NATURAL/USING join semantics.
  
  NOTICE: there is one failing test case which is commented with
  WL#2486 in the test file.
mysql-test/r/subselect.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/type_ranges.result:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/r/union.result:
  More detailed error message.
mysql-test/t/bdb.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/errors.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/fulltext.test:
  Adjusted according to standard JOIN ... ON semantics =>
  the ON condition can refer only to the join operands.
mysql-test/t/fulltext_order_by.test:
  More detailed error message.
mysql-test/t/innodb.test:
  Adjusted according to standard NATURAL/USING join semantics.
  This test doesn't pass completetly yet!
mysql-test/t/insert_select.test:
  More detailed error message.
mysql-test/t/join.test:
  Adjusted according to standard NATURAL/USING join semantics.
  
  NOTICE: there is one test case that still fails, and it is
  commeted out and marked with WL#2486 in the test file.
mysql-test/t/join_crash.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/join_nested.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/join_outer.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/null_key.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/order_by.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/select.test:
  Adjusted according to standard NATURAL/USING join semantics.
  
  NOTICE: there is one test case that still fails, and it is
  commeted out and marked with WL#2486 in the test file.
mysql-test/t/subselect.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/type_ranges.test:
  Adjusted according to standard NATURAL/USING join semantics.
mysql-test/t/union.test:
  More detailed error message.
sql/item.cc:
  - extra parameter to find_field_in_tables
  - find_field_in_real_table renamed to find_field_in_table
  - fixed comments/typos
sql/item.h:
  - added [first | last]_name_resolution_table to class
    Name_resolution_context
  - commented old code
  - standardized formatting
sql/mysql_priv.h:
  - refactored the find_field_in_XXX procedures,
  - added a new procedure for natural join table references,
  - renamed the find_field_in_XXX procedures to clearer names
sql/sp.cc:
  - pass the top-most list of the FROM clause to setup_tables
  - extra parameter to find_field_in_tables
sql/sql_acl.cc:
  - renamed find_field_in_table => find_field_in_table_ref
  - extra parameter to find_field_in_table_ref
  - commented old code
sql/sql_base.cc:
  This file contains the core of the implementation of the processing
  of NATURAL/USING joins (WL#2486).
  - added many comments to old code
  - refactored the group of find_field_in_XXX procedures, and added a
    new procedure for natural joins. There is one find_field_in_XXX procedure
    per each type of table reference (stored table, merge view, or natural
    join); one meta-procedure that selects the correct one depeneding on the
    table reference; and one procedure that goes over a list of table
    referenes.
  - NATURAL/USING joins are processed through the procedures:
      mark_common_columns, store_natural_using_join_columns,
      store_top_level_join_columns, setup_natural_join_row_types.
    The entry point to processing NATURAL/USING joins is the
    procedure 'setup_natural_join_row_types'.
  - Replaced the specialized Field_iterator_XXX iterators with one
    generic iterator over the fields of a table reference.
  - Simplified 'insert_fields' and 'setup_conds' due to encapsulation of
    the processing of natural joins in a separate set of procedures.
sql/sql_class.h:
  - Commented old code.
sql/sql_delete.cc:
  - Pass the FROM clause to setup_tables.
sql/sql_help.cc:
  - pass the end name resolution table to find_field_in_tables
  - adjust the list of tables for name resolution
sql/sql_insert.cc:
  - Changed the code that saves and restores the current context to
    support the list of tables for name resolution -
    context->first_name_resolution_table, and
    table_list->next_name_resolution_table.
    Needed to support an ugly trick to resolve inserted columns only in
    the first table.
  - Added Name_resolution_context::[first | last]_name_resolution_table.
  - Commented old code
sql/sql_lex.cc:
  - set select_lex.parent_lex correctly
  - set correct state of the current name resolution context
sql/sql_lex.h:
  - Added a stack of name resolution contexts to support local
    contexts for JOIN ... ON conditions.
  - Commented old code.
sql/sql_load.cc:
  - Pass the FROM clause to setup_tables.
sql/sql_olap.cc:
  - Pass the FROM clause to setup_tables.
sql/sql_parse.cc:
  - correctly set SELECT_LEX::parent_lex
  - set the first table of the current name resoltion context
  - added support for NATURAL/USING joins
  - commented old code
sql/sql_select.cc:
  - Pass the FROM clause to setup_tables.
  - Pass the end table to find_field_in_tables
  - Improved comments
sql/sql_show.cc:
  - Set SELECT_LEX::parent_lex.
sql/sql_update.cc:
  - Pass the FROM clause to setup_tables.
sql/sql_yacc.yy:
  - Added support for a stack of name resolution contexts needed to
    implement name resolution for JOIN ... ON. A context is pushed
    for each new JOIN ... ON, and popped afterwards.
  - Added support for NATURAL/USING joins.
sql/table.cc:
  - Added new class Natural_join_column to hide the heterogeneous
    representation of column references for stored tables and for
    views.
  - Added a new list TABLE_LIST::next_name_resolution_table to
    support name resolution with NATURAL/USING joins. Also added
    other members to TABLE_LIST to support NATURAL/USING joins.
  - Added a generic iterator over the fields of table references
    of various types - class Field_iterator_table_ref
sql/table.h:
  - Added new class Natural_join_column to hide the heterogeneous
    representation of column references for stored tables and for
    views.
  - Added a new list TABLE_LIST::next_name_resolution_table to
    support name resolution with NATURAL/USING joins. Also added
    other members to TABLE_LIST to support NATURAL/USING joins.
  - Added a generic iterator over the fields of table references
    of various types - class Field_iterator_table_ref
tests/mysql_client_test.c:
  Adjusted according to standard NATURAL JOIN syntax.

41 files changed, 1107 insertions, 1167 deletions

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index 27a86f88231..97653c0b9f8 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -349,14 +349,14 @@ drop table if exists t2 ;
 --enable_warnings
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 let $1= 9 ;
 while ($1)
 {
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index 876da3cb964..a0b68bfbc13 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -732,7 +732,7 @@ drop table t1;
 set @a:=now();
 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=bdb;
 insert into t1 (a) values(1),(2),(3);
-select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
+select a from t1 natural join t1 as t2 where b >= @a order by a;
 a
 1
 2
@@ -906,7 +906,7 @@ create temporary table tmp1
 select branch_id, target_id, platform_id, product_id
 from t1, t2, t3, t4 ;
 create temporary table tmp2 
-select tmp1.branch_id, tmp1.target_id, tmp1.platform_id, tmp1.product_id 
+select branch_id, target_id, platform_id, product_id 
 from tmp1 left join t8 
 using (branch_id,target_id,platform_id,product_id) 
 where t8.archive_id is null ;
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index fd6a834c694..586be94301f 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -149,8 +149,8 @@ SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
 (SELECT * FROM (SELECT 1 as a) as a )
 1
 select * from (select 1 as a) b  left join (select 2 as a) c using(a);
-a	a
-1	NULL
+a
+1
 SELECT * FROM (SELECT 1 UNION SELECT a) b;
 ERROR 42S22: Unknown column 'a' in 'field list'
 SELECT 1 as a FROM (SELECT a UNION SELECT 1) b;
diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result
index d0011c8deb6..6a0791c8ca3 100644
--- a/mysql-test/r/errors.result
+++ b/mysql-test/r/errors.result
@@ -9,9 +9,9 @@ create table t1 (a int);
 select count(test.t1.b) from t1;
 ERROR 42S22: Unknown column 'test.t1.b' in 'field list'
 select count(not_existing_database.t1) from t1;
-ERROR 42S02: Unknown table 'not_existing_database' in field list
+ERROR 42S22: Unknown column 'not_existing_database.t1' in 'field list'
 select count(not_existing_database.t1.a) from t1;
-ERROR 42S02: Unknown table 'not_existing_database.t1' in field list
+ERROR 42S22: Unknown column 'not_existing_database.t1.a' in 'field list'
 select count(not_existing_database.t1.a) from not_existing_database.t1;
 Got one of the listed errors
 select 1 from t1 order by 2;
diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result
index 1a79f6d9736..27b3be39161 100644
--- a/mysql-test/r/fulltext.result
+++ b/mysql-test/r/fulltext.result
@@ -221,14 +221,14 @@ select t1.id FROM t2 as ttxt,t1,t1 as ticket2
 WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and
 match(ttxt.inhalt) against ('foobar');
 id
-select t1.id FROM t2 as ttxt,t1 INNER JOIN t1 as ticket2 ON
-ticket2.id = ttxt.ticket
-WHERE t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
+select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON
+ticket2.id = t2.ticket
+WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
 id
 INSERT INTO t1 VALUES (3,3);
-select t1.id FROM t2 as ttxt,t1
-INNER JOIN t1 as ticket2 ON ticket2.id = ttxt.ticket
-WHERE t1.id = ticket2.ticket and
+select ticket2.id FROM t2 as ttxt,t2
+INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket
+WHERE ticket2.id = ticket2.ticket and
 match(ttxt.inhalt) against ('foobar');
 id
 3
@@ -337,8 +337,8 @@ insert into t2 values (1, 1, 'xxfoo');
 insert into t2 values (2, 1, 'xxbar');
 insert into t2 values (3, 1, 'xxbuz');
 select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode);
-t1_id	name	t2_id	t1_id	name
-1	data1	1	1	xxfoo
+t1_id	name	t2_id	name
+1	data1	1	xxfoo
 select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
 t2_id	t1_id	name
 drop table t1,t2;
diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result
index 3b52be4b1f2..e80a4daefe8 100644
--- a/mysql-test/r/fulltext_order_by.result
+++ b/mysql-test/r/fulltext_order_by.result
@@ -126,7 +126,7 @@ group by
 a.text, b.id, b.betreff
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S02: Unknown table 'b' in order clause
+ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join
@@ -142,7 +142,7 @@ where
 match(c.beitrag) against ('+abc' in boolean mode)
 order by 
 match(b.betreff) against ('+abc' in boolean mode) desc;
-ERROR 42S02: Unknown table 'b' in order clause
+ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
 select a.text, b.id, b.betreff
 from 
 t2 a inner join t3 b on a.id = b.forum inner join
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 2bdec5125dd..ffdc3f1ca59 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -750,7 +750,7 @@ drop table t1;
 set @a:=now();
 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
 insert into t1 (a) values(1),(2),(3);
-select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
+select a from t1 natural join t1 as t2 where b >= @a order by a;
 a
 1
 2
@@ -1377,7 +1377,7 @@ ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fail
 update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
 update t3 set  t3.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
-ERROR 42S02: Unknown table 't1' in where clause
+ERROR 42S22: Unknown column 't1.id' in 'where clause'
 drop table t3,t2,t1;
 create table t1(
 id int primary key,
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index f745af182eb..17f65d96abc 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -664,7 +664,7 @@ insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a +
 insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
 ERROR 23000: Column 'a' in field list is ambiguous
 insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
-ERROR 42S02: Unknown table 't2' in field list
+ERROR 42S22: Unknown column 't2.a' in 'field list'
 insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
-ERROR 42S02: Unknown table 't2' in field list
+ERROR 42S22: Unknown column 't2.b' in 'field list'
 drop table t1,t2,t3;
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index dc763472b0e..5ea863b4bdb 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -10,21 +10,21 @@ SELECT * FROM t1 INNER JOIN t2;
 S1	S1
 1	2
 SELECT * from t1 JOIN t2 USING (S1);
-S1	S1
+S1
 SELECT * FROM t1 INNER JOIN t2 USING (S1);
-S1	S1
+S1
 SELECT * from t1 CROSS JOIN t2;
 S1	S1
 1	2
 SELECT * from t1 LEFT JOIN t2 USING(S1);
-S1	S1
-1	NULL
+S1
+1
 SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2);
 S1	S1
 1	2
 SELECT * from t1 RIGHT JOIN t2 USING(S1);
-S1	S1
-NULL	2
+S1
+2
 SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1);
 S1	S1
 1	2
@@ -121,11 +121,11 @@ id	catid	stateid	countyid
 drop table t1, t2;
 create table t1 (a int primary key);
 insert into t1 values(1),(2);
-select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
+select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
 a
 1
 2
-select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
+select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
 ERROR HY000: Too many tables; MySQL can only use XX tables in a join
 drop table t1;
 CREATE TABLE t1 (
@@ -145,10 +145,9 @@ DROP TABLE t1, t2;
 CREATE TABLE t1 (d DATE NOT NULL);
 CREATE TABLE t2 (d DATE NOT NULL);
 INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');
-SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
-d	d
-2001-08-01	NULL
-0000-00-00	NULL
+SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
+d
+0000-00-00
 SELECT * from t1 WHERE t1.d IS NULL;
 d
 0000-00-00
@@ -265,10 +264,10 @@ PRIMARY KEY  (siteid,rate_code),
 FULLTEXT KEY rate_code (rate_code)
 ) ENGINE=MyISAM;
 INSERT INTO t2 VALUES ('rivercats','cust',20);
-SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
+SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
 rate_code	base_rate
 cust	20
-SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
+SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
 rate_code	base_rate
 cust	20
 drop table t1,t2;
@@ -296,79 +295,79 @@ insert into t1 values(1),(2);
 insert into t2 values(2),(3);
 insert into t3 values (2),(4);
 select * from t1 natural left join t2;
-i	i
-1	NULL
-2	2
+i
+1
+2
 select * from t1 left join t2 on (t1.i=t2.i);
 i	i
 1	NULL
 2	2
 select * from t1 natural left join t2 natural left join t3;
-i	i	i
-1	NULL	NULL
-2	2	2
+i
+1
+2
 select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
 i	i	i
 1	NULL	NULL
 2	2	2
 select * from t3 natural right join t2;
-i	i
-2	2
-NULL	3
+i
+2
+3
 select * from t3 right join t2 on (t3.i=t2.i);
 i	i
 2	2
 NULL	3
 select * from t3 natural right join t2 natural right join t1;
-i	i	i
-NULL	NULL	1
-2	2	2
+i
+1
+2
 select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
 i	i	i
 NULL	NULL	1
 2	2	2
-select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
-i	i	i
-1	2	2
-1	3	NULL
-2	2	2
-2	3	NULL
+select * from t1,t2 natural left join t3 order by 1,2;
+i	i
+1	2
+1	3
+2	2
+2	3
 select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
 i	i	i
 1	2	2
 1	3	NULL
 2	2	2
 2	3	NULL
-select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
-i	i	i
-1	2	2
-1	3	NULL
-2	2	2
-2	3	NULL
+select * from t2 natural left join t3,t1 order by t1.i;
+i	i
+2	1
+3	1
+2	2
+3	2
 select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 i	i	i
 1	2	2
 1	3	NULL
 2	2	2
 2	3	NULL
-select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
-i	i	i
-1	NULL	4
-1	2	2
-2	NULL	4
-2	2	2
+select * from t1,t2 natural right join t3 order by 1,2;
+i	i
+1	2
+1	4
+2	2
+2	4
 select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
 i	i	i
 1	NULL	4
 1	2	2
 2	NULL	4
 2	2	2
-select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
-i	i	i
-1	NULL	4
-1	2	2
-2	NULL	4
-2	2	2
+select * from t2 natural right join t3,t1 order by t1.i;
+i	i
+2	1
+4	1
+2	2
+4	2
 select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 i	i	i
 1	NULL	4
diff --git a/mysql-test/r/join_crash.result b/mysql-test/r/join_crash.result
index c1671ea7e20..f1a3b4956a8 100644
--- a/mysql-test/r/join_crash.result
+++ b/mysql-test/r/join_crash.result
@@ -75,18 +75,11 @@ t1.client_ptr as client_ptr,
 t1.comments as comments,
 sum( t3.amount_received ) + sum( t3.adjustment ) as total_budget
 from
-t1 ,
 t2 as client_period ,
-t2 as project_period
-left join
-t3
-on
-t3.project_ptr = t1.project_id
-and t3.date_received <= '2001-03-22 14:15:09'
-    left join
-t4
-on
-t4.client_id = t1.client_ptr
+t2 as project_period,
+t3 left join t1 on (t3.project_ptr = t1.project_id and
+t3.date_received <= '2001-03-22 14:15:09')
+left join t4 on t4.client_id = t1.client_ptr
 where
 1
 and ( client_period.period_type = 'client_table'
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 27edac1b30b..8bc2b1843b2 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -223,8 +223,7 @@ a	b
 1	2
 EXPLAIN EXTENDED
 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
-FROM t6,
-t7 
+FROM (t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10;
@@ -235,8 +234,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 Warnings:
 Note	1003	select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1
 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
-FROM t6,
-t7 
+FROM (t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10;
@@ -260,8 +258,7 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -279,8 +276,7 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -312,8 +308,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -357,8 +352,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -397,8 +391,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -470,8 +463,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -537,8 +529,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -575,8 +566,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -625,8 +615,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -679,8 +668,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -825,44 +813,38 @@ a	b	a	b	a	b	a	b
 1	3	5	3	NULL	NULL	NULL	NULL
 2	2	5	3	NULL	NULL	NULL	NULL
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-FROM t1, t3, t4 
+FROM t1, (t3, t4)
 RIGHT JOIN              
 t2
 ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 a	b	a	b	a	b	a	b
-1	3	3	3	1	2	NULL	NULL
-1	3	3	3	2	2	NULL	NULL
-2	2	3	3	1	2	NULL	NULL
-2	2	3	3	2	2	NULL	NULL
+1	3	3	3	NULL	NULL	NULL	NULL
+2	2	3	3	NULL	NULL	NULL	NULL
 1	3	4	2	1	2	3	2
 1	3	4	2	1	2	4	2
-1	3	4	2	2	2	NULL	NULL
 2	2	4	2	1	2	3	2
 2	2	4	2	1	2	4	2
-2	2	4	2	2	2	NULL	NULL
-1	3	5	3	1	2	NULL	NULL
-1	3	5	3	2	2	NULL	NULL
-2	2	5	3	1	2	NULL	NULL
-2	2	5	3	2	2	NULL	NULL
+1	3	5	3	NULL	NULL	NULL	NULL
+2	2	5	3	NULL	NULL	NULL	NULL
 EXPLAIN EXTENDED
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-FROM t1, t3, t4
+FROM t1, (t3, t4)
 RIGHT JOIN
 t2
 ON t3.a=1 AND t2.b=t4.b
 WHERE t1.a <= 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t3` join `test`.`t2` left join `test`.`t4` on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.`a` <= 2)
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.`a` <= 2)
 CREATE INDEX idx_b ON t2(b);
 EXPLAIN EXTENDED
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-FROM t3,t4 
+FROM (t3,t4)
 LEFT JOIN              
 (t1,t2)
 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
@@ -874,7 +856,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-FROM t3,t4 
+FROM (t3,t4)
 LEFT JOIN              
 (t1,t2)
 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
@@ -900,8 +882,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -951,8 +932,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -1001,8 +981,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -1052,8 +1031,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -1100,8 +1078,7 @@ ON t3.a=1 AND t2.b=t4.b,
 t5 
 LEFT JOIN 
 ( 
-t6,
-t7 
+(t6, t7)
 LEFT JOIN 
 t8
 ON t7.b=t8.b AND t6.b < 10
@@ -1176,13 +1153,13 @@ a	b	a1	b
 4	2	1	2
 4	2	2	2
 5	3	NULL	NULL
-SELECT t2.a,t2.b,t3.a1,t3.b
+SELECT *
 FROM t2 NATURAL LEFT JOIN t3
 WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
-a	b	a1	b
-4	2	1	2
-4	2	2	2
-5	3	NULL	NULL
+b	a	c	a1	c1
+2	4	0	1	0
+2	4	0	2	0
+3	5	0	NULL	NULL
 DROP TABLE t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
 CREATE TABLE t1 (a int);
 CREATE TABLE t2 (a int);
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index b75eadd5291..9bc85dfa987 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -36,7 +36,16 @@ grp	a	c	id	a	c	d
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
 NULL	NULL	NULL	4	7	D	7
-select t1.*,t2.* from t1 left join t2 using (a);
+select * from t1 left join t2 using (a);
+a	grp	c	id	c	d
+1	1	a	1	a	1
+2	2	b	NULL	NULL	NULL
+3	2	c	NULL	NULL	NULL
+4	3	E	3	A	4
+5	3	C	3	B	5
+6	3	D	3	C	6
+NULL	NULL		NULL	NULL	NULL
+select t1.*,t2.* from t1 left join t2 on t1.a=t2.a;
 grp	a	c	id	a	c	d
 1	1	a	1	1	a	1
 2	2	b	NULL	NULL	NULL	NULL
@@ -45,40 +54,34 @@ grp	a	c	id	a	c	d
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
 NULL	NULL		NULL	NULL	NULL	NULL
-select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
-grp	a	c	id	a	c	d
-1	1	a	1	1	a	1
-3	4	E	3	4	A	4
-3	5	C	3	5	B	5
-3	6	D	3	6	C	6
-select t1.*,t2.* from t1 left join t2 using (a,c);
-grp	a	c	id	a	c	d
-1	1	a	1	1	a	1
-2	2	b	NULL	NULL	NULL	NULL
-2	3	c	NULL	NULL	NULL	NULL
-3	4	E	NULL	NULL	NULL	NULL
-3	5	C	NULL	NULL	NULL	NULL
-3	6	D	NULL	NULL	NULL	NULL
-NULL	NULL		NULL	NULL	NULL	NULL
-select t1.*,t2.* from t1 left join t2 using (c);
-grp	a	c	id	a	c	d
-1	1	a	1	1	a	1
-1	1	a	3	4	A	4
-2	2	b	3	5	B	5
-2	3	c	3	6	C	6
-3	4	E	NULL	NULL	NULL	NULL
-3	5	C	3	6	C	6
-3	6	D	4	7	D	7
-NULL	NULL		NULL	NULL	NULL	NULL
-select t1.*,t2.* from t1 natural left outer join t2;
-grp	a	c	id	a	c	d
-1	1	a	1	1	a	1
-2	2	b	NULL	NULL	NULL	NULL
-2	3	c	NULL	NULL	NULL	NULL
-3	4	E	NULL	NULL	NULL	NULL
-3	5	C	NULL	NULL	NULL	NULL
-3	6	D	NULL	NULL	NULL	NULL
-NULL	NULL		NULL	NULL	NULL	NULL
+select * from t1 left join t2 using (a,c);
+a	c	grp	id	d
+1	a	1	1	1
+2	b	2	NULL	NULL
+3	c	2	NULL	NULL
+4	E	3	NULL	NULL
+5	C	3	NULL	NULL
+6	D	3	NULL	NULL
+NULL		NULL	NULL	NULL
+select * from t1 left join t2 using (c);
+c	grp	a	id	a	d
+a	1	1	1	1	1
+a	1	1	3	4	4
+b	2	2	3	5	5
+c	2	3	3	6	6
+E	3	4	NULL	NULL	NULL
+C	3	5	3	6	6
+D	3	6	4	7	7
+	NULL	NULL	NULL	NULL	NULL
+select * from t1 natural left outer join t2;
+a	c	grp	id	d
+1	a	1	1	1
+2	b	2	NULL	NULL
+3	c	2	NULL	NULL
+4	E	3	NULL	NULL
+5	C	3	NULL	NULL
+6	D	3	NULL	NULL
+NULL		NULL	NULL	NULL
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
 grp	a	c	id	a	c	d
 3	4	E	3	4	A	4
@@ -106,26 +109,26 @@ grp	a	c	id	a	c	d	a
 3	6	D	3	6	C	6	6
 NULL	NULL		NULL	NULL	NULL	NULL	NULL
 explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
-ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
+ERROR 42S22: Unknown column 't3.a' in 'on clause'
 select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
-ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
+ERROR 42S22: Unknown column 't3.a' in 'on clause'
 select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
-ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
-select t1.*,t2.* from t1 inner join t2 using (a);
-grp	a	c	id	a	c	d
-1	1	a	1	1	a	1
-3	4	E	3	4	A	4
-3	5	C	3	5	B	5
-3	6	D	3	6	C	6
+ERROR 42S22: Unknown column 't3.a' in 'on clause'
+select * from t1 inner join t2 using (a);
+a	grp	c	id	c	d
+1	1	a	1	a	1
+4	3	E	3	A	4
+5	3	C	3	B	5
+6	3	D	3	C	6
 select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
 grp	a	c	id	a	c	d
 1	1	a	1	1	a	1
 3	4	E	3	4	A	4
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
-select t1.*,t2.* from t1 natural join t2;
-grp	a	c	id	d
-1	1	a	1	1
+select * from t1 natural join t2;
+a	c	grp	id	d
+1	a	1	1	1
 drop table t1,t2;
 CREATE TABLE t1 (
 usr_id INT unsigned NOT NULL,
@@ -400,7 +403,7 @@ insert into t3 values (1);
 insert into t4 values (1,1);
 insert into t5 values (1,1);
 explain select * from t3 left join t4 on t4.seq_1_id = t2.t2_id left join t1 on t1.t1_id = t4.seq_0_id left join t5 on t5.seq_0_id = t1.t1_id left join t2 on t2.t2_id = t5.seq_1_id where t3.t3_id = 23;
-ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
+ERROR 42S22: Unknown column 't2.t2_id' in 'on clause'
 drop table t1,t2,t3,t4,t5;
 create table t1 (n int, m int, o int, key(n));
 create table t2 (n int not null, m int, o int, primary key(n));
@@ -432,7 +435,7 @@ INSERT INTO t2 VALUES (3,'z');
 SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
 id2
 3
-SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
+SELECT id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
 id2
 3
 drop table t1,t2;
@@ -461,10 +464,10 @@ count	color
 15	white
 7	green
 select * from t2 natural join t1;
-count	color	name
-10	green	lime
-7	green	lime
-5	black	grape
+color	count	name
+green	10	lime
+green	7	lime
+black	5	grape
 select t2.count, t1.name from t2 natural join t1;
 count	name
 10	lime
@@ -647,16 +650,15 @@ insert into t1 values(1),(2);
 insert into t2 values(2),(3);
 insert into t3 values(2),(4);
 select * from t1 natural left join t2 natural left join t3;
-i	i	i
-1	NULL	NULL
-2	2	2
-select * from t1 natural left join t2 where (t2.i is not null)=0;
-i	i
-1	NULL
-select * from t1 natural left join t2 where (t2.i is not null) is not null;
-i	i
-1	NULL
-2	2
+i
+1
+2
+select * from t1 natural left join t2 where (i is not null)=0;
+i
+select * from t1 natural left join t2 where (i is not null) is not null;
+i
+1
+2
 drop table t1,t2,t3;
 create table t1 (f1 integer,f2 integer,f3 integer);
 create table t2 (f2 integer,f4 integer);
@@ -664,7 +666,7 @@ create table t3 (f3 integer,f5 integer);
 select * from t1
 left outer join t2 using (f2)
 left outer join t3 using (f3);
-ERROR 42S22: Unknown column 'test.t2.f3' in 'on clause'
+f3	f2	f1	f4	f5
 drop table t1,t2,t3;
 create table t1 (a1 int, a2 int);
 create table t2 (b1 int not null, b2 int);
@@ -929,13 +931,13 @@ create table t1 (a int, b varchar(20));
 create table t2 (a int, c varchar(20));
 insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
 insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
-select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
+select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
 group_concat(t1.b,t2.c)
 aaaaa
 bbbbb
 Warnings:
 Warning	1260	2 line(s) were cut by GROUP_CONCAT()
-select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
+select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
 group_concat(t1.b,t2.c)
 aaaaa
 bbbbb
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index f5c4e19af64..ea02a703c65 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -135,16 +135,16 @@ create table t1 (n numeric(10));
 create table t2 (n numeric(10));
 insert into t2 values (1),(2),(4),(8),(16),(32);
 select * from t2 left outer join t1  using (n);
-n	n
-1	NULL
-2	NULL
-4	NULL
-8	NULL
-16	NULL
-32	NULL
+n
+1
+2
+4
+8
+16
+32
 delete  t1,t2 from t2 left outer join t1  using (n);
 select * from t2 left outer join t1  using (n);
-n	n
+n
 drop table t1,t2 ;
 create table t1 (n int(10) not null primary key, d int(10));
 create table t2 (n int(10) not null primary key, d int(10));
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 7f746a3dbd8..5c2141befa6 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -313,12 +313,16 @@ INSERT INTO t1 (order_id, product_id, product_type) VALUES
 ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
 INSERT INTO t2 (order_id, product_id, product_type) VALUES
 ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
-select t1.* from t1
-left join t2 using(order_id, product_id, product_type)
-where t2.order_id=NULL;
+select t1.* from t1 left join t2
+on (t1.order_id = t2.order_id and
+t1.product_id = t2.product_id and
+t1.product_type = t2.product_type)
+where t2.order_id = NULL;
 order_id	product_id	product_type
-select t1.* from t1
-left join t2 using(order_id, product_id, product_type)
+select t1.* from t1 left join t2
+on (t1.order_id = t2.order_id and
+t1.product_id = t2.product_id and
+t1.product_type = t2.product_type)
 where t2.order_id is NULL;
 order_id	product_id	product_type
 3d7ce39b5d4b3e3d22aaafe9b633de51	1206029	3
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 628ca5fd958..b766f146254 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -180,7 +180,7 @@ INSERT INTO t3 VALUES (1,'123 Park Place');
 INSERT INTO t3 VALUES (2,'453 Boardwalk');
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
-LEFT JOIN t2 USING(c)
+LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c;
 a	b	if(b = 1,i,if(b = 2,v,''))
 1	1	50
@@ -189,7 +189,7 @@ a	b	if(b = 1,i,if(b = 2,v,''))
 4	2	453 Boardwalk
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
-LEFT JOIN t2 USING(c)
+LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c
 ORDER BY a;
 a	b	if(b = 1,i,if(b = 2,v,''))
@@ -523,10 +523,10 @@ KEY `pseudo` (`pseudo`)
 );
 INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
 INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
-SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+SELECT titre,numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
 titre	numeropost	auteur	icone	nbrep	0	date	vue	ouvert	lastauteur	dest
 test	1	joce	0	0	0	0000-00-00 00:00:00	0	1		bug
-SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+SELECT titre,numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
 titre	numeropost	auteur	icone	nbrep	0	date	vue	ouvert	lastauteur	dest
 test	1	joce	0	0	0	0000-00-00 00:00:00	0	1		bug
 drop table t1,t2;
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 5d0e1d703a6..da5466a6e9d 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -519,56 +519,56 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by t2.a 
+SELECT * FROM t2 right join t1 using(a) order by a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by t2.a 
+SELECT * FROM t2 natural right join t1 order by a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query7 ;
@@ -591,47 +591,47 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by t2.a 
+SELECT * FROM t2 left join t1 using(a) order by a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by t2.a 
+SELECT * FROM t2 natural left join t1 order by a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query4 ;
@@ -654,28 +654,28 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by t2.a 
+SELECT * FROM t2 join t1 using(a) order by a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by t2.a 
+SELECT * FROM t2 natural join t1 order by a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index da0421d2caa..237072d7218 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -519,56 +519,56 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by t2.a 
+SELECT * FROM t2 right join t1 using(a) order by a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by t2.a 
+SELECT * FROM t2 natural right join t1 order by a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query7 ;
@@ -591,47 +591,47 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by t2.a 
+SELECT * FROM t2 left join t1 using(a) order by a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by t2.a 
+SELECT * FROM t2 natural left join t1 order by a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query4 ;
@@ -654,28 +654,28 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by t2.a 
+SELECT * FROM t2 join t1 using(a) order by a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by t2.a 
+SELECT * FROM t2 natural join t1 order by a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index ff52847a0dc..9b76003900a 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -520,56 +520,56 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by t2.a 
+SELECT * FROM t2 right join t1 using(a) order by a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by t2.a 
+SELECT * FROM t2 natural right join t1 order by a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query7 ;
@@ -592,47 +592,47 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by t2.a 
+SELECT * FROM t2 left join t1 using(a) order by a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by t2.a 
+SELECT * FROM t2 natural left join t1 order by a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query4 ;
@@ -655,28 +655,28 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by t2.a 
+SELECT * FROM t2 join t1 using(a) order by a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by t2.a 
+SELECT * FROM t2 natural join t1 order by a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 9af8f6ed6ce..adb300fdcf1 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -562,56 +562,56 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by t2.a 
+SELECT * FROM t2 right join t1 using(a) order by a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by t2.a 
+SELECT * FROM t2 natural right join t1 order by a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query7 ;
@@ -634,47 +634,47 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by t2.a 
+SELECT * FROM t2 left join t1 using(a) order by a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by t2.a 
+SELECT * FROM t2 natural left join t1 order by a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query4 ;
@@ -697,28 +697,28 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by t2.a 
+SELECT * FROM t2 join t1 using(a) order by a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by t2.a 
+SELECT * FROM t2 natural join t1 order by a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
@@ -3574,56 +3574,56 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by t2.a 
+SELECT * FROM t2 right join t1 using(a) order by a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by t2.a 
+SELECT * FROM t2 natural right join t1 order by a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query7 ;
@@ -3646,47 +3646,47 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by t2.a 
+SELECT * FROM t2 left join t1 using(a) order by a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by t2.a 
+SELECT * FROM t2 natural left join t1 order by a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query4 ;
@@ -3709,28 +3709,28 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by t2.a 
+SELECT * FROM t2 join t1 using(a) order by a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by t2.a 
+SELECT * FROM t2 natural join t1 order by a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index b7d63b97c09..d5a15fb4265 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -519,56 +519,56 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by t2.a 
+SELECT * FROM t2 right join t1 using(a) order by a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by t2.a 
+SELECT * FROM t2 natural right join t1 order by a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query7 ;
@@ -591,47 +591,47 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by t2.a 
+SELECT * FROM t2 left join t1 using(a) order by a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by t2.a 
+SELECT * FROM t2 natural left join t1 order by a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query4 ;
@@ -654,28 +654,28 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by t2.a 
+SELECT * FROM t2 join t1 using(a) order by a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by t2.a 
+SELECT * FROM t2 natural join t1 order by a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index c54c09cf6aa..33769f400ae 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -519,56 +519,56 @@ a	?	a
 drop table if exists t2 ;
 create table t2 as select * from t1 ;
 set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by a ' ;
 set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by a ' ;
 set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by a ' ;
 the join statement is:
-SELECT * FROM t2 right join t1 using(a) order by t2.a 
+SELECT * FROM t2 right join t1 using(a) order by a 
 prepare stmt1 from @query9  ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural right join t1 order by t2.a 
+SELECT * FROM t2 natural right join t1 order by a 
 prepare stmt1 from @query8 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query7 ;
@@ -591,47 +591,47 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 left join t1 using(a) order by t2.a 
+SELECT * FROM t2 left join t1 using(a) order by a 
 prepare stmt1 from @query6 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural left join t1 order by t2.a 
+SELECT * FROM t2 natural left join t1 order by a 
 prepare stmt1 from @query5 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b
+1	one
+2	two
+3	three
+4	four
 the join statement is:
 SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a 
 prepare stmt1 from @query4 ;
@@ -654,28 +654,28 @@ a	b	a	b
 3	three	3	three
 4	four	4	four
 the join statement is:
-SELECT * FROM t2 join t1 using(a) order by t2.a 
+SELECT * FROM t2 join t1 using(a) order by a 
 prepare stmt1 from @query3 ;
 execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
-execute stmt1 ;
-a	b	a	b
-1	one	1	one
-2	two	2	two
-3	three	3	three
-4	four	4	four
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
+execute stmt1 ;
+a	b	b
+1	one	one
+2	two	two
+3	three	three
+4	four	four
 the join statement is:
-SELECT * FROM t2 natural join t1 order by t2.a 
+SELECT * FROM t2 natural join t1 order by a 
 prepare stmt1 from @query2 ;
 execute stmt1 ;
 a	b
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index a0f6bb7084f..d3409bf8d39 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -1350,45 +1350,42 @@ fld1	fld1
 250504	250505
 250505	250505
 insert into t2 (fld1, companynr) values (999999,99);
-select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
 companynr	companyname
-99	NULL
-select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
+select count(*) from t2 left join t4 using (companynr) where companynr is not null;
 count(*)
-1199
-explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
+1200
+explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	Using where; Not exists
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	Using where; Not exists
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 delete from t2 where fld1=999999;
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	1	test.t2.companynr	1	
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
+explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	
 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 companynr	companynr
 37	36
@@ -2115,159 +2112,142 @@ insert into t1 values ();
 insert into t1 values ();
 insert into t1 values ();
 select * from (t1 as t2 left join t1 as t3 using (a)), t1;
-a	a	a
-1	1	1
-2	2	1
-3	3	1
-1	1	2
-2	2	2
-3	3	2
-1	1	3
-2	2	3
-3	3	3
+a	a
+1	1
+2	1
+3	1
+1	2
+2	2
+3	2
+1	3
+2	3
+3	3
 select * from t1, (t1 as t2 left join t1 as t3 using (a));
-a	a	a
-1	1	1
-2	1	1
-3	1	1
-1	2	2
-2	2	2
-3	2	2
-1	3	3
-2	3	3
-3	3	3
+a	a
+1	1
+2	1
+3	1
+1	2
+2	2
+3	2
+1	3
+2	3
+3	3
 select * from (t1 as t2 left join t1 as t3 using (a)) straight_join t1;
-a	a	a
-1	1	1
-2	2	1
-3	3	1
-1	1	2
-2	2	2
-3	3	2
-1	1	3
-2	2	3
-3	3	3
+a	a
+1	1
+2	1
+3	1
+1	2
+2	2
+3	2
+1	3
+2	3
+3	3
 select * from t1 straight_join (t1 as t2 left join t1 as t3 using (a));
-a	a	a
-1	1	1
-2	1	1
-3	1	1
-1	2	2
-2	2	2
-3	2	2
-1	3	3
-2	3	3
-3	3	3
+a	a
+1	1
+2	1
+3	1
+1	2
+2	2
+3	2
+1	3
+2	3
+3	3
 select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
-a	a	a
-1	1	2
-2	2	2
-3	3	2
-1	1	3
-2	2	3
-3	3	3
+a	a
+1	2
+2	2
+3	2
+1	3
+2	3
+3	3
 select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
-a	a	a
-2	1	1
-3	1	1
-2	2	2
-3	2	2
-2	3	3
-3	3	3
+a	a
+2	1
+3	1
+2	2
+3	2
+2	3
+3	3
 select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 using ( a );
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) using ( a );
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
-a	a	a
-1	1	2
-1	1	3
-2	2	2
-2	2	3
-3	3	2
-3	3	3
+a	a
+1	2
+1	3
+2	2
+2	3
+3	2
+3	3
 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
-a	a	a
-1	NULL	NULL
-2	1	1
-2	2	2
-2	3	3
-3	1	1
-3	2	2
-3	3	3
+a	a
+1	NULL
+2	1
+2	2
+2	3
+3	1
+3	2
+3	3
 select * from (t1 as t2 left join t1 as t3 using (a)) left join t1 using ( a );
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
-a	a	a
-1	1	1
-2	2	2
-3	3	3
-select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
-a	a	a
-NULL	NULL	1
-1	1	2
-2	2	2
-3	3	2
-1	1	3
-2	2	3
-3	3	3
-select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
-a	a	a
-2	1	1
-3	1	1
-2	2	2
-3	2	2
-2	3	3
-3	3	3
+a
+1
+2
+3
 select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from (t1 as t2 left join t1 as t3 using (a)) natural right join t1;
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from t1 natural right join (t1 as t2 left join t1 as t3 using (a));
-a	a	a
-1	1	1
-2	2	2
-3	3	3
+a
+1
+2
+3
 select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
-a	a
-1	1
-2	2
-3	3
+a
+1
+2
+3
 select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
-a	a
-1	1
-2	2
-3	3
+a
+1
+2
+3
 drop table t1;
 CREATE TABLE t1 (  aa char(2),  id int(11) NOT NULL auto_increment,  t2_id int(11) NOT NULL default '0',  PRIMARY KEY  (id),  KEY replace_id (t2_id)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 693146c869e..ab44305818c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1200,7 +1200,7 @@ INSERT INTO t1 VALUES (1);
 UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
 UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
 UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
-ERROR 42S02: Unknown table 't' in field list
+ERROR 42S22: Unknown column 't.i' in 'field list'
 select * from t1;
 i
 1
@@ -1638,7 +1638,7 @@ ERROR 42S22: Unknown column 't1.s2' in 'where clause'
 select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
 ERROR 42S22: Unknown column 't1.s2' in 'group statement'
 select count(*) from t2 group by t1.s2;
-ERROR 42S02: Unknown table 't1' in group statement
+ERROR 42S22: Unknown column 't1.s2' in 'group statement'
 drop table t1, t2;
 CREATE TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB));
 CREATE TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA));
@@ -1789,11 +1789,11 @@ CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
 INSERT INTO t1 VALUES (1), (2);
 INSERT INTO t2 VALUES (1);
-SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
+SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
 id	c
 1	1
 2	0
-SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
+SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id;
 id	c
 1	1
 2	0
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index 3da8d76e0f2..39c55206c60 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -307,18 +307,18 @@ create table t1 ( id integer unsigned not null primary key );
 create table t2 ( id integer unsigned not null primary key );
 insert into t1 values (1), (2);
 insert into t2 values (1);
-select  t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
+select  t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
 id_A	id_B
 1	1
 2	NULL
 create table t3 (id_A integer unsigned not null, id_B integer unsigned null  );
-insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
+insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
 select * from t3;
 id_A	id_B
 1	1
 2	NULL
 drop table t3;
-create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
+create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
 select * from t3;
 id_A	id_B
 1	1
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 1f45d238ea5..eb129e32983 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -430,7 +430,7 @@ drop temporary table t1;
 create table t1 select a from t1 union select a from t2;
 ERROR HY000: You can't specify target table 't1' for update in FROM clause
 select a from t1 union select a from t2 order by t2.a;
-ERROR 42S02: Unknown table 't2' in order clause
+ERROR 42S22: Unknown column 't2.a' in 'order clause'
 drop table t1,t2;
 select length(version()) > 1 as `*` UNION select 2;
 *
diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test
index c1abe011be4..5e487bd8036 100644
--- a/mysql-test/t/bdb.test
+++ b/mysql-test/t/bdb.test
@@ -400,7 +400,7 @@ drop table t1;
 set @a:=now();
 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=bdb;
 insert into t1 (a) values(1),(2),(3);
-select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
+select a from t1 natural join t1 as t2 where b >= @a order by a;
 update t1 set a=5 where a=1;
 select a from t1;
 drop table t1;
@@ -571,7 +571,7 @@ create temporary table tmp1
         select branch_id, target_id, platform_id, product_id
         from t1, t2, t3, t4 ;
 create temporary table tmp2 
-        select tmp1.branch_id, tmp1.target_id, tmp1.platform_id, tmp1.product_id 
+        select branch_id, target_id, platform_id, product_id 
         from tmp1 left join t8 
         using (branch_id,target_id,platform_id,product_id) 
         where t8.archive_id is null ;
diff --git a/mysql-test/t/errors.test b/mysql-test/t/errors.test
index 93668ffdd3d..7f41a1621d3 100644
--- a/mysql-test/t/errors.test
+++ b/mysql-test/t/errors.test
@@ -14,9 +14,9 @@ update t1 set a=1;
 create table t1 (a int);
 --error 1054
 select count(test.t1.b) from t1;
---error 1109
+--error 1054
 select count(not_existing_database.t1) from t1;
---error 1109
+--error 1054
 select count(not_existing_database.t1.a) from t1;
 --error 1044,1146
 select count(not_existing_database.t1.a) from not_existing_database.t1;
diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test
index b0b70d40e5c..3b2fd3fadff 100644
--- a/mysql-test/t/fulltext.test
+++ b/mysql-test/t/fulltext.test
@@ -128,14 +128,14 @@ WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and
 match(ttxt.inhalt) against ('foobar');
 
 # In the following query MySQL didn't use the fulltext index
-select t1.id FROM t2 as ttxt,t1 INNER JOIN t1 as ticket2 ON
-ticket2.id = ttxt.ticket
-WHERE t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
+select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON
+ticket2.id = t2.ticket
+WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
 
 INSERT INTO t1 VALUES (3,3);
-select t1.id FROM t2 as ttxt,t1
-INNER JOIN t1 as ticket2 ON ticket2.id = ttxt.ticket
-WHERE t1.id = ticket2.ticket and
+select ticket2.id FROM t2 as ttxt,t2
+INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket
+WHERE ticket2.id = ticket2.ticket and
       match(ttxt.inhalt) against ('foobar');
 
 # Check that we get 'fulltext' index in SHOW CREATE
diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test
index a0bd7954674..98c393fede4 100644
--- a/mysql-test/t/fulltext_order_by.test
+++ b/mysql-test/t/fulltext_order_by.test
@@ -80,7 +80,7 @@ CREATE TABLE t3 (
   FULLTEXT KEY betreff (betreff)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
 
---error 1109
+--error 1054
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
@@ -100,7 +100,7 @@ group by
 order by 
   match(b.betreff) against ('+abc' in boolean mode) desc;
   
---error 1109
+--error 1054
 select a.text, b.id, b.betreff
 from 
   t2 a inner join t3 b on a.id = b.forum inner join
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 05f47f36e42..910a7b9b4de 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -440,7 +440,7 @@ drop table t1;
 set @a:=now();
 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
 insert into t1 (a) values(1),(2),(3);
-select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
+select a from t1 natural join t1 as t2 where b >= @a order by a;
 update t1 set a=5 where a=1;
 select a from t1;
 drop table t1;
@@ -981,7 +981,7 @@ insert into `t3`values ( 1 ) ;
 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
 --error 1217
 update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
---error 1109
+--error 1054
 update t3 set  t3.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
 drop table t3,t2,t1;
 
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 4a463c1d52e..7116a25ff39 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -196,9 +196,9 @@ insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a +
 #Some error cases
 --error 1052
 insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
---error 1109
+--error 1054
 insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
---error 1109
+--error 1054
 insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
 drop table t1,t2,t3;
 
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 2715f30b6cf..06e89e6cb9d 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -111,10 +111,12 @@ drop table t1, t2;
 
 create table t1 (a int primary key);
 insert into t1 values(1),(2);
-select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
+# TODO: WL#2486 - the query fails in PS mode with error:
+#       Cross dependency found in OUTER JOIN; examine your ON conditions
+#select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a);
 --replace_result "31 tables" "XX tables" "61 tables" "XX tables"
 --error 1116
-select t1.a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
+select a from t1 as t1 left join t1 as t2 using (a) left join t1 as t3 using (a) left join t1 as t4 using (a) left join t1 as t5 using (a) left join t1 as t6 using (a) left join t1 as t7 using (a) left join t1 as t8 using (a) left join t1 as t9 using (a) left join t1 as t10 using (a) left join t1 as t11 using (a) left join t1 as t12 using (a) left join t1 as t13 using (a) left join t1 as t14 using (a) left join t1 as t15 using (a) left join t1 as t16 using (a) left join t1 as t17 using (a) left join t1 as t18 using (a) left join t1 as t19 using (a) left join t1 as t20 using (a) left join t1 as t21 using (a) left join t1 as t22 using (a) left join t1 as t23 using (a) left join t1 as t24 using (a) left join t1 as t25 using (a) left join t1 as t26 using (a) left join t1 as t27 using (a) left join t1 as t28 using (a) left join t1 as t29 using (a) left join t1 as t30 using (a) left join t1 as t31 using (a) left join t1 as t32 using (a) left join t1 as t33 using (a) left join t1 as t34 using (a) left join t1 as t35 using (a) left join t1 as t36 using (a) left join t1 as t37 using (a) left join t1 as t38 using (a) left join t1 as t39 using (a) left join t1 as t40 using (a) left join t1 as t41 using (a) left join t1 as t42 using (a) left join t1 as t43 using (a) left join t1 as t44 using (a) left join t1 as t45 using (a) left join t1 as t46 using (a) left join t1 as t47 using (a) left join t1 as t48 using (a) left join t1 as t49 using (a) left join t1 as t50 using (a) left join t1 as t51 using (a) left join t1 as t52 using (a) left join t1 as t53 using (a) left join t1 as t54 using (a) left join t1 as t55 using (a) left join t1 as t56 using (a) left join t1 as t57 using (a) left join t1 as t58 using (a) left join t1 as t59 using (a) left join t1 as t60 using (a) left join t1 as t61 using (a) left join t1 as t62 using (a) left join t1 as t63 using (a) left join t1 as t64 using (a) left join t1 as t65 using (a);
 drop table t1;
 
 #
@@ -144,7 +146,7 @@ DROP TABLE t1, t2;
 CREATE TABLE t1 (d DATE NOT NULL);
 CREATE TABLE t2 (d DATE NOT NULL);
 INSERT INTO t1 (d) VALUES ('2001-08-01'),('0000-00-00');
-SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE t2.d IS NULL;
+SELECT * FROM t1 LEFT JOIN t2 USING (d) WHERE d IS NULL;
 SELECT * from t1 WHERE t1.d IS NULL;
 SELECT * FROM t1 WHERE 1/0 IS NULL;
 DROP TABLE t1,t2;
@@ -266,8 +268,8 @@ CREATE TABLE t2 (
   FULLTEXT KEY rate_code (rate_code)
 ) ENGINE=MyISAM;
 INSERT INTO t2 VALUES ('rivercats','cust',20);
-SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
-SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
+SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND siteid = 'rivercats';
+SELECT rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE siteid = 'rivercats' AND emp.emp_id = 'psmith';
 drop table t1,t2;
 
 #
@@ -315,14 +317,14 @@ select * from t3 right join t2 on (t3.i=t2.i);
 select * from t3 natural right join t2 natural right join t1;
 select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
 
-select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
+select * from t1,t2 natural left join t3 order by 1,2;
 select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
-select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
+select * from t2 natural left join t3,t1 order by t1.i;
 select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 
-select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
+select * from t1,t2 natural right join t3 order by 1,2;
 select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
-select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
+select * from t2 natural right join t3,t1 order by t1.i;
 select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
 drop table t1,t2,t3;
 
diff --git a/mysql-test/t/join_crash.test b/mysql-test/t/join_crash.test
index 68fd9226e41..2ec96dc2c28 100644
--- a/mysql-test/t/join_crash.test
+++ b/mysql-test/t/join_crash.test
@@ -92,18 +92,11 @@ select distinct
     t1.comments as comments,
     sum( t3.amount_received ) + sum( t3.adjustment ) as total_budget
 from
-    t1 ,
     t2 as client_period ,
-    t2 as project_period
-    left join
-        t3
-    on
-        t3.project_ptr = t1.project_id
-        and t3.date_received <= '2001-03-22 14:15:09'
-    left join
-        t4
-    on
-        t4.client_id = t1.client_ptr
+    t2 as project_period,
+    t3 left join t1 on (t3.project_ptr = t1.project_id and
+                        t3.date_received <= '2001-03-22 14:15:09')
+       left join t4 on t4.client_id = t1.client_ptr
    where
         1
         and ( client_period.period_type = 'client_table'
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index 992217d0391..20a9db7a196 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -130,15 +130,13 @@ SELECT t8.a,t8.b
 
 EXPLAIN EXTENDED
 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
-  FROM t6,
-       t7 
+  FROM (t6, t7)
        LEFT JOIN 
        t8
        ON t7.b=t8.b AND t6.b < 10;
 
 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
-  FROM t6,
-       t7 
+  FROM (t6, t7)
        LEFT JOIN 
        t8
        ON t7.b=t8.b AND t6.b < 10;
@@ -150,8 +148,7 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t5 
        LEFT JOIN 
        ( 
-         t6,
-         t7 
+         (t6, t7)
          LEFT JOIN 
          t8
          ON t7.b=t8.b AND t6.b < 10
@@ -162,8 +159,7 @@ SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
   FROM t5 
        LEFT JOIN 
        ( 
-         t6,
-         t7 
+         (t6, t7)
          LEFT JOIN 
          t8
          ON t7.b=t8.b AND t6.b < 10
@@ -186,8 +182,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5 
        LEFT JOIN 
        ( 
-         t6,
-         t7 
+         (t6, t7)
          LEFT JOIN 
          t8
          ON t7.b=t8.b AND t6.b < 10
@@ -203,8 +198,7 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
        t5 
        LEFT JOIN 
        ( 
-         t6,
-         t7 
+         (t6, t7)
          LEFT JOIN 
          t8
          ON t7.b=t8.b AND t6.b < 10
@@ -228,8 +222,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -252,8 +245,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -281,8 +273,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -308,8 +299,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -336,8 +326,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -375,8 +364,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -452,7 +440,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
     WHERE t1.a <= 2;
 
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-  FROM t1, t3, t4 
+  FROM t1, (t3, t4)
        RIGHT JOIN              
        t2
        ON t3.a=1 AND t2.b=t4.b
@@ -460,7 +448,7 @@ SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 
 EXPLAIN EXTENDED
 SELECT t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-  FROM t1, t3, t4
+  FROM t1, (t3, t4)
        RIGHT JOIN
        t2
        ON t3.a=1 AND t2.b=t4.b
@@ -470,13 +458,13 @@ CREATE INDEX idx_b ON t2(b);
 
 EXPLAIN EXTENDED
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-  FROM t3,t4 
+  FROM (t3,t4)
        LEFT JOIN              
        (t1,t2)
        ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
 
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
-  FROM t3,t4 
+  FROM (t3,t4)
        LEFT JOIN              
        (t1,t2)
        ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
@@ -494,8 +482,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -534,8 +521,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -573,8 +559,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -613,8 +598,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -649,8 +633,7 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
          t5 
          LEFT JOIN 
          ( 
-           t6,
-           t7 
+           (t6, t7)
            LEFT JOIN 
            t8
            ON t7.b=t8.b AND t6.b < 10
@@ -695,7 +678,7 @@ SELECT t2.a,t2.b,t3.a1,t3.b
   FROM t2 LEFT JOIN t3 ON t2.b=t3.b
     WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
 
-SELECT t2.a,t2.b,t3.a1,t3.b
+SELECT *
   FROM t2 NATURAL LEFT JOIN t3
     WHERE t2.a = 4 OR (t2.a > 4 AND t3.a1 IS NULL);
 
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index f48ae985e56..6a3b79f8354 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -19,11 +19,11 @@ select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
 select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
 select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
-select t1.*,t2.* from t1 left join t2 using (a);
-select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
-select t1.*,t2.* from t1 left join t2 using (a,c);
-select t1.*,t2.* from t1 left join t2 using (c);
-select t1.*,t2.* from t1 natural left outer join t2;
+select * from t1 left join t2 using (a);
+select t1.*,t2.* from t1 left join t2 on t1.a=t2.a;
+select * from t1 left join t2 using (a,c);
+select * from t1 left join t2 using (c);
+select * from t1 natural left outer join t2;
 
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
 select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
@@ -34,19 +34,19 @@ explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
 select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 
 # The next query should rearange the left joins to get this to work
---error 1120
+--error 1054
 explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
---error 1120
+--error 1054
 select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a);
 
 # The next query should give an error in MySQL
---error 1120
+--error 1054
 select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 
 # Test of inner join
-select t1.*,t2.* from t1 inner join t2 using (a);
+select * from t1 inner join t2 using (a);
 select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
-select t1.*,t2.* from t1 natural join t2;
+select * from t1 natural join t2;
 
 drop table t1,t2;
 
@@ -292,7 +292,7 @@ insert into t3 values (1);
 insert into t4 values (1,1);
 insert into t5 values (1,1);
 
---error 1120
+--error 1054
 explain select * from t3 left join t4 on t4.seq_1_id = t2.t2_id left join t1 on t1.t1_id = t4.seq_0_id left join t5 on t5.seq_0_id = t1.t1_id left join t2 on t2.t2_id = t5.seq_1_id where t3.t3_id = 23;
 
 drop table t1,t2,t3,t4,t5;
@@ -325,7 +325,7 @@ INSERT INTO t2 VALUES (2,'y');
 INSERT INTO t2 VALUES (3,'z');
 
 SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
-SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
+SELECT id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
 
 drop table t1,t2;
 
@@ -430,8 +430,8 @@ insert into t1 values(1),(2);
 insert into t2 values(2),(3);
 insert into t3 values(2),(4);
 select * from t1 natural left join t2 natural left join t3;
-select * from t1 natural left join t2 where (t2.i is not null)=0;
-select * from t1 natural left join t2 where (t2.i is not null) is not null;
+select * from t1 natural left join t2 where (i is not null)=0;
+select * from t1 natural left join t2 where (i is not null) is not null;
 drop table t1,t2,t3;
 
 #
@@ -440,7 +440,6 @@ drop table t1,t2,t3;
 create table t1 (f1 integer,f2 integer,f3 integer);
 create table t2 (f2 integer,f4 integer);
 create table t3 (f3 integer,f5 integer);
---error 1054
 select * from t1
          left outer join t2 using (f2)
          left outer join t3 using (f3);
@@ -657,8 +656,8 @@ create table t1 (a int, b varchar(20));
 create table t2 (a int, c varchar(20));
 insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
 insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
-select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
-select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
+select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
+select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
 drop table t1, t2;
 set group_concat_max_len=default;
 
diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index e15aec01d2a..4ab99fc5d6d 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -152,12 +152,18 @@ INSERT INTO t1 (order_id, product_id, product_type) VALUES
 INSERT INTO t2 (order_id, product_id, product_type) VALUES
 ('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);
 
-select t1.* from t1
-left join t2 using(order_id, product_id, product_type)
-where t2.order_id=NULL;
-select t1.* from t1
-left join t2 using(order_id, product_id, product_type)
+select t1.* from t1 left join t2
+       on (t1.order_id = t2.order_id and
+           t1.product_id = t2.product_id and
+           t1.product_type = t2.product_type)
+where t2.order_id = NULL;
+
+select t1.* from t1 left join t2
+       on (t1.order_id = t2.order_id and
+           t1.product_id = t2.product_id and
+           t1.product_type = t2.product_type)
 where t2.order_id is NULL;
+
 drop table t1,t2;
 
 #
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index e3b26a3e47f..615b317eb36 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -153,12 +153,12 @@ INSERT INTO t3 VALUES (2,'453 Boardwalk');
 
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
-LEFT JOIN t2 USING(c)
+LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c;
 
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
-LEFT JOIN t2 USING(c)
+LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c
 ORDER BY a;
 
@@ -339,8 +339,8 @@ CREATE TABLE t2 (
 
 INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
 INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
-SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
-SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+SELECT titre,numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
+SELECT titre,numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
 drop table t1,t2;
 
 #
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index eac76749589..1de0831ad84 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1538,22 +1538,22 @@ select t2.fld1,t22.fld1 from t2,t2 t22 where t2.fld1 >= 250501 and t2.fld1 <= 25
 #
 insert into t2 (fld1, companynr) values (999999,99);
 
-select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
-select count(*) from t2 left join t4 using (companynr) where t4.companynr is not null;
-explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null;
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
+select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
+select count(*) from t2 left join t4 using (companynr) where companynr is not null;
+explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
 delete from t2 where fld1=999999;
 
 #
 # Test left join optimization
 
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0;
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 # Following can't be optimized
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
-explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
+explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
+explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 
 #
 # Joins with forms.
@@ -1850,8 +1850,10 @@ select * from t1 left join (t1 as t2 left join t1 as t3 using (a)) using ( a );
 select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
 select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
 # right join on
-select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
-select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
+# TODO: WL#2486 - there is a problem in the order of tables in RIGHT JOIN
+#       check how we set next_name_resolution_table
+# select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
+# select * from t1 right join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
 # right [outer] joing using
 select * from (t1 as t2 left join t1 as t3 using (a)) right outer join t1 using ( a );
 select * from t1 right outer join (t1 as t2 left join t1 as t3 using (a)) using ( a );
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 41cdf9f1c64..cd79ddcf82a 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -696,7 +696,7 @@ CREATE TABLE `t1` (
 INSERT INTO t1 VALUES (1);
 UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
 UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
--- error 1109
+-- error 1054
 UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
 select * from t1;
 drop table t1;
@@ -1005,7 +1005,7 @@ create table t2 (s1 int);
 select * from t1 where (select count(*) from t2 where t1.s2) = 1;
 -- error 1054
 select * from t1 where (select count(*) from t2 group by t1.s2) = 1;
--- error 1109
+-- error 1054
 select count(*) from t2 group by t1.s2;
 drop table t1, t2;
 
@@ -1132,8 +1132,8 @@ CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
 INSERT INTO t1 VALUES (1), (2);
 INSERT INTO t2 VALUES (1);
-SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
-SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
+SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
+SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id;
 DROP TABLE t1,t2;
 
 #
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index c1a5ac382ec..85862821aab 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -157,12 +157,12 @@ create table t1 ( id integer unsigned not null primary key );
 create table t2 ( id integer unsigned not null primary key );
 insert into t1 values (1), (2);
 insert into t2 values (1);
-select  t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id ); 
+select  t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id); 
 create table t3 (id_A integer unsigned not null, id_B integer unsigned null  );
-insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
+insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
 select * from t3;
 drop table t3;
-create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
+create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
 select * from t3;
 drop table t1,t2,t3;
 
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 227d1cddcfa..d4b0c1746af 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -276,7 +276,7 @@ create temporary table t1 select a from t1 union select a from t2;
 drop temporary table t1;
 --error 1093
 create table t1 select a from t1 union select a from t2;
---error 1109
+--error 1054
 select a from t1 union select a from t2 order by t2.a;
 drop table t1,t2;