bzr commit into mysql-5.0-bugteam branch (kgeorge:2707) Bug#33811

[Georgi Kodinov]

#At file:///home/kgeorge/mysql/bzr/B33811-5.0-bugteam/

 2707 Georgi Kodinov	2008-10-17
      Bug #33811: Call to stored procedure with SELECT * / RIGHT JOIN 
      fails after the first time
        
      Two separate problems : 
        1. When flattening joins the linked list used for name resolution 
        (next_name_resolution_table) was not updated.
        Fixed by updating the pointers when extending the table list
        
        2. The items created by expanding a * (star) as a column reference
        were marked as fixed, but no cached table was assigned to them 
        (unlike what Item_field::fix_fields does).
        Fixed by assigning a cached table (so the re-preparation is done
        faster).
        
      Note that the fix for #2 hides the fix for #1 in most cases
      (except when a table reference cannot be cached).
modified:
  mysql-test/r/sp.result
  mysql-test/t/sp.test
  sql/sql_base.cc
  sql/sql_select.cc

per-file messages:
  mysql-test/r/sp.result
    Bug #33811: test case
  mysql-test/t/sp.test
    Bug #33811: test case
  sql/sql_base.cc
    Bug #33811: cache the table for Item_fields created by expanding '*'
  sql/sql_select.cc
    Bug #33811: maintain a correct name resolution chain when
    flattening joins.
=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result	2008-10-14 14:04:36 +0000
+++ b/mysql-test/r/sp.result	2008-10-17 14:55:06 +0000
@@ -6646,6 +6646,24 @@ ttt
 2
 drop function func30787;
 drop table t1;
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+CREATE PROCEDURE test_sp()
+SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id;
+CALL test_sp();
+id
+1
+2
+3
+4
+CALL test_sp();
+id
+1
+2
+3
+4
+DROP PROCEDURE test_sp;
+DROP TABLE t1;
 create table t1(c1 INT);
 create function f1(p1 int) returns varchar(32)
 return 'aaa';

=== modified file 'mysql-test/t/sp.test'
--- a/mysql-test/t/sp.test	2008-10-14 14:04:36 +0000
+++ b/mysql-test/t/sp.test	2008-10-17 14:55:06 +0000
@@ -7793,6 +7793,21 @@ select (select func30787(f1)) as ttt fro
 drop function func30787;
 drop table t1;
 
+#
+# Bug #33811: Call to stored procedure with SELECT * / RIGHT JOIN fails
+# after the first time
+#
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+
+CREATE PROCEDURE test_sp()
+  SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id;
+
+CALL test_sp();
+CALL test_sp();
+
+DROP PROCEDURE test_sp;
+DROP TABLE t1;
 
 #
 # Bug#38291 memory corruption and server crash with view/sp/function

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2008-10-07 21:34:00 +0000
+++ b/sql/sql_base.cc	2008-10-17 14:55:06 +0000
@@ -5508,6 +5508,10 @@ insert_fields(THD *thd, Name_resolution_
 
       if (!(item= field_iterator.create_item(thd)))
         DBUG_RETURN(TRUE);
+      DBUG_ASSERT(item->fixed);
+      /* cache the table for the Item_fields inserted by expanding stars */
+      if (item->type() == Item::FIELD_ITEM && tables->cacheable_table)
+        ((Item_field *)item)->cached_table= tables;
 
       if (!found)
       {

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-10 10:13:12 +0000
+++ b/sql/sql_select.cc	2008-10-17 14:55:06 +0000
@@ -8268,6 +8268,8 @@ simplify_joins(JOIN *join, List<TABLE_LI
   }
     
   /* Flatten nested joins that can be flattened. */
+  TABLE_LIST *right_neighbor= NULL;
+  bool fix_name_res= FALSE;
   li.rewind();
   while ((table= li++))
   {
@@ -8280,9 +8282,17 @@ simplify_joins(JOIN *join, List<TABLE_LI
       {
         tbl->embedding= table->embedding;
         tbl->join_list= table->join_list;
-      }      
+      }
       li.replace(nested_join->join_list);
-    }
+      /* Need to update the name resolution table chain when flattening joins */
+      fix_name_res= TRUE;
+      table= *li.ref();
+    }
+    if (fix_name_res)
+      table->next_name_resolution_table= right_neighbor ?
+        right_neighbor->first_leaf_for_name_resolution() :
+        NULL;
+    right_neighbor= table;
   }
   DBUG_RETURN(conds); 
 }