Debian Codegraph @

mysql-test/suite/gcol_ndb/r/gcol_view_ndb.result

raw
1 SET @@session.default_storage_engine = 'ndbcluster';
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 OK
9 create view v1 (d,e) as select abs(b), abs(c) from t1;
10 select d,e from v1;
11 d e
12 1 1
13 1 1
14 2 2
15 2 2
16 3 3
17 select is_updatable from information_schema.views where table_name='v1';
18 IS_UPDATABLE
19 NO
20 explain select d,e from v1;
21 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
22 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL X 100.00 NULL
23 Warnings:
24 Note 1003 /* select#1 */ select abs(`test`.`t1`.`b`) AS `d`,abs(`test`.`t1`.`c`) AS `e` from `test`.`t1`
25 create algorithm=temptable view v2 (d,e) as select abs(b), abs(c) from t1;
26 show create view v2;
27 View Create View character_set_client collation_connection
28 v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` (`d`,`e`) AS select abs(`t1`.`b`) AS `abs(b)`,abs(`t1`.`c`) AS `abs(c)` from `t1` utf8mb4 utf8mb4_0900_ai_ci
29 select d,e from v2;
30 d e
31 1 1
32 1 1
33 2 2
34 2 2
35 3 3
36 explain select d,e from v2;
37 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
38 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL X 100.00 NULL
39 2 DERIVED t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL X 100.00 NULL
40 Warnings:
41 Note 1003 /* select#1 */ select `test`.`v2`.`d` AS `d`,`test`.`v2`.`e` AS `e` from `test`.`v2`
42 create view v3 (d,e) as select d*2, e*2 from v1;
43 select * from v3;
44 d e
45 2 2
46 2 2
47 4 4
48 4 4
49 6 6
50 explain select * from v3;
51 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
52 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL X 100.00 NULL
53 Warnings:
54 Note 1003 /* select#1 */ select (abs(`test`.`t1`.`b`) * 2) AS `d`,(abs(`test`.`t1`.`c`) * 2) AS `e` from `test`.`t1`
55 drop view v1,v2,v3;
56 drop table t1;
57 create table t1 (a int not null,
58 b int generated always as (-a) virtual,
59 c int generated always as (-a) stored);
60 insert into t1 (a) values (1), (2), (3), (1), (2), (3);
61 create view v1 as select distinct b from t1;
62 select * from v1;
63 b
64 -1
65 -2
66 -3
67 explain select * from v1;
68 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
69 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL X 100.00 NULL
70 2 DERIVED t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL X 100.00 Using temporary
71 Warnings:
72 Note 1003 /* select#1 */ select `test`.`v1`.`b` AS `b` from `test`.`v1`
73 select * from t1;
74 a b c
75 1 -1 -1
76 1 -1 -1
77 2 -2 -2
78 2 -2 -2
79 3 -3 -3
80 3 -3 -3
81 drop view v1;
82 create view v1 as select distinct c from t1;
83 select * from v1;
84 c
85 -1
86 -2
87 -3
88 explain select * from v1;
89 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
90 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL X 100.00 NULL
91 2 DERIVED t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL X 100.00 Using temporary
92 Warnings:
93 Note 1003 /* select#1 */ select `test`.`v1`.`c` AS `c` from `test`.`v1`
94 select * from t1;
95 a b c
96 1 -1 -1
97 1 -1 -1
98 2 -2 -2
99 2 -2 -2
100 3 -3 -3
101 3 -3 -3
102 drop view v1;
103 drop table t1;
104 create table t1 (a int not null,
105 b int generated always as (-a) virtual,
106 c int generated always as (-a) stored);
107 insert into t1 (a) values (1), (2), (3), (4);
108 create view v1 as select b+1 from t1 order by 1 desc limit 2;
109 select * from v1;
110 b+1
111 0
112 -1
113 explain select * from v1;
114 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
115 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL X 100.00 NULL
116 2 DERIVED t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL X 100.00 Using filesort
117 Warnings:
118 Note 1003 /* select#1 */ select `test`.`v1`.`b+1` AS `b+1` from `test`.`v1`
119 drop view v1;
120 create view v1 as select c+1 from t1 order by 1 desc limit 2;
121 select * from v1;
122 c+1
123 -1
124 0
125 explain select * from v1;
126 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
127 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL X 100.00 NULL
128 2 DERIVED t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL X 100.00 Using filesort
129 Warnings:
130 Note 1003 /* select#1 */ select `test`.`v1`.`c+1` AS `c+1` from `test`.`v1`
131 drop view v1;
132 drop table t1;
133 create table t1 (a int,
134 b int,
135 c int generated always as (-a) virtual,
136 d int generated always as (-a) stored,
137 primary key(a));
138 insert into t1 (a,b) values (10,2), (20,3), (30,4), (40,5), (50,10);
139 create view v1 (a,e,f,g) as select a, b+1,c+1,d+1 from t1;
140 update v1 set a=a+e;
141 select * from v1 order by a;
142 a e f g
143 13 3 -12 -12
144 24 4 -23 -23
145 35 5 -34 -34
146 46 6 -45 -45
147 61 11 -60 -60
148 select * from t1 order by a;
149 a b c d
150 13 2 -13 -13
151 24 3 -24 -24
152 35 4 -35 -35
153 46 5 -46 -46
154 61 10 -61 -61
155 delete from v1;
156 select * from v1;
157 a e f g
158 select * from t1;
159 a b c d
160 insert into v1 (a,e) values (60,15);
161 ERROR HY000: Column 'e' is not updatable
162 drop table t1;
163 drop view v1;
164 create table t1 (a int,
165 b int generated always as (-a) virtual,
166 c int generated always as (-a) stored,
167 primary key(a));
168 insert into t1 (a) values (1), (2), (3);
169 create view v1 (x,y,z) as select a,b,c from t1 where b < -1;
170 select t1.a, v1.x, v1.y, v1.z from t1 left join v1 on (t1.b= v1.y);
171 a x y z
172 1 NULL NULL NULL
173 2 2 -2 -2
174 3 3 -3 -3
175 drop view v1;
176 create view v1 (x,y,z) as select a,b,c from t1 where c < -1;
177 select t1.a, v1.x, v1.y, v1.z from t1 left join v1 on (t1.c= v1.z);
178 a x y z
179 1 NULL NULL NULL
180 2 2 -2 -2
181 3 3 -3 -3
182 drop view v1;
183 drop table t1;
184 create table t1 (a1 int,
185 b1 int generated always as (-a1) virtual,
186 c1 int generated always as (-a1) stored);
187 create table t2 (a2 int,
188 b2 int generated always as (-a2) virtual,
189 c2 int generated always as (-a2) stored);
190 insert into t1 (a1) values (1), (2);
191 insert into t2 (a2) values (2), (3);
192 create view v1 as select * from t1,t2 union all select * from t1,t2;
193 select * from v1;
194 a1 b1 c1 a2 b2 c2
195 1 -1 -1 2 -2 -2
196 1 -1 -1 2 -2 -2
197 1 -1 -1 3 -3 -3
198 1 -1 -1 3 -3 -3
199 2 -2 -2 2 -2 -2
200 2 -2 -2 2 -2 -2
201 2 -2 -2 3 -3 -3
202 2 -2 -2 3 -3 -3
203 drop view v1;
204 drop table t1, t2;
205 create table t1 (a int,
206 b int generated always as (-a) virtual,
207 c int generated always as (-a) stored);
208 create table t2 like t1;
209 create view v1 as select a,b,c from t1;
210 create view v2 as select a,b,c from t2 where b in (select b from v1);
211 show create view v2;
212 View Create View character_set_client collation_connection
213 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`) utf8mb4 utf8mb4_0900_ai_ci
214 drop view v2, v1;
215 drop table t1, t2;
216 create table t1 (a int,
217 b int generated always as (-a) virtual,
218 c int generated always as (-a) stored);
219 insert into t1 (a) values (1),(1),(2),(2),(3),(3);
220 create view v1 as select b from t1;
221 select distinct b from v1;
222 b
223 -1
224 -2
225 -3
226 select distinct b from v1 order by b limit 2;
227 b
228 -3
229 -2
230 select distinct b from t1 order by b limit 2;
231 b
232 -3
233 -2
234 prepare stmt1 from "select distinct b from v1 order by b limit 2";
235 execute stmt1;
236 b
237 -3
238 -2
239 execute stmt1;
240 b
241 -3
242 -2
243 deallocate prepare stmt1;
244 drop view v1;
245 create view v1 as select c from t1;
246 select distinct c from v1;
247 c
248 -1
249 -2
250 -3
251 select distinct c from v1 order by c limit 2;
252 c
253 -3
254 -2
255 select distinct c from t1 order by c limit 2;
256 c
257 -3
258 -2
259 prepare stmt1 from "select distinct c from v1 order by c limit 2";
260 execute stmt1;
261 c
262 -3
263 -2
264 execute stmt1;
265 c
266 -3
267 -2
268 deallocate prepare stmt1;
269 drop view v1;
270 drop table t1;
271 create table t1 (a int,
272 b int generated always as (-a) virtual,
273 c int generated always as (-a) stored);
274 create view v1 as select * from t1 where b > -2 && c >-2 with check option;
275 Warnings:
276 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
277 insert into v1 (a) values (1);
278 insert into v1 (a) values (3);
279 ERROR HY000: CHECK OPTION failed 'test.v1'
280 insert ignore into v1 (a) values (2),(3),(0);
281 Warnings:
282 Warning 1369 CHECK OPTION failed 'test.v1'
283 Warning 1369 CHECK OPTION failed 'test.v1'
284 select * from t1;
285 a b c
286 0 0 0
287 1 -1 -1
288 drop view v1;
289 drop table t1;
290 DROP VIEW IF EXISTS v1,v2;
291 DROP TABLE IF EXISTS t1,t2,t3;
292 DROP PROCEDURE IF EXISTS p1;
293 DROP FUNCTION IF EXISTS f1;
294 DROP TRIGGER IF EXISTS trg1;
295 DROP TRIGGER IF EXISTS trg2;
296 set sql_warnings = 0;