MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:timour Date:September 20 2005 11:00am
Subject:bk commit into 5.0 tree (timour:1.1985) BUG#13127
View as plain text  
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.1985 05/09/20 14:00:39 timour@stripped +3 -0
  Fix for BUG#13127.
  
  The problem was in the way table references are pre-filtered when
  resolving a qualified field. When resolving qualified table references
  we search recursively in the operands of the join. If there is
  natural/using join with a merge view, the first call to find_field_in_table_ref
  makes a recursive call to itself with the view as the new table reference
  to search for the column. However the view has both nested_join and
  join_columns != NULL so it skipped the test whether the view name matches
  the field qualifier. As a result the field was found in the view since the
  view already has a field with the same name. Thus the field was incorrectly
  resolved as the view field.

  sql/sql_base.cc
    1.303 05/09/20 14:00:35 timour@stripped +63 -31
    The patch contains two independent changes:
    
    - When searching for qualified fields, include merge views and information schema tables
    used with SHOW (both using TABLE_LIST::field_translation to represent result fields) in
    the test that compares the qualifying table with the name of the table reference being
    searched. This change fixes the bug.
    
    - Do not search the materialized list of columns of a NATURAL/USING join if 'table_list'
    is a stored table or merge view. Instead search directly in the table or view as if it
    is not under a natural join. This is a performance improvement since if 'table_list'
    is a stored table, then the search can utilize the name hash for table names.

  mysql-test/t/select.test
    1.79 05/09/20 14:00:35 timour@stripped +30 -0
    Test for BUG#13127.

  mysql-test/r/select.result
    1.94 05/09/20 14:00:35 timour@stripped +69 -0
    Test for BUG#13127.

# 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-bug-13127

--- 1.302/sql/sql_base.cc	2005-09-15 10:02:14 +03:00
+++ 1.303/sql/sql_base.cc	2005-09-20 14:00:35 +03:00
@@ -2948,6 +2948,18 @@
                                  belongs - differs from 'table_list' only for
                                  NATURAL_USING joins.
 
+  DESCRIPTION
+    Find a field in a table reference depending on the type of table
+    reference. There are three types of table references with respect
+    to the representation of their result columns:
+    - an array of Field_translator objects for MERGE views and some
+      information_schema tables,
+    - an array of Field objects (and possibly a name hash) for stored
+      tables,
+    - a list of Natural_join_column objects for NATURAL/USING joins.
+    This procedure detects the type of the table reference 'table_list'
+    and calls the corresponding search routine.
+
   RETURN
     0			field is not found
     view_ref_found	found value in VIEW (real result is in *ref)
@@ -2971,16 +2983,30 @@
 
   /*
     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 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.
+    are the same as the table reference we are going to search for the field.
+
+    We exclude from the test below NATURAL/USING joins and any nested join
+    that is an operand of NATURAL/USING join, because each column in such
+    joins may potentially originate from a different table. However, base
+    tables and views that are under some NATURAL/USING join are searched
+    as usual base tables/views.
+
+    We include explicitly table references with a 'field_translation' table,
+    because if there are views over natural joins we don't want to search
+    inside the view, but we want to search directly in the view columns
+    which are represented as a 'field_translation'.
+
     TODO: Ensure that table_name, db_name and tables->db always points to
           something !
   */
-  if (!(table_list->nested_join && table_list->join_columns) &&
+  if (/* Exclude natural joins and nested joins underlying natural joins. */
+      (!(table_list->nested_join && table_list->join_columns) ||
+       /* Include merge views and information schema tables. */
+       table_list->field_translation) &&
+      /*
+        Test if the field qualifiers match the table reference we plan
+        to search.
+      */
       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] &&
@@ -2988,25 +3014,48 @@
     DBUG_RETURN(0);
 
   *actual_table= NULL;
+
   if (table_list->field_translation)
   {
+    /* 'table_list' is a view or an information schema table. */
     if ((fld= find_field_in_view(thd, table_list, name, item_name, length,
                                  ref, check_grants_view,
                                  register_tree_change)))
       *actual_table= table_list;
   }
-  else if (table_list->nested_join && table_list->join_columns)
+  else if (!(table_list->nested_join && table_list->join_columns))
+  {
+    /*
+      'table_list' is a stored table. It is so because the only type of nested
+      join passed to this procedure is a NATURAL/USING join or an operand of a
+      NATURAL/USING join.
+    */
+    if ((fld= find_field_in_table(thd, table_list->table, name, length,
+                                  check_grants_table, allow_rowid,
+                                  cached_field_index_ptr)))
+      *actual_table= table_list;
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    /* check for views with temporary table algorithm */
+    if (check_grants_view && table_list->view &&
+        fld && fld != WRONG_GRANT &&
+        check_grant_column(thd, &table_list->grant,
+                           table_list->view_db.str,
+                           table_list->view_name.str,
+                           name, length))
+    fld= WRONG_GRANT;
+#endif
+  }
+  else
   {
     /*
-      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.
+      'table_list' is a NATURAL/USING join, or an operand of such join that
+      is a nested join itself.
+
+      If the field name we search for is qualified, then search for the field
+      in the table references used by NATURAL/USING the join.
     */
     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++))
