bk commit into 5.0 tree (timour:1.1957) BUG#13127

[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.1957 05/09/15 16:44:19 timour@stripped +3 -0
  Fix for BUG#13127

  sql/sql_base.cc
    1.301 05/09/15 16:44:16 timour@stripped +32 -25
    - When searching for qualified fields, include views in the test
      that compares the qualifying table with the name of the table
      reference being searched.
    - Do not search in the materialized list of columns of a NATURAL
      join if 'table_list' is a stored table or view. This is a
      performance improvement if 'table_list' is a stored table because
      there is a name hash for such tables.

  mysql-test/t/select.test
    1.78 05/09/15 16:44:16 timour@stripped +30 -0
    Test for BUG#13127.

  mysql-test/r/select.result
    1.93 05/09/15 16:44:15 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.300/sql/sql_base.cc	2005-09-13 21:21:36 +03:00
+++ 1.301/sql/sql_base.cc	2005-09-15 16:44:16 +03:00
@@ -2909,7 +2909,8 @@
     TODO: Ensure that table_name, db_name and tables->db always points to
           something !
   */
-  if (!(table_list->nested_join && table_list->join_columns) &&
+  if ((!(table_list->nested_join && table_list->join_columns) ||
+       table_list->field_translation) &&
       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] &&
@@ -2917,25 +2918,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 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++))
@@ -2960,23 +2984,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.92/mysql-test/r/select.result	2005-09-12 19:18:14 +03:00
+++ 1.93/mysql-test/r/select.result	2005-09-15 16:44:15 +03:00
@@ -2943,3 +2943,72 @@
 c	a	b
 1	1	1
 drop table t1, t2, t3, t4;
+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.77/mysql-test/t/select.test	2005-09-12 19:18:14 +03:00
+++ 1.78/mysql-test/t/select.test	2005-09-15 16:44:16 +03:00
@@ -2523,3 +2523,33 @@
 select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);
 select * from t1 join t2 join t4 using (c);
 drop table t1, t2, t3, t4;
+
+#
+# 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;