diff options
Diffstat (limited to 'mysql-test/main/query_cache_with_views.test')
| -rw-r--r-- | mysql-test/main/query_cache_with_views.test | 160 |
1 files changed, 160 insertions, 0 deletions
diff --git a/mysql-test/main/query_cache_with_views.test b/mysql-test/main/query_cache_with_views.test new file mode 100644 index 00000000..6740c708 --- /dev/null +++ b/mysql-test/main/query_cache_with_views.test @@ -0,0 +1,160 @@ +-- source include/have_query_cache.inc +-- source include/no_view_protocol.inc +# +# QUERY CACHE options for VIEWs +# +--disable_warnings +drop table if exists t1,t2,v1,v2,v3; +drop view if exists t1,t2,v1,v2,v3; +--enable_warnings + +set @save_query_cache_size=@@global.query_cache_size; +set @save_query_cache_type=@@global.query_cache_type; +set GLOBAL query_cache_type=ON; +set LOCAL query_cache_type=ON; +set GLOBAL query_cache_size=1355776; + +--disable_ps2_protocol +flush status; +create table t1 (a int, b int); + +# queries with following views should not be in query cache +create view v1 (c,d) as select sql_no_cache a,b from t1; +create view v2 (c,d) as select a+rand(),b from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +select * from v2; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +select * from v2; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +--enable_ps2_protocol + +drop view v1,v2; + +# SQL_CACHE option +set query_cache_type=demand; +--disable_ps2_protocol +flush status; +# query with view will be cached, but direct acess to table will not +create view v1 (c,d) as select sql_cache a,b from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from t1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +drop view v1; +set query_cache_type=default; +--enable_ps2_protocol + +drop table t1; + +# +# invalidation of view +# +create table t1 (a int); +insert into t1 values (1), (2), (3); +create view v1 as select a from t1 where a > 1; +select * from v1; +alter view v1 as select a from t1 where a > 2; +select * from v1; +drop view v1; +-- error 1146 +select * from v1; +drop table t1; + +# +# join view with QC +# +create table t1 (a int, primary key (a), b int); +create table t2 (a int, primary key (a), b int); +insert into t2 values (1000, 2000); +create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; +select * from v3; +drop view v3; +drop table t1, t2; + +# +# Bug #13424 locking view with query cache enabled crashes server +# +create table t1(f1 int); +insert into t1 values(1),(2),(3); +create view v1 as select * from t1; +set query_cache_wlock_invalidate=1; +lock tables v1 read /*!32311 local */; +unlock tables; +set query_cache_wlock_invalidate=default; +drop view v1; +drop table t1; + +# +# BUG#15119: returning temptable view from the query cache. +# +--disable_ps2_protocol +flush status; +create table t1 (a int, b int); +create algorithm=temptable view v1 as select * from t1; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +insert into t1 values (1,1); +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +select * from v1; +select * from v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +drop view v1; +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +drop table t1; +--enable_ps2_protocol + +--echo # +--echo # Bug46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table +--echo # +CREATE TABLE t1 (c1 INT, c2 INT); +CREATE TABLE t2 LIKE t1; +SET AUTOCOMMIT=OFF; +CREATE VIEW t1_view AS SELECT c1 FROM t1 NATURAL JOIN t2 ; +# Before the bug patch the below INSERT stmt used to +# crash when other fields than the ones listed in the +# view definition were used. +--error ER_BAD_FIELD_ERROR +INSERT INTO t1_view (c1, c2) SELECT c1, c2 FROM t1; +DROP TABLE t1; +DROP TABLE t2; +DROP VIEW t1_view; +SET AUTOCOMMIT=DEFAULT; + +# Reset default environment. +set GLOBAL query_cache_size=@save_query_cache_size; +set GLOBAL query_cache_type=@save_query_cache_type; |