bzr commit into mysql-6.0-codebase-bugfixing branch (jon.hauglid:3673) Bug#47313

[Jon Olav Hauglid]

#At file:///export/home/z/mysql-6.0-codebase-bugfixing-bug47313/ based on revid:lars-erik.bjork@stripped

 3673 Jon Olav Hauglid	2009-10-26
      Bug #47313 assert in check_key_in_view during CALL procedure
      
      View definitions are inlined in a stored procedure when the procedure
      is fist called. This means that if a temporary table is later added
      with the same name as the view, the stored procedure will still
      use the view. This happens even if temporary tables normally shadow
      base tables/views.
      
      The reason for the assert was that even if the stored procedure
      referenced the view, open_table() still tried to open the
      temporary table. This "half view/half temporary table" state
      caused the assert.
      
      The bug was not present in 5.1 as open_table() is not called
      for the view there. This code was changed with the introduction 
      of MDL in order to properly lock the view and any objects it 
      refers to.
      
      This patch fixes the problem by instructing open_table()
      to open base tables/views (using OT_BASE_ONLY) when reopening
      tables/views used by stored procedures. This also means that
      a prepared statement is no longer invalidated if a temporary
      table is created with the same name as a view used in the
      prepared statement.
      
      Test case added to sp.test. The test case also demonstrates
      the effect of sp cache invalidation between CALLs.
     @ mysql-test/t/ps_ddl.test
        Extended the VIEW->TEMPORARY TABLE transition test to cover not only
        merged views, but now also materialized views and views containing
        a reference to an information schema table. 
        
        Test also updated to reflect the change to prepared statement
        invalidatation.

    modified:
      mysql-test/r/ps_ddl.result
      mysql-test/r/sp.result
      mysql-test/t/ps_ddl.test
      mysql-test/t/sp.test
      sql/sql_view.cc
=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result	2009-10-12 09:08:34 +0000
+++ b/mysql-test/r/ps_ddl.result	2009-10-26 16:16:48 +0000
@@ -707,6 +707,9 @@ deallocate prepare stmt;
 =====================================================================
 Part 16: VIEW -> TEMPORARY TABLE transitions
 =====================================================================
+#
+# Test 1: Merged view
+#
 create table t2 (a int);
 insert into t2 (a) values (1);
 create view t1 as select * from t2;
@@ -720,18 +723,74 @@ SUCCESS
 create temporary table t1 (a int);
 execute stmt;
 a
-call p_verify_reprepare_count(1);
+1
+call p_verify_reprepare_count(0);
 SUCCESS
 
 drop view t1;
 execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop table t2;
+drop temporary table t1;
+deallocate prepare stmt;
+#
+# Test 2: Materialized view
+#
+create table t2 (a int);
+insert into t2 (a) values (1);
+create algorithm = temptable view t1 as select * from t2;
+prepare stmt from "select * from t1";
+execute stmt;
 a
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create temporary table t1 (a int);
+execute stmt;
+a
+1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view t1;
+execute stmt;
+ERROR 42S02: Table 'test.t1' doesn't exist
 call p_verify_reprepare_count(0);
 SUCCESS
 
 drop table t2;
 drop temporary table t1;
 deallocate prepare stmt;
+#
+# Test 3: View referencing an Information schema table
+#
+create view t1 as select table_name from information_schema.views;
+prepare stmt from "select * from t1";
+execute stmt;
+table_name
+t1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+create temporary table t1 (a int);
+execute stmt;
+table_name
+t1
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop view t1;
+execute stmt;
+table_name
+call p_verify_reprepare_count(0);
+SUCCESS
+
+drop temporary table t1;
+deallocate prepare stmt;
 =====================================================================
 Part 17: VIEW -> VIEW (DDL) transitions
 =====================================================================

=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result	2009-10-23 12:22:29 +0000
+++ b/mysql-test/r/sp.result	2009-10-26 16:16:48 +0000
@@ -7072,6 +7072,65 @@ SELECT routine_comment FROM information_
 routine_comment
 12345678901234567890123456789012345678901234567890123456789012345678901234567890
 DROP PROCEDURE p1;
