-- 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; set GLOBAL query_cache_size=default;