Debian Codegraph @

mysql-test/suite/gcol/r/gcol_view_myisam.result

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 create table t1 (a int not null,
3 b int generated always as (-a) virtual,
4 c int generated always as (-a) stored);
5 insert into t1 (a) values (1), (1), (2), (2), (3);
6 analyze table t1;
7 Table Op Msg_type Msg_text
8 test.t1 analyze status Engine-independent statistics collected
9 test.t1 analyze status OK
10 create view v1 (d,e) as select abs(b), abs(c) from t1;
11 select d,e from v1;
12 d e
13 1 1
14 1 1
15 2 2
16 2 2
17 3 3
18 select is_updatable from information_schema.views where table_name='v1';
19 is_updatable
20 NO
21 explain select d,e from v1;
22 id select_type table type possible_keys key key_len ref rows Extra
23 1 SIMPLE t1 ALL NULL NULL NULL NULL X
24 create algorithm=temptable view v2 (d,e) as select abs(b), abs(c) from t1;
25 show create view v2;
26 View Create View character_set_client collation_connection
27 v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select abs(`t1`.`b`) AS `d`,abs(`t1`.`c`) AS `e` from `t1` latin1 latin1_swedish_ci
28 select d,e from v2;
29 d e
30 1 1
31 1 1
32 2 2
33 2 2
34 3 3
35 explain select d,e from v2;
36 id select_type table type possible_keys key key_len ref rows Extra
37 1 PRIMARY <derived2> ALL NULL NULL NULL NULL X
38 2 DERIVED t1 ALL NULL NULL NULL NULL X
39 create view v3 (d,e) as select d*2, e*2 from v1;
40 select * from v3;
41 d e
42 2 2
43 2 2
44 4 4
45 4 4
46 6 6
47 explain select * from v3;
48 id select_type table type possible_keys key key_len ref rows Extra
49 1 SIMPLE t1 ALL NULL NULL NULL NULL X
50 drop view v1,v2,v3;
51 drop table t1;
52 create table t1 (a int not null,
53 b int generated always as (-a) virtual,
54 c int generated always as (-a) stored);
55 insert into t1 (a) values (1), (2), (3), (1), (2), (3);
56 create view v1 as select distinct b from t1;
57 select * from v1;
58 b
59 -1
60 -2
61 -3
62 explain select * from v1;
63 id select_type table type possible_keys key key_len ref rows Extra
64 1 PRIMARY <derived2> ALL NULL NULL NULL NULL X
65 2 DERIVED t1 ALL NULL NULL NULL NULL X Using temporary
66 select * from t1;
67 a b c
68 1 -1 -1
69 1 -1 -1
70 2 -2 -2
71 2 -2 -2
72 3 -3 -3
73 3 -3 -3
74 drop view v1;
75 create view v1 as select distinct c from t1;
76 select * from v1;
77 c
78 -1
79 -2
80 -3
81 explain select * from v1;
82 id select_type table type possible_keys key key_len ref rows Extra
83 1 PRIMARY <derived2> ALL NULL NULL NULL NULL X
84 2 DERIVED t1 ALL NULL NULL NULL NULL X Using temporary
85 select * from t1;
86 a b c
87 1 -1 -1
88 1 -1 -1
89 2 -2 -2
90 2 -2 -2
91 3 -3 -3
92 3 -3 -3
93 drop view v1;
94 drop table t1;
95 create table t1 (a int not null,
96 b int generated always as (-a) virtual,
97 c int generated always as (-a) stored);
98 insert into t1 (a) values (1), (2), (3), (4);
99 create view v1 as select b+1 from t1 order by 1 desc limit 2;
100 select * from v1;
101 b+1
102 0
103 -1
104 explain select * from v1;
105 id select_type table type possible_keys key key_len ref rows Extra
106 1 PRIMARY <derived2> ALL NULL NULL NULL NULL X
107 2 DERIVED t1 ALL NULL NULL NULL NULL X Using filesort
108 drop view v1;
109 create view v1 as select c+1 from t1 order by 1 desc limit 2;
110 select * from v1;
111 c+1
112 -1
113 0
114 explain select * from v1;
115 id select_type table type possible_keys key key_len ref rows Extra
116 1 PRIMARY <derived2> ALL NULL NULL NULL NULL X
117 2 DERIVED t1 ALL NULL NULL NULL NULL X Using filesort
118 drop view v1;
119 drop table t1;
120 create table t1 (a int,
121 b int,
122 c int generated always as (-a) virtual,
123 d int generated always as (-a) stored,
124 primary key(a));
125 insert into t1 (a,b) values (10,2), (20,3), (30,4), (40,5), (50,10);
126 create view v1 (a,e,f,g) as select a, b+1,c+1,d+1 from t1;
127 update v1 set a=a+e;
128 select * from v1 order by a;
129 a e f g
130 13 3 -12 -12
131 24 4 -23 -23
132 35 5 -34 -34
133 46 6 -45 -45
134 61 11 -60 -60
135 select * from t1 order by a;
136 a b c d
137 13 2 -13 -13
138 24 3 -24 -24
139 35 4 -35 -35
140 46 5 -46 -46
141 61 10 -61 -61
142 delete from v1;
143 select * from v1;
144 a e f g
145 select * from t1;
146 a b c d
147 insert into v1 (a,e) values (60,15);
148 ERROR HY000: The target table v1 of the INSERT is not insertable-into
149 drop table t1;
150 drop view v1;
151 create table t1 (a int,
152 b int generated always as (-a) virtual,
153 c int generated always as (-a) stored,
154 primary key(a));
155 insert into t1 (a) values (1), (2), (3);
156 create view v1 (x,y,z) as select a,b,c from t1 where b < -1;
157 select t1.a, v1.x, v1.y, v1.z from t1 left join v1 on (t1.b= v1.y);
158 a x y z
159 1 NULL NULL NULL
160 2 2 -2 -2
161 3 3 -3 -3
162 drop view v1;
163 create view v1 (x,y,z) as select a,b,c from t1 where c < -1;
164 select t1.a, v1.x, v1.y, v1.z from t1 left join v1 on (t1.c= v1.z);
165 a x y z
166 1 NULL NULL NULL
167 2 2 -2 -2
168 3 3 -3 -3
169 drop view v1;
170 drop table t1;
171 create table t1 (a1 int,
172 b1 int generated always as (-a1) virtual,
173 c1 int generated always as (-a1) stored);
174 create table t2 (a2 int,
175 b2 int generated always as (-a2) virtual,
176 c2 int generated always as (-a2) stored);
177 insert into t1 (a1) values (1), (2);
178 insert into t2 (a2) values (2), (3);
179 create view v1 as select * from t1,t2 union all select * from t1,t2;
180 select * from v1;
181 a1 b1 c1 a2 b2 c2
182 1 -1 -1 2 -2 -2
183 1 -1 -1 2 -2 -2
184 1 -1 -1 3 -3 -3
185 1 -1 -1 3 -3 -3
186 2 -2 -2 2 -2 -2
187 2 -2 -2 2 -2 -2
188 2 -2 -2 3 -3 -3
189 2 -2 -2 3 -3 -3
190 drop view v1;
191 drop table t1, t2;
192 create table t1 (a int,
193 b int generated always as (-a) virtual,
194 c int generated always as (-a) stored);
195 create table t2 like t1;
196 create view v1 as select a,b,c from t1;
197 create view v2 as select a,b,c from t2 where b in (select b from v1);
198 show create view v2;
199 View Create View character_set_client collation_connection
200 v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from `t2` where `t2`.`b` in (select `v1`.`b` from `v1`) latin1 latin1_swedish_ci
201 drop view v2, v1;
202 drop table t1, t2;
203 create table t1 (a int,
204 b int generated always as (-a) virtual,
205 c int generated always as (-a) stored);
206 insert into t1 (a) values (1),(1),(2),(2),(3),(3);
207 create view v1 as select b from t1;
208 select distinct b from v1;
209 b
210 -1
211 -2
212 -3
213 select distinct b from v1 order by b limit 2;
214 b
215 -3
216 -2
217 select distinct b from t1 order by b limit 2;
218 b
219 -3
220 -2
221 prepare stmt1 from "select distinct b from v1 order by b limit 2";
222 execute stmt1;
223 b
224 -3
225 -2
226 execute stmt1;
227 b
228 -3
229 -2
230 deallocate prepare stmt1;
231 drop view v1;
232 create view v1 as select c from t1;
233 select distinct c from v1;
234 c
235 -1
236 -2
237 -3
238 select distinct c from v1 order by c limit 2;
239 c
240 -3
241 -2
242 select distinct c from t1 order by c limit 2;
243 c
244 -3
245 -2
246 prepare stmt1 from "select distinct c from v1 order by c limit 2";
247 execute stmt1;
248 c
249 -3
250 -2
251 execute stmt1;
252 c
253 -3
254 -2
255 deallocate prepare stmt1;
256 drop view v1;
257 drop table t1;
258 create table t1 (a int,
259 b int generated always as (-a) virtual,
260 c int generated always as (-a) stored);
261 create view v1 as select * from t1 where b > -2 && c >-2 with check option;
262 insert into v1 (a) values (1);
263 insert into v1 (a) values (3);
264 ERROR 44000: CHECK OPTION failed `test`.`v1`
265 insert ignore into v1 (a) values (2),(3),(0);
266 Warnings:
267 Warning 1369 CHECK OPTION failed `test`.`v1`
268 Warning 1369 CHECK OPTION failed `test`.`v1`
269 select * from t1;
270 a b c
271 0 0 0
272 1 -1 -1
273 drop view v1;
274 drop table t1;
275 #
276 # MDEV-18166 ASSERT_COLUMN_MARKED_FOR_READ failed on tables with vcols
277 #
278 CREATE TABLE t1 (d DATETIME(3), v DATETIME(2) AS (d));
279 CREATE VIEW v1 AS SELECT * FROM t1;
280 INSERT INTO t1 (d) VALUES ('2004-04-19 15:37:39.123'),
281 ('1985-12-24 10:15:08.456');
282 DELETE FROM v1 ORDER BY v LIMIT 4;
283 DROP VIEW v1;
284 DROP TABLE t1;
285 #
286 # [duplicate] MDEV-19306 Assertion `marked_for_read()' failed in
287 # Field_blob::val_str with virtual columns and views
288 #
289 CREATE TABLE t1 (a BLOB, b TEXT AS (a) VIRTUAL);
290 CREATE VIEW v1 AS SELECT * FROM t1;
291 INSERT INTO t1 (a) VALUES ('foo'),('bar');
292 DELETE FROM v1 ORDER BY b LIMIT 2;
293 DROP VIEW v1;
294 DROP TABLE t1;
295 CREATE TABLE t1 (d INT, v TINYINT AS (d));
296 CREATE VIEW v1 AS SELECT * FROM t1;
297 INSERT INTO t1 (d) VALUES ('2004'),('1985') ;
298 DELETE FROM v1 ORDER BY v LIMIT 4;
299 DROP VIEW v1;
300 DROP TABLE t1;
301 CREATE TABLE t1 (d VARCHAR(64), v VARCHAR(63) AS (d));
302 CREATE VIEW v1 AS SELECT * FROM t1;
303 INSERT INTO t1 (d) VALUES ('2004-04-19 15:37:39.123'),('1985-12-24 10:15:08.456') ;
304 DELETE FROM v1 ORDER BY v LIMIT 4;
305 DROP TABLE t1;
306 DROP VIEW v1;
307 #
308 # MDEV-18249 ASSERT_COLUMN_MARKED_FOR_READ failed in ANALYZE TABLE
309 #
310 create table t1 (c varchar(3) not null, v varchar(4) as (c) virtual);
311 insert into t1 (c) values ('a'),('b');
312 analyze table t1 persistent for columns (v) indexes ();
313 Table Op Msg_type Msg_text
314 test.t1 analyze status Engine-independent statistics collected
315 test.t1 analyze status OK
316 DROP VIEW IF EXISTS v1,v2;
317 DROP TABLE IF EXISTS t1,t2,t3;
318 DROP PROCEDURE IF EXISTS p1;
319 DROP FUNCTION IF EXISTS f1;
320 DROP TRIGGER IF EXISTS trg1;
321 DROP TRIGGER IF EXISTS trg2;
322 set sql_warnings = 0;