+#
+# Bug #47313 assert in check_key_in_view during CALL procedure
+#
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS t1, t2_unrelated;
+DROP PROCEDURE IF EXISTS p1;
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE VIEW t1 AS SELECT 10 AS f1;
+# t1 refers to the view
+CALL p1(1);
+ERROR HY000: The target table t1 of the INSERT is not insertable-into
+CREATE TEMPORARY TABLE t1 (f1 INT);
+# t1 still refers to the view since it was inlined
+CALL p1(2);
+ERROR HY000: The target table t1 of the INSERT is not insertable-into
+DROP VIEW t1;
+# t1 now refers to the temporary table
+CALL p1(3);
+# Check which values were inserted into the temp table.
+SELECT * FROM t1;
+f1
+3
+DROP TEMPORARY TABLE t1;
+DROP PROCEDURE p1;
+# Now test what happens if the sp cache is invalidated.
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE VIEW t1 AS SELECT 10 AS f1;
+CREATE VIEW v2_unrelated AS SELECT 1 AS r1;
+# Load the procedure into the sp cache
+CALL p1(4);
+ERROR HY000: The target table t1 of the INSERT is not insertable-into
+CREATE TEMPORARY TABLE t1 (f1 int);
+ALTER VIEW v2_unrelated AS SELECT 2 AS r1;
+# Alter view causes the sp cache to be invalidated.
+# Now t1 refers to the temporary table, not the view.
+CALL p1(5);
+# Check which values were inserted into the temp table.
+SELECT * FROM t1;
+f1
+5
+DROP TEMPORARY TABLE t1;
+DROP VIEW t1, v2_unrelated;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE TEMPORARY TABLE t1 (f1 INT);
+# t1 refers to the temporary table
+CALL p1(6);
+CREATE VIEW t1 AS SELECT 10 AS f1;
+# Create view causes the sp cache to be invalidated.
+# t1 still refers to the temporary table since it shadows the view.
+CALL p1(7);
+DROP VIEW t1;
+# Check which values were inserted into the temp table.
+SELECT * FROM t1;
+f1
+6
+7
+DROP TEMPORARY TABLE t1;
+DROP PROCEDURE p1;
 # ------------------------------------------------------------------
 # -- End of 6.0 tests
 # ------------------------------------------------------------------

=== modified file 'mysql-test/t/ps_ddl.test'
--- a/mysql-test/t/ps_ddl.test	2009-10-12 09:08:34 +0000
+++ b/mysql-test/t/ps_ddl.test	2009-10-26 16:16:48 +0000
@@ -642,6 +642,9 @@ deallocate prepare stmt;
 --echo Part 16: VIEW -> TEMPORARY TABLE transitions
 --echo =====================================================================
 
+--echo #
+--echo # Test 1: Merged view
+--echo #
 create table t2 (a int);
 insert into t2 (a) values (1);
 create view t1 as select * from t2;
@@ -651,9 +654,39 @@ execute stmt;
 call p_verify_reprepare_count(0);
 
 create temporary table t1 (a int);
+# t1 still refers to the view - no reprepare has been done.
 execute stmt;
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
+
+drop view t1;
+# t1 still refers to the, now deleted, view - no reprepare has been done.
+--error ER_NO_SUCH_TABLE
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop table t2;
+drop temporary table t1;
+deallocate prepare stmt;
+
+--echo #
+--echo # Test 2: Materialized view
+--echo #
+create table t2 (a int);
+insert into t2 (a) values (1);
+create algorithm = temptable view t1 as select * from t2;
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+create temporary table t1 (a int);
+# t1 still refers to the view - no reprepare has been done.
+execute stmt;
+call p_verify_reprepare_count(0);
+
 drop view t1;
+# t1 still refers to the, now deleted, view - no reprepare has been done.
+--error ER_NO_SUCH_TABLE
 execute stmt;
 call p_verify_reprepare_count(0);
 
@@ -661,6 +694,28 @@ drop table t2;
 drop temporary table t1;
 deallocate prepare stmt;
 
+--echo #
+--echo # Test 3: View referencing an Information schema table
+--echo #
+create view t1 as select table_name from information_schema.views;
+
+prepare stmt from "select * from t1";
+execute stmt;
+call p_verify_reprepare_count(0);
+
+create temporary table t1 (a int);
+# t1 has been substituted with a reference to the IS table
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop view t1;
+# Since the IS table has been substituted in, the statement still works
+execute stmt;
+call p_verify_reprepare_count(0);
+
+drop temporary table t1;
+deallocate prepare stmt;
+
 --echo =====================================================================
 --echo Part 17: VIEW -> VIEW (DDL) transitions
 --echo =====================================================================

