diff options
| -rw-r--r-- | mysql-test/r/ps_ddl.result | 61 | ||||
| -rw-r--r-- | mysql-test/r/sp.result | 59 | ||||
| -rw-r--r-- | mysql-test/t/ps_ddl.test | 57 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 81 | ||||
| -rw-r--r-- | sql/sql_view.cc | 14 |
5 files changed, 270 insertions, 2 deletions
diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result index 3d57c8f7332..c72d129c8e4 100644 --- a/mysql-test/r/ps_ddl.result +++ b/mysql-test/r/ps_ddl.result @@ -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 ===================================================================== diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index bcc738d695c..08c7831c955 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7076,3 +7076,62 @@ SELECT routine_comment FROM information_schema.routines WHERE routine_name = "p1 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; 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 ===================================================================== diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 7cf2fcf9bb7..a29275eeda4 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8433,3 +8433,84 @@ SELECT routine_comment FROM information_schema.routines WHERE routine_name = "p1 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; + + diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 6d2836afc0d..17ac10ebfb9 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1137,6 +1137,20 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, 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 */ |