1drop table if exists t1,t2,v1,v2,v3; 2drop view if exists t1,t2,v1,v2,v3; 3set @save_query_cache_size=@@global.query_cache_size; 4set @save_query_cache_type=@@global.query_cache_type; 5set GLOBAL query_cache_type=ON; 6set LOCAL query_cache_type=ON; 7set GLOBAL query_cache_size=1355776; 8flush status; 9create table t1 (a int, b int); 10create view v1 (c,d) as select sql_no_cache a,b from t1; 11create view v2 (c,d) as select a+rand(),b from t1; 12show status like "Qcache_queries_in_cache"; 13Variable_name Value 14Qcache_queries_in_cache 0 15show status like "Qcache_inserts"; 16Variable_name Value 17Qcache_inserts 0 18show status like "Qcache_hits"; 19Variable_name Value 20Qcache_hits 0 21select * from v1; 22c d 23select * from v2; 24c d 25show status like "Qcache_queries_in_cache"; 26Variable_name Value 27Qcache_queries_in_cache 0 28show status like "Qcache_inserts"; 29Variable_name Value 30Qcache_inserts 0 31show status like "Qcache_hits"; 32Variable_name Value 33Qcache_hits 0 34select * from v1; 35c d 36select * from v2; 37c d 38show status like "Qcache_queries_in_cache"; 39Variable_name Value 40Qcache_queries_in_cache 0 41show status like "Qcache_inserts"; 42Variable_name Value 43Qcache_inserts 0 44show status like "Qcache_hits"; 45Variable_name Value 46Qcache_hits 0 47drop view v1,v2; 48set query_cache_type=demand; 49flush status; 50create view v1 (c,d) as select sql_cache a,b from t1; 51show status like "Qcache_queries_in_cache"; 52Variable_name Value 53Qcache_queries_in_cache 0 54show status like "Qcache_inserts"; 55Variable_name Value 56Qcache_inserts 0 57show status like "Qcache_hits"; 58Variable_name Value 59Qcache_hits 0 60select * from v1; 61c d 62show status like "Qcache_queries_in_cache"; 63Variable_name Value 64Qcache_queries_in_cache 1 65show status like "Qcache_inserts"; 66Variable_name Value 67Qcache_inserts 1 68show status like "Qcache_hits"; 69Variable_name Value 70Qcache_hits 0 71select * from t1; 72a b 73show status like "Qcache_queries_in_cache"; 74Variable_name Value 75Qcache_queries_in_cache 1 76show status like "Qcache_inserts"; 77Variable_name Value 78Qcache_inserts 1 79show status like "Qcache_hits"; 80Variable_name Value 81Qcache_hits 0 82select * from v1; 83c d 84show status like "Qcache_queries_in_cache"; 85Variable_name Value 86Qcache_queries_in_cache 1 87show status like "Qcache_inserts"; 88Variable_name Value 89Qcache_inserts 1 90show status like "Qcache_hits"; 91Variable_name Value 92Qcache_hits 1 93select * from t1; 94a b 95show status like "Qcache_queries_in_cache"; 96Variable_name Value 97Qcache_queries_in_cache 1 98show status like "Qcache_inserts"; 99Variable_name Value 100Qcache_inserts 1 101show status like "Qcache_hits"; 102Variable_name Value 103Qcache_hits 1 104drop view v1; 105set query_cache_type=default; 106drop table t1; 107create table t1 (a int); 108insert into t1 values (1), (2), (3); 109create view v1 as select a from t1 where a > 1; 110select * from v1; 111a 1122 1133 114alter view v1 as select a from t1 where a > 2; 115select * from v1; 116a 1173 118drop view v1; 119select * from v1; 120ERROR 42S02: Table 'test.v1' doesn't exist 121drop table t1; 122create table t1 (a int, primary key (a), b int); 123create table t2 (a int, primary key (a), b int); 124insert into t2 values (1000, 2000); 125create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; 126select * from v3; 127a b 128drop view v3; 129drop table t1, t2; 130create table t1(f1 int); 131insert into t1 values(1),(2),(3); 132create view v1 as select * from t1; 133set query_cache_wlock_invalidate=1; 134lock tables v1 read /*!32311 local */; 135unlock tables; 136set query_cache_wlock_invalidate=default; 137drop view v1; 138drop table t1; 139flush status; 140create table t1 (a int, b int); 141create algorithm=temptable view v1 as select * from t1; 142select * from v1; 143a b 144show status like "Qcache_queries_in_cache"; 145Variable_name Value 146Qcache_queries_in_cache 1 147show status like "Qcache_inserts"; 148Variable_name Value 149Qcache_inserts 1 150show status like "Qcache_hits"; 151Variable_name Value 152Qcache_hits 0 153select * from v1; 154a b 155show status like "Qcache_queries_in_cache"; 156Variable_name Value 157Qcache_queries_in_cache 1 158show status like "Qcache_inserts"; 159Variable_name Value 160Qcache_inserts 1 161show status like "Qcache_hits"; 162Variable_name Value 163Qcache_hits 1 164insert into t1 values (1,1); 165show status like "Qcache_queries_in_cache"; 166Variable_name Value 167Qcache_queries_in_cache 0 168show status like "Qcache_inserts"; 169Variable_name Value 170Qcache_inserts 1 171show status like "Qcache_hits"; 172Variable_name Value 173Qcache_hits 1 174select * from v1; 175a b 1761 1 177select * from v1; 178a b 1791 1 180show status like "Qcache_queries_in_cache"; 181Variable_name Value 182Qcache_queries_in_cache 1 183show status like "Qcache_inserts"; 184Variable_name Value 185Qcache_inserts 2 186show status like "Qcache_hits"; 187Variable_name Value 188Qcache_hits 2 189drop view v1; 190show status like "Qcache_queries_in_cache"; 191Variable_name Value 192Qcache_queries_in_cache 0 193show status like "Qcache_inserts"; 194Variable_name Value 195Qcache_inserts 2 196show status like "Qcache_hits"; 197Variable_name Value 198Qcache_hits 2 199drop table t1; 200# 201# Bug46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table 202# 203CREATE TABLE t1 (c1 INT, c2 INT); 204CREATE TABLE t2 LIKE t1; 205SET AUTOCOMMIT=OFF; 206CREATE VIEW t1_view AS SELECT c1 FROM t1 NATURAL JOIN t2 ; 207INSERT INTO t1_view (c1, c2) SELECT c1, c2 FROM t1; 208ERROR 42S22: Unknown column 'c2' in 'field list' 209DROP TABLE t1; 210DROP TABLE t2; 211DROP VIEW t1_view; 212SET AUTOCOMMIT=DEFAULT; 213set GLOBAL query_cache_size=@save_query_cache_size; 214set GLOBAL query_cache_type=@save_query_cache_type; 215