=== modified file 'mysql-test/t/sp.test'
--- a/mysql-test/t/sp.test	2009-10-23 12:22:29 +0000
+++ b/mysql-test/t/sp.test	2009-10-26 16:16:48 +0000
@@ -8423,6 +8423,86 @@ SELECT routine_comment FROM information_
 
 DROP PROCEDURE p1;
 
+
+--echo #
+--echo # Bug #47313 assert in check_key_in_view during CALL procedure
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS t1, t2_unrelated;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE VIEW t1 AS SELECT 10 AS f1;
+
+--echo # t1 refers to the view
+--error ER_NON_INSERTABLE_TABLE
+CALL p1(1);
+
+CREATE TEMPORARY TABLE t1 (f1 INT);
+
+--echo # t1 still refers to the view since it was inlined
+--error ER_NON_INSERTABLE_TABLE
+CALL p1(2);
+
+DROP VIEW t1;
+
+--echo # t1 now refers to the temporary table
+CALL p1(3);
+
+--echo # Check which values were inserted into the temp table.
+SELECT * FROM t1;
+
+DROP TEMPORARY TABLE t1;
+DROP PROCEDURE p1;
+
+--echo # Now test what happens if the sp cache is invalidated.
+
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE VIEW t1 AS SELECT 10 AS f1;
+CREATE VIEW v2_unrelated AS SELECT 1 AS r1;
+
+--echo # Load the procedure into the sp cache
+--error ER_NON_INSERTABLE_TABLE
+CALL p1(4);
+
+CREATE TEMPORARY TABLE t1 (f1 int);
+
+ALTER VIEW v2_unrelated AS SELECT 2 AS r1;
+
+--echo # Alter view causes the sp cache to be invalidated.
+--echo # Now t1 refers to the temporary table, not the view.
+CALL p1(5);
+
+--echo # Check which values were inserted into the temp table.
+SELECT * FROM t1;
+
+DROP TEMPORARY TABLE t1;
+DROP VIEW t1, v2_unrelated;
+DROP PROCEDURE p1;
+
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE TEMPORARY TABLE t1 (f1 INT);
+
+--echo # t1 refers to the temporary table
+CALL p1(6);
+
+CREATE VIEW t1 AS SELECT 10 AS f1;
+
+--echo # Create view causes the sp cache to be invalidated.
+--echo # t1 still refers to the temporary table since it shadows the view.
+CALL p1(7);
+
+DROP VIEW t1;
+
+--echo # Check which values were inserted into the temp table.
+SELECT * FROM t1;
+
+DROP TEMPORARY TABLE t1;
+DROP PROCEDURE p1;
+
 --echo # ------------------------------------------------------------------
 --echo # -- End of 6.0 tests
 --echo # ------------------------------------------------------------------

=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc	2009-10-23 06:24:37 +0000
+++ b/sql/sql_view.cc	2009-10-26 16:16:48 +0000
@@ -1146,10 +1146,23 @@ bool mysql_make_view(THD *thd, File_pars
   table->view_db.length= table->db_length;
   table->view_name.str= table->table_name;
   table->view_name.length= table->table_name_length;
+  /*
+    We don't invalidate a prepared statement when a view changes,
+    or when someone creates a temporary table.
+    Instead, the view is inlined into the body of the statement
+    upon the first execution. Below, make sure that on
+    re-execution of a prepared statement we don't prefer
+    a temporary table to the view, if the view name was shadowed
+    with a temporary table with the same name.
+    This assignment ensures that on re-execution open_table() will
+    not try to call find_temporary_table() for this TABLE_LIST,
+    but will invoke open_table_from_share(), which will
+    eventually call this function.
+  */
+  table->open_type= OT_BASE_ONLY;
 
   /*TODO: md5 test here and warning if it is differ */
 
-
   /*
     TODO: TABLE mem root should be used here when VIEW will be stored in
     TABLE cache


Attachment: [text/bzr-bundle] bzr/[e-mail]