BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join

 Two problems here:

 Problem 1:

 While constructing the join columns list the optimizer does as follows:
  1. Sets the join_using_fields/natural_join members of the right JOIN 
   operand.
  2. Makes a "table reference" (TABLE_LIST) to parent the two tables.
  3. Assigns the join_using_fields/is_natural_join of the wrapper table
   using join_using_fields/natural_join of the rightmost table
  4. Sets join_using_fields to NULL for the right JOIN operand.
  5. Passes the parent table up to the same procedure on the upper 
   level.

 Step 1 overrides the the join_using_fields that are set for a nested 
 join wrapping table in step 4.
 Fixed by making a designated variable SELECT_LEX::prev_join_using to 
 pass the data from step 1 to step 4 without destroying the wrapping 
 table data.

 Problem 2:

 The optimizer checks for ambiguous columns while transforming 
 NATURAL JOIN/JOIN USING to JOIN ON. While doing that there was no
 distinction between columns that are used in the generated join
 condition (where ambiguity can be checked) and the other columns
 (where ambiguity can be checked only when resolving references
 coming from outside the JOIN construct itself).
 Fixed by allowing the non-USING columns to be present in multiple 
 copies in both sides of the join and moving the ambiguity check 
 to the place where unqualified references to the join columns are
 resolved (find_field_in_natural_join()).


mysql-test/r/join_nested.result:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - test case
mysql-test/t/join_nested.test:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - test case
sql/mysql_priv.h:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - use SELECT_LEX to store the ref to JOIN USING list needed by the 
     parser
sql/sql_base.cc:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - proper check for duplicate cols
   - more detailed debug output
sql/sql_lex.h:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - use SELECT_LEX to store the ref to JOIN USING list needed by the 
     parser
sql/sql_parse.cc:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - proper check for duplicate cols in JOIN USING
sql/sql_yacc.yy:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - use SELECT_LEX to store the ref to JOIN USING list needed by the 
     parser
sql/table.cc:
  BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
   - return null if no table ref (as in nested join columns).

1 files changed, 14 insertions, 0 deletions

diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 1bf37c92b85..83daec89a50 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -587,6 +587,20 @@ public:
   int cur_pos_in_select_list;
 
   List<udf_func>     udf_list;                  /* udf function calls stack */
+  /* 
+    This is a copy of the original JOIN USING list that comes from
+    the parser. The parser :
+      1. Sets the natural_join of the second TABLE_LIST in the join
+         and the st_select_lex::prev_join_using.
+      2. Makes a parent TABLE_LIST and sets its is_natural_join/
+       join_using_fields members.
+      3. Uses the wrapper TABLE_LIST as a table in the upper level.
+    We cannot assign directly to join_using_fields in the parser because
+    at stage (1.) the parent TABLE_LIST is not constructed yet and
+    the assignment will override the JOIN USING fields of the lower level
+    joins on the right.
+  */
+  List<String> *prev_join_using;
 
   void init_query();
   void init_select();