MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:December 17 2009 1:40pm
Subject:bzr commit into mysql-6.0-codebase-bugfixing branch (jorgen.loland:3778)
Bug#49453
View as plain text  
#At file:///localhome/jl208045/mysql/mysql-6.0-codebase-bugfixing-49453/ based on revid:zhenxing.he@stripped

 3778 Jorgen Loland	2009-12-17
      Bug#49453: "re-execution of prepared statement with view and 
                  semijoin crashes"
      
      There were two bugs:
      1) When semijoin was applied on a view, the replaced items were
         pulled out instead of Item_field since the items referring
         to view columns had already been fixed. However, the semijoin
         nest still stored the item list in sj_subq_pred, and on next
         execution of the PS, this item list was reset during parsing.
         Since this list is not attached to the query, Item_fields 
         referring to view columns were not replaced with 
         Item_direct_view_ref as would happen in normal execution. 
         The fix for this is to reresolve views pulled out to semijoin
         nests on next execute.
      2) After pulling views out to semijoin nests, fix_after_pullout
         is called on all the items. However, this call did not 
         propagate to items referred to from views because 
         Item_ref::fix_after_pullout() did only recursively call this
         method on it's children if the item had depended_from 
         set to the select lex of the semijoin nest. The fix is to 
         call fix_after_pullout() on the children even if the view 
         was not dependent on that select lex. The Item_ref's 
         depended_from will, e.g., be null if the view is only 
         used inside the subselect.
     @ mysql-test/r/subselect_sj.result
        Added test for BUG#49453
     @ mysql-test/r/subselect_sj_jcl6.result
        Added test for BUG#49453
     @ mysql-test/t/subselect_sj.test
        Added test for BUG#49453
     @ sql/item.cc
        Make Item_ref::fix_after_pullout() call fix_after_pullout() on children even if this Item_ref wasn't dependent on the select lex it has been pulled out to.
     @ sql/item_cmpfunc.cc
        REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing
     @ sql/sql_base.cc
        REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing
     @ sql/sql_class.h
        REVIEWERS DISREGARD. This is patch for bug 48508, which has not yet been merged into 6.0-codebase-bugfixing
     @ sql/sql_select.cc
        Make views pulled out to a semijoin-nest be reresolved on next PS/SP execution.
     @ sql/sql_view.cc
        Resolve views over again if execution of PS/SP requires it. This happens if the view has been pulled out to a semijoin nest.
     @ sql/table.h
        Added variable bool view_needs_new_resolve, used to indicate that a view needs to be resolved on next PS/SP execution even though it was already resolved on a previous execution. This happens if the view has been pulled out to a semijoin nest.

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/r/subselect_sj_jcl6.result
      mysql-test/t/subselect_sj.test
      sql/item.cc
      sql/item_cmpfunc.cc
      sql/sql_base.cc
      sql/sql_class.h
      sql/sql_select.cc
      sql/sql_view.cc
      sql/table.h
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2009-12-11 14:09:23 +0000
+++ b/mysql-test/r/subselect_sj.result	2009-12-17 13:40:35 +0000
@@ -712,3 +712,53 @@ deallocate prepare stmt;
 drop table t1, t2;
 drop view v1;
 # End of Bug#49097
+#
+# Bug#49453: re-execution of prepared statement with view and 
+#            semijoin crashes
+#
+CREATE TABLE t1 (
+city VARCHAR(50) NOT NULL,
+country_id SMALLINT UNSIGNED NOT NULL
+);
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+CREATE TABLE t2 (
+country_id SMALLINT UNSIGNED NOT NULL,
+country VARCHAR(50) NOT NULL
+);
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(2,'AlgeriaDup'),
+(3,'XAmerican Samoa') ;
+CREATE VIEW v1 AS 
+SELECT country_id as vf_country_id
+FROM t2
+WHERE LEFT(country,1) = "A";
+
+PREPARE stmt FROM "
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (SELECT vf_country_id FROM v1);
+";
+
+EXECUTE stmt;
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+
+EXECUTE stmt;
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+# End of Bug#49453

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2009-12-11 14:09:23 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2009-12-17 13:40:35 +0000
@@ -716,6 +716,56 @@ deallocate prepare stmt;
 drop table t1, t2;
 drop view v1;
 # End of Bug#49097
