WL#2486 - natural and using join according to SQL:2003

* Provide backwards compatibility extension to name resolution of
  coalesced columns. The patch allows such columns to be qualified
  with a table (and db) name, as it is in 4.1.
  Based on a patch from Monty.

* Adjusted tests accordingly to test both backwards compatible name
  resolution of qualified columns, and ANSI-style resolution of
  non-qualified columns.
  For this, each affected test has two versions - one with qualified
  columns, and one without. 


mysql-test/include/ps_query.inc:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/bdb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/innodb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/join.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/join_nested.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/join_outer.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/null_key.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/order_by.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_2myisam.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_3innodb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_4heap.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_5merge.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_6bdb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/ps_7ndb.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/select.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/subselect.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/r/type_ranges.result:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/bdb.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/innodb.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/join.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/join_nested.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/join_outer.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/null_key.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/order_by.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/select.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/subselect.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
mysql-test/t/type_ranges.test:
  Put back old tests to test that coalesced columns of natural joins can be qualified.
sql/sql_base.cc:
  * Applied Monty's patch for backwards compatible name resolution
    of qualified columns. The idea is:
    - When a column is qualified, search for the column in all
      tables/views underlying each natural join. In this case
      natural joins are *not* considered leaves.
    - If a column is not qualified, then consider natural joins
      as leaves, thus directly search the result columns of
      natural joins.
  * Simplified 'find_field_in_tables()' - unified two similar
    loops into one.
sql/table.cc:
  - Removed method & members not needed after Monty's patch.
sql/table.h:
  - Removed method & members not needed after Monty's patch.
tests/mysql_client_test.c:
  Put back old tests to test that coalesced columns of natural joins can be qualified.

3 files changed, 95 insertions, 161 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index b8748b89fbd..8017a82f1ed 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2587,8 +2587,6 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list,
     thd			 [in]  thread handler
     table_ref            [in]  table reference to search
     name		 [in]  name of field
-    table_name           [in]  optional table name that qualifies the field
-    db_name              [in]  optional database name that qualifies the field
     length		 [in]  length of name
     ref                  [in/out] if 'name' is resolved to a view field, ref is
                                set to point to the found view field
@@ -2599,6 +2597,12 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list,
                                belongs - differs from 'table_list' only for
                                NATURAL/USING joins
 
+  DESCRIPTION
+    Search for a field among the result fields of a NATURAL/USING join.
+    Notice that this procedure is called only for non-qualified field
+    names. In the case of qualified fields, we search directly the base
+    tables of a natural join.
+
   RETURN
     NULL        if the field was not found
     WRONG_GRANT if no access rights to the found field
@@ -2607,7 +2611,6 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list,
 
 static Field *
 find_field_in_natural_join(THD *thd, TABLE_LIST *table_ref, const char *name,
-                           const char *table_name, const char *db_name,
                            uint length, Item **ref, bool check_grants,
                            bool register_tree_change,
                            TABLE_LIST **actual_table)
@@ -2629,27 +2632,6 @@ find_field_in_natural_join(THD *thd, TABLE_LIST *table_ref, const char *name,
     if (!(nj_col= field_it++))
       DBUG_RETURN(NULL);
 
-    if (table_name)
-    {
-      /*
-        Coalesced columns cannot be qualified unless this is the execute phase
-        of prepared statements. The reason is that they do not belong to any
-        table, but for PS the prepare phase already resolves and stores
-        items, so during the execution phase we resolve fully qualified items.
-      */
-      if (!thd->current_arena->is_stmt_execute() && nj_col->is_coalesced)
-        continue;
-      if (table_name[0] &&
-          my_strcasecmp(table_alias_charset, nj_col->table_name(), table_name))
-        continue;
-      if (db_name && db_name[0])
-      {
-        const char *cur_db_name= nj_col->db_name();
-        if (cur_db_name && strcmp(db_name, cur_db_name))
-          continue;
-      }
-    }
-
     if (!my_strcasecmp(system_charset_info, nj_col->name(), name))
       break;
   }
@@ -2842,11 +2824,35 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list,
       *actual_table= table_list;
   }
   else if (table_list->is_natural_join)
