bk commit into 5.0 tree (gkodinov:1.2385) BUG#25575

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-01-31 16:04:38+02:00, gkodinov@stripped +8 -0
  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@stripped, 2007-01-31 16:04:26+02:00, gkodinov@stripped
+28 -0
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - test case

  mysql-test/t/join_nested.test@stripped, 2007-01-31 16:04:27+02:00, gkodinov@stripped +39
-0
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - test case

  sql/mysql_priv.h@stripped, 2007-01-31 16:04:27+02:00, gkodinov@stripped +2 -1
    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@stripped, 2007-01-31 16:04:28+02:00, gkodinov@stripped +40 -11
    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@stripped, 2007-01-31 16:04:29+02:00, gkodinov@stripped +14 -0
    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@stripped, 2007-01-31 16:04:30+02:00, gkodinov@stripped +6 -7
    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@stripped, 2007-01-31 16:04:31+02:00, gkodinov@stripped +10 -6
    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@stripped, 2007-01-31 16:04:31+02:00, gkodinov@stripped +1 -0
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - return null if no table ref (as in nested join columns).

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B25575-5.0-opt

--- 1.428/sql/mysql_priv.h	2007-01-11 22:17:39 +02:00
+++ 1.429/sql/mysql_priv.h	2007-01-31 16:04:27 +02:00
@@ -982,7 +982,8 @@ bool push_new_name_resolution_context(TH
                                       TABLE_LIST *left_op,
                                       TABLE_LIST *right_op);
 void add_join_on(TABLE_LIST *b,Item *expr);