+#
+# Bug#49453: re-execution of prepared statement with view and 
+#            semijoin crashes
+#
+CREATE TABLE t1 (
+city VARCHAR(50) NOT NULL,
+country_id SMALLINT UNSIGNED NOT NULL
+);
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+CREATE TABLE t2 (
+country_id SMALLINT UNSIGNED NOT NULL,
+country VARCHAR(50) NOT NULL
+);
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(2,'AlgeriaDup'),
+(3,'XAmerican Samoa') ;
+CREATE VIEW v1 AS 
+SELECT country_id as vf_country_id
+FROM t2
+WHERE LEFT(country,1) = "A";
+
+PREPARE stmt FROM "
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (SELECT vf_country_id FROM v1);
+";
+
+EXECUTE stmt;
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+
+EXECUTE stmt;
+city	country_id
+Batna	2
+Bchar	2
+Skikda	2
+Algeria	2
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+# End of Bug#49453
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2009-12-11 14:09:23 +0000
+++ b/mysql-test/t/subselect_sj.test	2009-12-17 13:40:35 +0000
@@ -630,3 +630,52 @@ drop table t1, t2;
 drop view v1;
 
 --echo # End of Bug#49097
+
+--echo #
+--echo # Bug#49453: re-execution of prepared statement with view and 
+--echo #            semijoin crashes
+--echo #
+CREATE TABLE t1 (
+  city VARCHAR(50) NOT NULL,
+  country_id SMALLINT UNSIGNED NOT NULL
+);
+
+INSERT INTO t1 VALUES 
+('Batna',2),
+('Bchar',2),
+('Skikda',2),
+('Tafuna',3),
+('Algeria',2) ;
+
+CREATE TABLE t2 (
+  country_id SMALLINT UNSIGNED NOT NULL,
+  country VARCHAR(50) NOT NULL
+);
+
+INSERT INTO t2 VALUES
+(2,'Algeria'),
+(2,'AlgeriaDup'),
+(3,'XAmerican Samoa') ;
+
+CREATE VIEW v1 AS 
+  SELECT country_id as vf_country_id
+  FROM t2
+  WHERE LEFT(country,1) = "A"; 
+
+--echo
+PREPARE stmt FROM "
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (SELECT vf_country_id FROM v1);
+";
+
+--echo 
+EXECUTE stmt;
+--echo
+EXECUTE stmt;
+--echo
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+
+--echo # End of Bug#49453

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2009-12-16 09:53:09 +0000
+++ b/sql/item.cc	2009-12-17 13:40:35 +0000
@@ -6679,11 +6679,9 @@ void Item_outer_ref::fix_after_pullout(s
 
 void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
 {
+  (*ref)->fix_after_pullout(new_parent, ref);
   if (depended_from == new_parent)
-  {
-    (*ref)->fix_after_pullout(new_parent, ref);
     depended_from= NULL;
-  }
 }
 
 /**

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-12-15 06:57:50 +0000
+++ b/sql/item_cmpfunc.cc	2009-12-17 13:40:35 +0000
@@ -4394,7 +4394,7 @@ Item *Item_cond::compile(Item_analyzer a
     uchar *arg_v= *arg_p;
     Item *new_item= item->compile(analyzer, &arg_v, transformer, arg_t);
     if (new_item && new_item != item)
-      li.replace(new_item);
+      current_thd->change_item_tree(li.ref(), new_item);
   }
   return Item_func::transform(transformer, arg_t);
 }

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2009-12-17 09:02:50 +0000
+++ b/sql/sql_base.cc	2009-12-17 13:40:35 +0000
@@ -5485,7 +5485,8 @@ find_field_in_view(THD *thd, TABLE_LIST 
     if (!my_strcasecmp(system_charset_info, field_it.name(), name))
     {
       // in PS use own arena or data will be freed after prepare
-      if (register_tree_change && thd->stmt_arena->is_stmt_prepare_or_first_sp_execute())
+      if (register_tree_change &&
+          thd->stmt_arena->is_stmt_prepare_or_first_stmt_execute())
         arena= thd->activate_stmt_arena_if_needed(&backup);
       /*
         create_item() may, or may not create a new Item, depending on

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2009-12-17 09:02:50 +0000
+++ b/sql/sql_class.h	2009-12-17 13:40:35 +0000
@@ -605,6 +605,8 @@ public:
   { return state == INITIALIZED_FOR_SP; }
   inline bool is_stmt_prepare_or_first_sp_execute() const
   { return (int)state < (int)PREPARED; }
+  inline bool is_stmt_prepare_or_first_stmt_execute() const
+  { return (int)state <= (int)PREPARED; }
   inline bool is_first_stmt_execute() const { return state == PREPARED; }
   inline bool is_stmt_execute() const
   { return state == PREPARED || state == EXECUTED; }

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-12-16 09:53:09 +0000
+++ b/sql/sql_select.cc	2009-12-17 13:40:35 +0000
@@ -3374,6 +3374,21 @@ bool convert_subq_to_sj(JOIN *parent_joi
     tl->embedding= sj_nest;
     tl->join_list= &nested_join->join_list;
     nested_join->join_list.push_back(tl);
+
+    /*
+      As part of already executed Item_field::fix_field(), an item
+      referring to a view column has been replaced with an
+      Item_direct_view_ref. The replaced item is pulled out to the
+      sj_nest. However, the sj_nest->sj_subq_pred also stores the
+      items. This list is rebuilt as part of next PS/SP execution's
+      parse phase. View items will then be uninitialized Item_fields.
+      Since this list is not linked to the query, the items will not
+      be fixed. view_needs_new_resolve ensures that all view
+      TABLE-LISTs that are pulled out are resolved and replaced with
+      Item_direct_view_ref again in next execution.
+    */
+    if (tl->view)
+      tl->view_needs_new_resolve= TRUE;
   }
   
   /*

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	2009-12-17 09:02:50 +0000
+++ b/sql/sql_view.cc	2009-12-17 13:40:35 +0000
@@ -1038,7 +1038,7 @@ bool mysql_make_view(THD *thd, File_pars
   DBUG_ENTER("mysql_make_view");
   DBUG_PRINT("info", ("table: %p (%s)", table, table->table_name));
 
-  if (table->view)
+  if (table->view && !table->view_needs_new_resolve)
   {
     /*
       It's an execution of a PS/SP and the view has already been unfolded
@@ -1059,6 +1059,7 @@ bool mysql_make_view(THD *thd, File_pars
                 table->view_db.str, table->view_name.str));
     DBUG_RETURN(0);
   }
+  table->view_needs_new_resolve= FALSE;
 
   if (table->index_hints && table->index_hints->elements)
   {

=== modified file 'sql/table.h'
--- a/sql/table.h	2009-12-15 06:57:50 +0000
+++ b/sql/table.h	2009-12-17 13:40:35 +0000
@@ -1148,6 +1148,7 @@ struct TABLE_LIST
     alias= (char*) alias_arg;
     lock_type= lock_type_arg;
     mdl_request.init(MDL_key::TABLE, db, table_name, MDL_SHARED);
+    view_needs_new_resolve= FALSE;
   }
 
   /*
@@ -1265,6 +1266,8 @@ struct TABLE_LIST
   /* link to select_lex where this table was used */
   st_select_lex	*select_lex;
   LEX *view;                    /* link on VIEW lex for merging */
+  /* TRUE if view needs to be resolved again on next PS/SP execution */
+  bool view_needs_new_resolve;    
   Field_translator *field_translation;	/* array of VIEW fields */
   /* pointer to element after last one in translation table above */
   Field_translator *field_translation_end;


Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20091217134035-a5vctnfnrx8cjqh3.bundle
Thread
bzr commit into mysql-6.0-codebase-bugfixing branch (jorgen.loland:3778)Bug#49453Jorgen Loland17 Dec