bk commit into 5.0 tree (timour:1.1974) BUG#12977

[timour]

Below is the list of changes that have just been committed into a local
5.0 repository of timka. When timka 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
  1.1974 05/09/08 11:29:52 timour@stripped +3 -0
  Fix for BUG#12977.

  sql/sql_base.cc
    1.297 05/09/08 11:29:49 timour@stripped +21 -7
    - Compare table qualifier of qualified fields only with tables that
      are not natural joins or their operands.
    - For qualified fields perform recursive search in all operands of
      natural joins that are nested joins.
    - Symmetrically detect ambiguous columns for both operands of
      NATURAL/USING joins.

  mysql-test/t/select.test
    1.74 05/09/08 11:29:49 timour@stripped +22 -0
    Test for BUG#12977.

  mysql-test/r/select.result
    1.89 05/09/08 11:29:49 timour@stripped +15 -0
    Test for BUG#12977.

# 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:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/5.0-2486

--- 1.296/sql/sql_base.cc	2005-09-02 17:06:09 +03:00
+++ 1.297/sql/sql_base.cc	2005-09-08 11:29:49 +03:00
@@ -2877,14 +2877,15 @@
   /*
     Check that the table and database that qualify the current field name
     are the same as the table we are going to search for the field.
-    This is done differently for NATURAL/USING joins because there we can't
-    simply compare the qualifying table and database names with the ones of
+    This is done differently for NATURAL/USING joins or nested joins that
+    are operands of NATURAL/USING joins because there we can't simply
+    compare the qualifying table and database names with the ones of
     'table_list' because each field in such a join may originate from a
     different table.
     TODO: Ensure that table_name, db_name and tables->db always points to
           something !
   */
-  if (!table_list->is_natural_join &&
+  if (!(table_list->nested_join && table_list->join_columns) &&
       table_name && table_name[0] &&
       (my_strcasecmp(table_alias_charset, table_list->alias, table_name) ||
        (db_name && db_name[0] && table_list->db &&
table_list->db[0] &&
@@ -2899,8 +2900,13 @@
                                  register_tree_change)))
       *actual_table= table_list;
   }
-  else if (table_list->is_natural_join)
+  else if (table_list->nested_join && table_list->join_columns)
   {
+    /*
+      If this is a NATURAL/USING join, or an operand of such join which is a
+      join itself, and the field name is qualified, then search for the field
+      in the operands of the join.
+    */
     if (table_name && table_name[0])
     {
       /*
@@ -2922,7 +2928,9 @@
     }
     /*
       Non-qualified field, search directly in the result columns of the
-      natural join.
+      natural join. The condition of the outer IF is true for the top-most
+      natural join, thus if the field is not qualified, we will search
+      directly the top-most NATURAL/USING join.
     */
     fld= find_field_in_natural_join(thd, table_list, name, length, ref,
                                     /* TIMOUR_TODO: check this with Sanja */
@@ -3528,10 +3536,16 @@
       if (add_columns && is_created_2)
         table_ref_2->join_columns->push_back(cur_nj_col_2);
 
-      /* Compare the two columns and check for duplicate common fields. */
+      /*
+        Compare the two columns and check for duplicate common fields.
+        A common field is duplicate either if it was already found in
+        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).
+      */
       if (!my_strcasecmp(system_charset_info, field_name_1, cur_field_name_2))
       {
-        if (found)
+        if (found || cur_nj_col_2->is_common)
         {
           my_error(ER_NON_UNIQ_ERROR, MYF(0), field_name_1, thd->where);
           goto err;

--- 1.88/mysql-test/r/select.result	2005-08-29 16:44:57 +03:00
+++ 1.89/mysql-test/r/select.result	2005-09-08 11:29:49 +03:00
@@ -2897,3 +2897,18 @@
 a
 b
 drop table t1, t2;
+CREATE TABLE t1 (`id` TINYINT);
+CREATE TABLE t2 (`id` TINYINT);
+CREATE TABLE t3 (`id` TINYINT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
+ERROR 23000: Column 'id' in from clause is ambiguous
+SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);
+ERROR 23000: Column 'id' in from clause is ambiguous
+SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
+ERROR 23000: Column 'id' in from clause is ambiguous
+SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);
+ERROR 23000: Column 'id' in from clause is ambiguous
+drop table t1, t2, t3;

--- 1.73/mysql-test/t/select.test	2005-08-29 16:44:57 +03:00
+++ 1.74/mysql-test/t/select.test	2005-09-08 11:29:49 +03:00
@@ -2465,3 +2465,25 @@
 select a from t1 natural join t2;
 select * from t1 natural join t2 where a = 'b';
 drop table t1, t2;
+
+#
+# Bug #12977 Compare table names with qualifying field tables only
+# for base tables, search all nested join operands of natural joins.
+#
+
+CREATE TABLE t1 (`id` TINYINT);
+CREATE TABLE t2 (`id` TINYINT);
+CREATE TABLE t3 (`id` TINYINT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+-- error 1052
+SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
+-- error 1052
+SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);
+-- error 1052
+SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);
+-- error 1052
+SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);
+
+drop table t1, t2, t3;