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-22 18:15:57+02:00, gkodinov@stripped +6 -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 rightmost 
     table
    2. Makes a "table" (TABLE_LIST) to wrap 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 rightmost table
    5. Passes the wrapping 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 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.

  mysql-test/r/join_nested.result@stripped, 2007-01-22 18:15:48+02:00, gkodinov@stripped
+13 -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-22 18:15:48+02:00, gkodinov@stripped +19
-0
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - test case

  sql/sql_base.cc@stripped, 2007-01-22 18:15:49+02:00, gkodinov@stripped +33 -8
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - proper check for duplicate cols
     - more detailed debug output

  sql/sql_parse.cc@stripped, 2007-01-22 18:15:50+02:00, gkodinov@stripped +3 -6
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - proper check for duplicate cols

  sql/table.cc@stripped, 2007-01-22 18:15:51+02:00, gkodinov@stripped +1 -1
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - return null if no table ref (as in nested join columns).

  sql/table.h@stripped, 2007-01-22 18:15:51+02:00, gkodinov@stripped +13 -0
    BUG#25575: ERROR 1052 (Column in from clause is ambiguous) with sub-join
     - proper check for duplicate cols

# 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.364/sql/sql_base.cc	2007-01-11 22:20:26 +02:00
+++ 1.365/sql/sql_base.cc	2007-01-22 18:15:49 +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)
   {
@@ -3777,6 +3784,9 @@ mark_common_columns(THD *thd, TABLE_LIST
     if (!(nj_col_1= it_1.get_or_create_column_ref(leaf_1)))
       goto err;
     field_name_1= nj_col_1->name();
+    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 +3803,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.
@@ -3803,7 +3817,10 @@ mark_common_columns(THD *thd, TABLE_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 ||
+                       test_if_string_in_list(field_name_1, using_fields))))
         {
           my_error(ER_NON_UNIQ_ERROR, MYF(0), field_name_1, thd->where);
           goto err;
@@ -3886,6 +3903,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)
       {
@@ -4192,6 +4216,7 @@ store_top_level_join_columns(THD *thd, T
       and the two tables may be reordered.
     */
     table_ref_1->natural_join= table_ref_2->natural_join= NULL;
+    table_ref_1->join_using= table_ref_2->join_using= NULL;
 
     /* Add a TRUE condition to outer joins that have no common columns. */
     if (table_ref_2->outer_join &&

--- 1.593/sql/sql_parse.cc	2007-01-09 12:12:22 +02:00
+++ 1.594/sql/sql_parse.cc	2007-01-22 18:15:50 +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 (table->join_using)
+        ptr->join_using_fields= table->join_using;
     }
   }
   join_list->push_front(ptr);
@@ -6656,7 +6653,7 @@ void add_join_on(TABLE_LIST *b, Item *ex
 void add_join_natural(TABLE_LIST *a, TABLE_LIST *b, List<String> *using_fields)
 {
   b->natural_join= a;
-  b->join_using_fields= using_fields;
+  b->join_using= using_fields;
 }
 
 

--- 1.240/sql/table.cc	2007-01-09 12:12:23 +02:00
+++ 1.241/sql/table.cc	2007-01-22 18:15:51 +02:00
@@ -2628,7 +2628,7 @@ Field *Natural_join_column::field()
 
 const char *Natural_join_column::table_name()
 {
-  return table_ref->alias;
+  return table_ref ? table_ref->alias : NULL;
 }
 
 

--- 1.137/sql/table.h	2006-12-30 22:02:07 +02:00
+++ 1.138/sql/table.h	2007-01-22 18:15:51 +02:00
@@ -492,6 +492,19 @@ typedef struct st_table_list
     to the other operand of 'this'.
   */
   bool is_natural_join;
+  /* 
+    This is a buffer for the original JOIN USING list that comes from
+    the parser. The parser :
+      1. Sets the natural_join/join_using of the second TABLE_LIST in the join
+      2. Makes a wrapper TABLE_LIST and sets it's 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 that stage (1.) the wrapping 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> *join_using;
   /* Field names in a USING clause for JOIN ... USING. */
   List<String> *join_using_fields;
   /*

--- 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-22 18:15:48 +02:00
@@ -1605,3 +1605,16 @@ 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
+DROP TABLE t1,t2,t3,t4,t5;
+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-22 18:15:48 +02:00
@@ -1045,3 +1045,22 @@ 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);
+
+DROP TABLE t1,t2,t3,t4,t5;
+--echo End of 5.0 tests