Backport of revno: 3673

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.

1 files changed, 56 insertions, 1 deletions

diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test
index fe17bca1eba..e00d63aaedc 100644
--- a/mysql-test/t/ps_ddl.test
+++ b/mysql-test/t/ps_ddl.test
@@ -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 =====================================================================