1-- source include/have_query_cache.inc 2# 3# QUERY CACHE options for VIEWs 4# 5--disable_warnings 6drop table if exists t1,t2,v1,v2,v3; 7drop view if exists t1,t2,v1,v2,v3; 8--enable_warnings 9 10set @save_query_cache_size=@@global.query_cache_size; 11set @save_query_cache_type=@@global.query_cache_type; 12set GLOBAL query_cache_type=ON; 13set LOCAL query_cache_type=ON; 14set GLOBAL query_cache_size=1355776; 15flush status; 16create table t1 (a int, b int); 17 18# queries with following views should not be in query cache 19create view v1 (c,d) as select sql_no_cache a,b from t1; 20create view v2 (c,d) as select a+rand(),b from t1; 21show status like "Qcache_queries_in_cache"; 22show status like "Qcache_inserts"; 23show status like "Qcache_hits"; 24select * from v1; 25select * from v2; 26show status like "Qcache_queries_in_cache"; 27show status like "Qcache_inserts"; 28show status like "Qcache_hits"; 29select * from v1; 30select * from v2; 31show status like "Qcache_queries_in_cache"; 32show status like "Qcache_inserts"; 33show status like "Qcache_hits"; 34 35drop view v1,v2; 36 37# SQL_CACHE option 38set query_cache_type=demand; 39flush status; 40# query with view will be cached, but direct acess to table will not 41create view v1 (c,d) as select sql_cache a,b from t1; 42show status like "Qcache_queries_in_cache"; 43show status like "Qcache_inserts"; 44show status like "Qcache_hits"; 45select * from v1; 46show status like "Qcache_queries_in_cache"; 47show status like "Qcache_inserts"; 48show status like "Qcache_hits"; 49select * from t1; 50show status like "Qcache_queries_in_cache"; 51show status like "Qcache_inserts"; 52show status like "Qcache_hits"; 53select * from v1; 54show status like "Qcache_queries_in_cache"; 55show status like "Qcache_inserts"; 56show status like "Qcache_hits"; 57select * from t1; 58show status like "Qcache_queries_in_cache"; 59show status like "Qcache_inserts"; 60show status like "Qcache_hits"; 61drop view v1; 62set query_cache_type=default; 63 64drop table t1; 65 66# 67# invalidation of view 68# 69create table t1 (a int); 70insert into t1 values (1), (2), (3); 71create view v1 as select a from t1 where a > 1; 72select * from v1; 73alter view v1 as select a from t1 where a > 2; 74select * from v1; 75drop view v1; 76-- error 1146 77select * from v1; 78drop table t1; 79 80# 81# join view with QC 82# 83create table t1 (a int, primary key (a), b int); 84create table t2 (a int, primary key (a), b int); 85insert into t2 values (1000, 2000); 86create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; 87select * from v3; 88drop view v3; 89drop table t1, t2; 90 91# 92# Bug #13424 locking view with query cache enabled crashes server 93# 94create table t1(f1 int); 95insert into t1 values(1),(2),(3); 96create view v1 as select * from t1; 97set query_cache_wlock_invalidate=1; 98lock tables v1 read /*!32311 local */; 99unlock tables; 100set query_cache_wlock_invalidate=default; 101drop view v1; 102drop table t1; 103 104# 105# BUG#15119: returning temptable view from the query cache. 106# 107flush status; 108create table t1 (a int, b int); 109create algorithm=temptable view v1 as select * from t1; 110select * from v1; 111show status like "Qcache_queries_in_cache"; 112show status like "Qcache_inserts"; 113show status like "Qcache_hits"; 114select * from v1; 115show status like "Qcache_queries_in_cache"; 116show status like "Qcache_inserts"; 117show status like "Qcache_hits"; 118insert into t1 values (1,1); 119show status like "Qcache_queries_in_cache"; 120show status like "Qcache_inserts"; 121show status like "Qcache_hits"; 122select * from v1; 123select * from v1; 124show status like "Qcache_queries_in_cache"; 125show status like "Qcache_inserts"; 126show status like "Qcache_hits"; 127drop view v1; 128show status like "Qcache_queries_in_cache"; 129show status like "Qcache_inserts"; 130show status like "Qcache_hits"; 131drop table t1; 132 133--echo # 134--echo # Bug46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table 135--echo # 136CREATE TABLE t1 (c1 INT, c2 INT); 137CREATE TABLE t2 LIKE t1; 138SET AUTOCOMMIT=OFF; 139CREATE VIEW t1_view AS SELECT c1 FROM t1 NATURAL JOIN t2 ; 140# Before the bug patch the below INSERT stmt used to 141# crash when other fields than the ones listed in the 142# view definition were used. 143--error ER_BAD_FIELD_ERROR 144INSERT INTO t1_view (c1, c2) SELECT c1, c2 FROM t1; 145DROP TABLE t1; 146DROP TABLE t2; 147DROP VIEW t1_view; 148SET AUTOCOMMIT=DEFAULT; 149 150# Reset default environment. 151set GLOBAL query_cache_size=@save_query_cache_size; 152set GLOBAL query_cache_type=@save_query_cache_type; 153