-void add_join_natural(TABLE_LIST *a,TABLE_LIST *b,List<String> *using_fields);
+void add_join_natural(TABLE_LIST *a,TABLE_LIST *b,List<String> *using_fields,
+                      SELECT_LEX *lex);
 bool add_proc_to_list(THD *thd, Item *item);
 TABLE *unlink_open_table(THD *thd,TABLE *list,TABLE *find);
 void update_non_unique_table_error(TABLE_LIST *update,

--- 1.364/sql/sql_base.cc	2007-01-11 22:20:26 +02:00
+++ 1.365/sql/sql_base.cc	2007-01-31 16:04:28 +02:00
@@ -2945,7 +2945,7 @@ find_field_in_natural_join(THD *thd, TAB
 {
   List_iterator_fast<Natural_join_column>
     field_it(*(table_ref->join_columns));
-  Natural_join_column *nj_col;
+  Natural_join_column *nj_col, *curr_nj_col;
   Field *found_field;
   Query_arena *arena, backup;
   DBUG_ENTER("find_field_in_natural_join");
@@ -2956,14 +2956,21 @@ find_field_in_natural_join(THD *thd, TAB
 
   LINT_INIT(found_field);
 
-  for (;;)
+  for (nj_col= NULL, curr_nj_col= field_it++; curr_nj_col; 
+       curr_nj_col= field_it++)
   {
-    if (!(nj_col= field_it++))
-      DBUG_RETURN(NULL);
-
-    if (!my_strcasecmp(system_charset_info, nj_col->name(), name))
-      break;
+    if (!my_strcasecmp(system_charset_info, curr_nj_col->name(), name))
+    {
+      if (nj_col)
+      {
+        my_error(ER_NON_UNIQ_ERROR, MYF(0), name, thd->where);
+        DBUG_RETURN(NULL);
+      }
+      nj_col= curr_nj_col;
+    }
   }
+  if (!nj_col)
+    DBUG_RETURN(NULL);
 
   if (nj_col->view_field)
   {
@@ -3774,9 +3781,16 @@ mark_common_columns(THD *thd, TABLE_LIST
   {
     bool found= FALSE;
     const char *field_name_1;
+    /* true if field_name_1 is a member of using_fields */
+    bool is_using_column_1;
     if (!(nj_col_1= it_1.get_or_create_column_ref(leaf_1)))
       goto err;
     field_name_1= nj_col_1->name();
+    is_using_column_1= using_fields && 
+      test_if_string_in_list(field_name_1, using_fields);
+    DBUG_PRINT ("info", ("field_name_1=%s.%s", 
+                         nj_col_1->table_name() ? nj_col_1->table_name() : "", 
+                         field_name_1));
 
     /*
       Find a field with the same name in table_ref_2.
@@ -3793,6 +3807,10 @@ mark_common_columns(THD *thd, TABLE_LIST
       if (!(cur_nj_col_2= it_2.get_or_create_column_ref(leaf_2)))
         goto err;
       cur_field_name_2= cur_nj_col_2->name();
+      DBUG_PRINT ("info", ("cur_field_name_2=%s.%s", 
+                           cur_nj_col_2->table_name() ? 
+                             cur_nj_col_2->table_name() : "", 
+                           cur_field_name_2));
 
       /*
         Compare the two columns and check for duplicate common fields.
@@ -3800,10 +3818,16 @@ mark_common_columns(THD *thd, TABLE_LIST
         table_ref_2 (then found == TRUE), or if a field in table_ref_2
         was already matched by some previous field in table_ref_1
         (then cur_nj_col_2->is_common == TRUE).
+        Note that it is too early to check the columns outside of the
+        USING list for ambiguity because they are not actually "referenced"
+        here. These columns must be checked only on unqualified reference 
+        by name (e.g. in SELECT list).
       */
       if (!my_strcasecmp(system_charset_info, field_name_1, cur_field_name_2))
       {
-        if (found || cur_nj_col_2->is_common)
+        DBUG_PRINT ("info", ("match c1.is_common=%d", nj_col_1->is_common));
+        if (cur_nj_col_2->is_common ||
+            (found && (!using_fields || is_using_column_1)))
         {
           my_error(ER_NON_UNIQ_ERROR, MYF(0), field_name_1, thd->where);
           goto err;
@@ -3829,9 +3853,7 @@ mark_common_columns(THD *thd, TABLE_LIST
       clause (if present), mark them as common fields, and add a new
       equi-join condition to the ON clause.
     */
-    if (nj_col_2 &&
-        (!using_fields ||
-          test_if_string_in_list(field_name_1, using_fields)))
+    if (nj_col_2 && (!using_fields ||is_using_column_1))
     {
       Item *item_1=   nj_col_1->create_item(thd);
       Item *item_2=   nj_col_2->create_item(thd);
@@ -3886,6 +3908,13 @@ mark_common_columns(THD *thd, TABLE_LIST
                   eq_cond);
 
       nj_col_1->is_common= nj_col_2->is_common= TRUE;
+      DBUG_PRINT ("info", ("%s.%s and %s.%s are common", 
+                           nj_col_1->table_name() ? 
+                             nj_col_1->table_name() : "", 
+                           nj_col_1->name(),
+                           nj_col_2->table_name() ? 
+                             nj_col_2->table_name() : "", 
+                           nj_col_2->name()));
 
       if (field_1)
       {

--- 1.236/sql/sql_lex.h	2007-01-11 22:17:40 +02:00
+++ 1.237/sql/sql_lex.h	2007-01-31 16:04:29 +02:00
@@ -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();

--- 1.593/sql/sql_parse.cc	2007-01-09 12:12:22 +02:00
+++ 1.594/sql/sql_parse.cc	2007-01-31 16:04:30 +02:00
@@ -6365,11 +6365,8 @@ TABLE_LIST *st_select_lex::nest_last_joi
         If this is a JOIN ... USING, move the list of joined fields to the
         table reference that describes the join.
       */
-      if (table->join_using_fields)
-      {
-        ptr->join_using_fields= table->join_using_fields;
-        table->join_using_fields= NULL;
-      }
+      if (prev_join_using)
+        ptr->join_using_fields= prev_join_using;
     }
   }
   join_list->push_front(ptr);
@@ -6625,6 +6622,7 @@ void add_join_on(TABLE_LIST *b, Item *ex
     a			Left join argument
     b			Right join argument
     using_fields        Field names from USING clause
+    lex                 The current st_select_lex
   
   IMPLEMENTATION
     This function marks that table b should be joined with a either via
@@ -6653,10 +6651,11 @@ void add_join_on(TABLE_LIST *b, Item *ex
     None
 */
 
-void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List<String> *using_fields)
+void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List<String> *using_fields,
+                      SELECT_LEX *lex)
 {
   b->natural_join= a;
-  b->join_using_fields= using_fields;
+  lex->prev_join_using= using_fields;
 }
 
 

--- 1.499/sql/sql_yacc.yy	2006-12-23 21:04:28 +02:00
+++ 1.500/sql/sql_yacc.yy	2007-01-31 16:04:31 +02:00
@@ -5280,11 +5280,11 @@ join_table:
             YYERROR_UNLESS($1 && $3);
 	  }
 	  '(' using_list ')'
-          { add_join_natural($1,$3,$7); $$=$3; }
+          { add_join_natural($1,$3,$7,Select); $$=$3; }
 	| table_ref NATURAL JOIN_SYM table_factor
 	  {
             YYERROR_UNLESS($1 && ($$=$4));
-            add_join_natural($1,$4,NULL);
+            add_join_natural($1,$4,NULL,Select);
           }
 
 /* LEFT JOIN variants */
@@ -5311,11 +5311,15 @@ join_table:
             YYERROR_UNLESS($1 && $5);
 	  }
 	  USING '(' using_list ')'
-          { add_join_natural($1,$5,$9); $5->outer_join|=JOIN_TYPE_LEFT; $$=$5; }
+          { 
+            add_join_natural($1,$5,$9,Select); 
+            $5->outer_join|=JOIN_TYPE_LEFT; 
+            $$=$5; 
+          }
 	| table_ref NATURAL LEFT opt_outer JOIN_SYM table_factor
 	  {
             YYERROR_UNLESS($1 && $6);
- 	    add_join_natural($1,$6,NULL);
+ 	    add_join_natural($1,$6,NULL,Select);
 	    $6->outer_join|=JOIN_TYPE_LEFT;
 	    $$=$6;
 	  }
@@ -5349,12 +5353,12 @@ join_table:
 	    LEX *lex= Lex;
             if (!($$= lex->current_select->convert_right_join()))
               YYABORT;
-            add_join_natural($$,$5,$9);
+            add_join_natural($$,$5,$9,Select);
           }
 	| table_ref NATURAL RIGHT opt_outer JOIN_SYM table_factor
 	  {
             YYERROR_UNLESS($1 && $6);
-	    add_join_natural($6,$1,NULL);
+	    add_join_natural($6,$1,NULL,Select);
 	    LEX *lex= Lex;
             if (!($$= lex->current_select->convert_right_join()))
               YYABORT;

--- 1.240/sql/table.cc	2007-01-09 12:12:23 +02:00
+++ 1.241/sql/table.cc	2007-01-31 16:04:31 +02:00
@@ -2628,6 +2628,7 @@ Field *Natural_join_column::field()
 
 const char *Natural_join_column::table_name()
 {
+  DBUG_ASSERT(table_ref);
   return table_ref->alias;
 }
 

--- 1.24/mysql-test/r/join_nested.result	2007-01-03 22:16:01 +02:00
+++ 1.25/mysql-test/r/join_nested.result	2007-01-31 16:04:26 +02:00
@@ -1605,3 +1605,31 @@ WHERE t1.id='5';
 id	ct	pc	nm
 5	NULL	NULL	NULL
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT, c INT);
+CREATE TABLE t4 (a INT, c INT);
+CREATE TABLE t5 (a INT, c INT);
+SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+b
+SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+ERROR 23000: Column 'c' in field list is ambiguous
+SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+b
+SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+ERROR 23000: Column 'c' in field list is ambiguous
+DROP TABLE t1,t2,t3,t4,t5;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t3 VALUES (1,1);
+SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a);
+ERROR 23000: Column 'a' in from clause is ambiguous
+DROP TABLE t1,t2,t3;
+End of 5.0 tests

--- 1.20/mysql-test/t/join_nested.test	2007-01-03 22:16:01 +02:00
+++ 1.21/mysql-test/t/join_nested.test	2007-01-31 16:04:27 +02:00
@@ -1045,3 +1045,42 @@ SELECT t1.*, t4.nm
     WHERE t1.id='5';
 
 DROP TABLE t1,t2,t3,t4;
+
+#
+# BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
+#
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT, c INT);
+CREATE TABLE t4 (a INT, c INT);
+CREATE TABLE t5 (a INT, c INT);
+
+SELECT b FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+
+--error ER_NON_UNIQ_ERROR
+SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a)
+LEFT JOIN t5 USING (a)) USING (a);
+
+SELECT b FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+
+--error ER_NON_UNIQ_ERROR
+SELECT c FROM t1 JOIN (t2 JOIN t3 USING (a) JOIN t4 USING (a)
+JOIN t5 USING (a)) USING (a);
+
+DROP TABLE t1,t2,t3,t4,t5;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, b INT);
+
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t2 VALUES (1,1);
+INSERT INTO t3 VALUES (1,1);
+
+--error ER_NON_UNIQ_ERROR
+SELECT * FROM t1 JOIN (t2 JOIN t3 USING (b)) USING (a);
+
+DROP TABLE t1,t2,t3;
+
+--echo End of 5.0 tests