Edit File: query_cache_with_views.result
drop table if exists t1,t2,v1,v2,v3; drop view if exists t1,t2,v1,v2,v3; set GLOBAL query_cache_size=1355776; flush status; create table t1 (a int, b int); 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"; Variable_name Value Qcache_queries_in_cache 0 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 0 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 select * from v1; c d select * from v2; c d show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 0 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 select * from v1; c d select * from v2; c d show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 0 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 drop view v1,v2; set query_cache_type=demand; flush status; create view v1 (c,d) as select sql_cache a,b from t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 0 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 select * from v1; c d show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 select * from t1; a b show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 select * from v1; c d show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 select * from t1; a b show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 drop view v1; set query_cache_type=default; drop table t1; 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; a 2 3 alter view v1 as select a from t1 where a > 2; select * from v1; a 3 drop view v1; select * from v1; ERROR 42S02: Table 'test.v1' doesn't exist drop table t1; 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; a b drop view v3; drop table t1, t2; 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; flush status; create table t1 (a int, b int); create algorithm=temptable view v1 as select * from t1; select * from v1; a b show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 select * from v1; a b show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 insert into t1 values (1,1); show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 select * from v1; a b 1 1 select * from v1; a b 1 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 2 show status like "Qcache_hits"; Variable_name Value Qcache_hits 2 drop view v1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 2 show status like "Qcache_hits"; Variable_name Value Qcache_hits 2 drop table t1; # # Bug46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table # 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 ; INSERT INTO t1_view (c1, c2) SELECT c1, c2 FROM t1; ERROR 42S22: Unknown column 'c2' in 'field list' DROP TABLE t1; DROP TABLE t2; DROP VIEW t1_view; SET AUTOCOMMIT=DEFAULT; set GLOBAL query_cache_size=default;
Back to File Manager