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