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.

1 files changed, 32 insertions, 55 deletions

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);