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