@@ -3031,23 +3080,6 @@
                                     /* 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,
-                                  check_grants_table, allow_rowid,
-                                  cached_field_index_ptr)))
-      *actual_table= table_list;
-#ifndef NO_EMBEDDED_ACCESS_CHECKS
-    /* check for views with temporary table algorithm */
-    if (check_grants_view && table_list->view &&
-        fld && fld != WRONG_GRANT &&
-        check_grant_column(thd, &table_list->grant,
-                           table_list->view_db.str,
-                           table_list->view_name.str,
-                           name, length))
-    fld= WRONG_GRANT;
-#endif
   }
 
   DBUG_RETURN(fld);

--- 1.93/mysql-test/r/select.result	2005-09-16 00:19:00 +03:00
+++ 1.94/mysql-test/r/select.result	2005-09-20 14:00:35 +03:00
@@ -2960,3 +2960,72 @@
 4	4
 4	5
 drop table t1,t2,t3;
+create table t1 (id char(16) not null default '', primary key  (id));
+insert into t1 values ('100'),('101'),('102');
+create table t2 (id char(16) default null);
+insert into t2 values (1);
+create view v1 as select t1.id from t1;
+create view v2 as select t2.id from t2;
+create view v3 as select (t1.id+2) as id from t1 natural left join t2;
+select t1.id from t1 left join v2 using (id);
+id
+100
+101
+102
+select t1.id from v2 right join t1 using (id);
+id
+100
+101
+102
+select t1.id from t1 left join v3 using (id);
+id
+100
+101
+102
+select * from t1 left join v2 using (id);
+id
+100
+101
+102
+select * from v2 right join t1 using (id);
+id
+100
+101
+102
+select * from t1 left join v3 using (id);
+id
+100
+101
+102
+select v1.id from v1 left join v2 using (id);
+id
+100
+101
+102
+select v1.id from v2 right join v1 using (id);
+id
+100
+101
+102
+select v1.id from v1 left join v3 using (id);
+id
+100
+101
+102
+select * from v1 left join v2 using (id);
+id
+100
+101
+102
+select * from v2 right join v1 using (id);
+id
+100
+101
+102
+select * from v1 left join v3 using (id);
+id
+100
+101
+102
+drop table t1, t2;
+drop view v1, v2, v3;

--- 1.78/mysql-test/t/select.test	2005-09-16 00:18:51 +03:00
+++ 1.79/mysql-test/t/select.test	2005-09-20 14:00:35 +03:00
@@ -2535,3 +2535,33 @@
 insert into t3 values (1), (2), (3), (4), (5);
 select t1.x, t3.x from t1, t2, t3  where t1.x = t2.x and t3.x >= t1.y and t3.x <= t2.y;
 drop table t1,t2,t3;
+
+#
+# Bug #13127 LEFT JOIN against a VIEW returns NULL instead of correct value
+#
+
+create table t1 (id char(16) not null default '', primary key  (id));
+insert into t1 values ('100'),('101'),('102');
+create table t2 (id char(16) default null);
+insert into t2 values (1);
+create view v1 as select t1.id from t1;
+create view v2 as select t2.id from t2;
+create view v3 as select (t1.id+2) as id from t1 natural left join t2;
+
+# all queries must return the same result
+select t1.id from t1 left join v2 using (id);
+select t1.id from v2 right join t1 using (id);
+select t1.id from t1 left join v3 using (id);
+select * from t1 left join v2 using (id);
+select * from v2 right join t1 using (id);
+select * from t1 left join v3 using (id);
+
+select v1.id from v1 left join v2 using (id);
+select v1.id from v2 right join v1 using (id);
+select v1.id from v1 left join v3 using (id);
+select * from v1 left join v2 using (id);
+select * from v2 right join v1 using (id);
+select * from v1 left join v3 using (id);
+
+drop table t1, t2;
+drop view v1, v2, v3;
Thread
bk commit into 5.0 tree (timour:1.1985) BUG#13127timour20 Sep