-- 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; --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=default;