diff options
Diffstat (limited to 'mysql-test/t/query_cache_with_views.test')
-rw-r--r-- | mysql-test/t/query_cache_with_views.test | 130 |
1 files changed, 130 insertions, 0 deletions
diff --git a/mysql-test/t/query_cache_with_views.test b/mysql-test/t/query_cache_with_views.test new file mode 100644 index 00000000000..d4ebe45b7ac --- /dev/null +++ b/mysql-test/t/query_cache_with_views.test @@ -0,0 +1,130 @@ +-- source include/have_query_cache.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 GLOBAL query_cache_size=1355776; +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"; + +drop view v1,v2; + +# SQL_CACHE option +set query_cache_type=demand; +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; + +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. +# +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; + +# Reset default environment. +set GLOBAL query_cache_size=default; |