-    fld= find_field_in_natural_join(thd, table_list, name, table_name,
-                                    db_name,  length, ref,
+  {
+    if (table_name && table_name[0])
+    {
+      /*
+        Qualified field; Search for it in the tables used by the natural join.
+      */
+      List_iterator<TABLE_LIST> it(table_list->nested_join->join_list);
+      TABLE_LIST *table;
+      while ((table= it++))
+      {
+        if ((fld= find_field_in_table_ref(thd, table, name, item_name,
+                                          table_name, db_name, length, ref,
+                                          check_grants_table,
+                                          check_grants_view,
+                                          allow_rowid, cached_field_index_ptr,
+                                          register_tree_change, actual_table)))
+          DBUG_RETURN(fld);
+      }
+      DBUG_RETURN(0);
+    }
+    /*
+      Non-qualified field, search directly in the result columns of the
+      natural join.
+    */
+    fld= find_field_in_natural_join(thd, table_list, name, length, ref,
                                     /* TIMOUR_TODO: check this with Sanja */
                                     check_grants_table || check_grants_view,
                                     register_tree_change, actual_table);
+  }
   else
   {
     if ((fld= find_field_in_table(thd, table_list->table, name, length,
@@ -2915,9 +2921,11 @@ find_field_in_tables(THD *thd, Item_ident *item,
   const char *name= item->field_name;
   uint length=(uint) strlen(name);
   char name_buff[NAME_LEN+1];
-  bool allow_rowid;
   TABLE_LIST *cur_table= first_table;
   TABLE_LIST *actual_table;
+  bool is_qualified= table_name && table_name[0];
+  bool allow_rowid= is_qualified ?
+                    TRUE : (cur_table && !cur_table->next_local);
 
   if (item->cached_table)
   {
@@ -2988,81 +2996,13 @@ find_field_in_tables(THD *thd, Item_ident *item,
   if (last_table)
     last_table= last_table->next_name_resolution_table;
 
-  /* The field we search for is qualified with a table name and optional db. */
-  if (table_name && table_name[0])
-  {
-    for (; cur_table != last_table ;
-         cur_table= cur_table->next_name_resolution_table)
-    {
-      Field *cur_field= find_field_in_table_ref(thd, cur_table, name,
-                                                item->name, table_name,
-                                                db, length, ref,
-                                                (cur_table->table &&
-                                                 test(cur_table->table->grant.
-                                                      want_privilege) &&
-                                                 check_privileges),
-                                                (test(cur_table->grant.
-                                                      want_privilege) &&
-                                                 check_privileges),
-                                                1, &(item->cached_field_index),
-                                                register_tree_change,
-                                                &actual_table);
-      if (cur_field)
-      {
-        /*
-          Store the original table of the field, which may be different from
-          cur_table in the case of NATURAL/USING join.
-        */
-        item->cached_table= (!actual_table->cacheable_table) ? 0 : actual_table;
-
-        if (cur_field == WRONG_GRANT)
-          return (Field*) 0;
-        if (db || !thd->where)
-          return cur_field;
-        if (found)
-        {
-          if (report_error == REPORT_ALL_ERRORS ||
-              report_error == IGNORE_EXCEPT_NON_UNIQUE)
-            my_error(ER_NON_UNIQ_ERROR, MYF(0),
-                     item->full_name(),thd->where);
-          return (Field*) 0;
-        }
-        found= cur_field;
-      }
-    }
-    if (found)
-      return found;
-    /*
-      If there were no tables to search, we wouldn't go through the loop and
-      cur_table wouldn't be updated by the loop increment part.
-    */
-    if (cur_table == first_table && (report_error == REPORT_ALL_ERRORS ||
-                                     report_error == REPORT_EXCEPT_NON_UNIQUE))
-    {
-      char buff[NAME_LEN*2+1];
-      if (db && db[0])
-      {
-	strxnmov(buff,sizeof(buff)-1,db,".",table_name,NullS);
-	table_name=buff;
-      }
-      my_error(ER_UNKNOWN_TABLE, MYF(0), table_name, thd->where);
-    }
-    else
-      if (report_error == REPORT_ALL_ERRORS ||
-          report_error == REPORT_EXCEPT_NON_UNIQUE)
-	my_error(ER_BAD_FIELD_ERROR, MYF(0), item->full_name(),thd->where);
-      else
-	return (Field*) not_found_field;
-    return (Field*) 0;
-  }
-
-  /* The field we search for is not qualified. */
-  allow_rowid= cur_table && !cur_table->next_local;
   for (; cur_table != last_table ;
        cur_table= cur_table->next_name_resolution_table)
   {
     Field *cur_field= find_field_in_table_ref(thd, cur_table, name, item->name,
-                                              NULL, NULL, length, ref,
+                                              is_qualified ? table_name : NULL,
+                                              is_qualified ? db : NULL,
+                                              length, ref,
                                               (cur_table->table &&
                                                test(cur_table->table->grant.
                                                     want_privilege) &&
@@ -3086,26 +3026,57 @@ find_field_in_tables(THD *thd, Item_ident *item,
       item->cached_table= (!actual_table->cacheable_table || found) ?
                           0 : actual_table;
 
+      DBUG_ASSERT(thd->where);
+      /*
+        If we found a fully qualified field we return it directly as it can't
+        have duplicates.
+       */
+      if (is_qualified && db)
+        return cur_field;
+
       if (found)
       {
-	if (!thd->where)			// Returns first found
-	  break;
         if (report_error == REPORT_ALL_ERRORS ||
             report_error == IGNORE_EXCEPT_NON_UNIQUE)
-          my_error(ER_NON_UNIQ_ERROR, MYF(0), name, thd->where);
+          my_error(ER_NON_UNIQ_ERROR, MYF(0),
+                   is_qualified ? item->full_name() : name, thd->where);
 	return (Field*) 0;
       }
       found= cur_field;
     }
   }
+
   if (found)
     return found;
-  if (report_error == REPORT_ALL_ERRORS ||
-      report_error == REPORT_EXCEPT_NON_UNIQUE)
-    my_error(ER_BAD_FIELD_ERROR, MYF(0), item->full_name(), thd->where);
+
+  /*
+    If the field was qualified and there were no tables to search, issue
+    an error that an unknown table was given. The situation is detected
+    as follows: if there were no tables we wouldn't go through the loop
+    and cur_table wouldn't be updated by the loop increment part, so it
+    will be equal to the first table.
+  */
+  if (is_qualified && (cur_table == first_table) &&
+      (report_error == REPORT_ALL_ERRORS ||
+       report_error == REPORT_EXCEPT_NON_UNIQUE))
+  {
+    char buff[NAME_LEN*2+1];
+    if (db && db[0])
+    {
+      strxnmov(buff,sizeof(buff)-1,db,".",table_name,NullS);
+      table_name=buff;
+    }
+    my_error(ER_UNKNOWN_TABLE, MYF(0), table_name, thd->where);
+  }
   else
-    return (Field*) not_found_field;
-  return (Field*) 0;
+    if (report_error == REPORT_ALL_ERRORS ||
+        report_error == REPORT_EXCEPT_NON_UNIQUE)
+      my_error(ER_BAD_FIELD_ERROR, MYF(0), item->full_name(), thd->where);
+    else
+      found= not_found_field;
+
+  DBUG_ASSERT(!found || found == not_found_field);
+  return found;
 }
 
 
@@ -3510,10 +3481,6 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2,
       Item_ident *item_ident_1, *item_ident_2;
       Item_func_eq *eq_cond;
 
-      DBUG_PRINT("info", ("new equi-join condition:  %s.%s = %s.%s",
-                          table_ref_1->alias, field_1->field_name,
-                          table_ref_2->alias, field_2->field_name));
-
       if (!item_1 || !item_2)
         goto err;                               // out of memory
 
@@ -3560,7 +3527,6 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2,
                   eq_cond);
 
       nj_col_1->is_common= nj_col_2->is_common= TRUE;
-      nj_col_1->is_coalesced= nj_col_2->is_coalesced= TRUE;
 
       if (field_1)
       {
@@ -4310,29 +4276,28 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
   }
 
   found= FALSE;
-  for (TABLE_LIST *tables= context->first_name_resolution_table;
+
+  /*
+    If table names are qualified, then loop over all tables used in the query,
+    else treat natural joins as leaves and do not iterate over their underlying
+    tables.
+  */
+  for (TABLE_LIST *tables= (table_name ? context->table_list :
+                            context->first_name_resolution_table);
        tables;
-       tables= tables->next_name_resolution_table)
+       tables= (table_name ? tables->next_local :
+                tables->next_name_resolution_table)
+       )
   {
     Field *field;
     TABLE *table= tables->table;
 
     DBUG_ASSERT(tables->is_leaf_for_name_resolution());
 
-    /*
-      If optional table and db names do not match the ones used to qualify
-      the field being expanded, skip this table reference. However if this is
-      a NATURAL/USING join, we can't simply skip the whole table reference,
-      because  its columns may come from different tables/views. For NATURAL/
-      USING joins we perform this test for each column in the loop below.
-    */
-    if (!tables->is_natural_join)
-    {
-      if (table_name && my_strcasecmp(table_alias_charset, table_name,
-                                      tables->alias) ||
-          (db_name && strcmp(tables->db,db_name)))
-        continue;
-    }
+    if (table_name && my_strcasecmp(table_alias_charset, table_name,
+                                    tables->alias) ||
+        (db_name && strcmp(tables->db,db_name)))
+      continue;
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
     /* Ensure that we have access rights to all fields to be inserted. */
@@ -4369,21 +4334,6 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
     {
       Item *item;
 
-      /*
-        If this is a column of a NATURAL/USING join, and the star was
-        qualified with a table (and database) name, check if the
-        column is not a coalesced one, and if not, that is belongs to
-        the same table.
-      */
-      if (tables->is_natural_join && table_name)
-      {
-        if (field_iterator.is_coalesced() ||
-            my_strcasecmp(table_alias_charset, table_name,
-                          field_iterator.table_name()) ||
-            (db_name && strcmp(db_name, field_iterator.db_name())))
-          continue;
-      }
-
       if (!(item= field_iterator.create_item(thd)))
         DBUG_RETURN(TRUE);
 
@@ -4410,18 +4360,18 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
                     tables->is_natural_join);
         DBUG_ASSERT(item->type() == Item::FIELD_ITEM);
         Item_field *fld= (Item_field*) item;
-        const char *table_name= field_iterator.table_name();
+        const char *field_table_name= field_iterator.table_name();
 
         if (!tables->schema_table && 
             !(fld->have_privileges=
               (get_column_grant(thd, field_iterator.grant(),
                                 field_iterator.db_name(),
-                                table_name, fld->field_name) &
+                                field_table_name, fld->field_name) &
                VIEW_ANY_ACL)))
         {
           my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0), "ANY",
                    thd->priv_user, thd->host_or_ip,
-                   fld->field_name, table_name);
+                   fld->field_name, field_table_name);
           DBUG_RETURN(TRUE);
         }
       }
diff --git a/sql/table.cc b/sql/table.cc
index beecd6442e8..5983b9afda7 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -2289,7 +2289,6 @@ Natural_join_column::Natural_join_column(Field_translator *field_param,
   table_field= NULL;
   table_ref= tab;
   is_common= FALSE;
-  is_coalesced= FALSE;
 }
 
 
@@ -2301,7 +2300,6 @@ Natural_join_column::Natural_join_column(Field *field_param,
   view_field= NULL;
   table_ref= tab;
   is_common= FALSE;
-  is_coalesced= FALSE;
 }
 
 
@@ -2641,13 +2639,6 @@ GRANT_INFO *Field_iterator_table_ref::grant()
 }
 
 
-bool Field_iterator_table_ref::is_coalesced()
-{
-  if (table_ref->is_natural_join)
-    return natural_join_it.column_ref()->is_coalesced;
-  return FALSE;
-}
-
 /*
   Create new or return existing column reference to a column of a
   natural/using join.
diff --git a/sql/table.h b/sql/table.h
index c0e4ad4c150..b6616150610 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -388,12 +388,6 @@ public:
     we are looking at some column.
   */
   bool is_common;
-  /*
-    A column is coalesced if it was common in some of several nested NATURAL/
-    USING joins. We have to know this, because according to ANSI, coalesced
-    columns cannot be qualified. 
-  */
-  bool is_coalesced;
 public:
   Natural_join_column(Field_translator *field_param, st_table_list *tab);
   Natural_join_column(Field *field_param, st_table_list *tab);
@@ -741,7 +735,6 @@ public:
   const char *table_name();
   const char *db_name();
   GRANT_INFO *grant();
-  bool is_coalesced();
   Item *create_item(THD *thd) { return field_it->create_item(thd); }
   Field *field() { return field_it->field(); }
   Natural_join_column *get_or_create_column_ref(THD *thd, bool *is_created);