oceanbase

1
drop database if exists db_view;
2
create database db_view;
3
use db_view;
4
drop view if exists v_mix_tv, v_mix_1, v_mix_2, v_mix_3, vv_mix_1, vv_1, vvv_1;
5
drop view if exists view_v1, view_v2, view_v3, view_v4, view_v5, view_v6, view_v7;
6
drop table if exists view_t1_not_null, view_t1_null_default, view_t1_not_null_default;
7
drop view if exists view_v1_null_default, view_v1_not_null, view_v1_not_null_default;
8
drop table if exists view_t1, view_t2, view_t3;
9
set character_set_client = 45;
10
create table view_t1(c1 int primary key, c2 int);
11
create table view_t2(c3 int primary key, c4 int);
12
create table view_t3(c1 int primary key, c2 int);
13
insert into view_t1 values(1, 11), (2, 12), (3, 13);
14
insert into view_t2 values(10, 111), (20, 112), (30, 113);
15
insert into view_t3 values(100, 1111), (200, 1112), (300, 1113);
16
create table view_t1_not_null(c1 int primary key, c2 int not null);
17
create table view_t1_null_default(c1 int primary key, c2 int default 22222);
18
create table view_t1_not_null_default(c1 int primary key, c2 int not null default 2222);
19
create view view_v1_not_null as select c1,c2 from view_t1_not_null;
20
create view view_v1_null_default as select c1,c2 from view_t1_null_default;
21
create view view_v1_not_null_default as select c1,c2 from view_t1_not_null_default;
22
create table table_t8(c1 int null,c2 int not null);
23
create view view_v8 as select c1+c2 from table_t8;
24
desc view_v8;
25
Field	Type	Null	Key	Default	Extra
26
c1+c2	bigint(12)	YES		NULL	
27
create view view_v9 as select c1+c2 from table_t8 a;
28
desc view_v9;
29
Field	Type	Null	Key	Default	Extra
30
c1+c2	bigint(12)	YES		NULL	
31
drop table table_t8;
32
drop view view_v8;
33
drop view view_v9;
34
create or replace view view_v1 as select c1,c2 from view_t1;
35
select * from view_v1;
36
c1	c2
37
1	11
38
2	12
39
3	13
40
create or replace view view_v2(c1, c2) as select c1, c2 from view_t1;
41
select * from view_v2;
42
c1	c2
43
1	11
44
2	12
45
3	13
46
create or replace view view_v3(c2) as select c2 from view_t1;
47
select * from view_v3;
48
c2
49
11
50
12
51
13
52
create or replace view view_v4(vc2) as select c2 as vc2 from view_t1;
53
select * from view_v4;
54
vc2
55
11
56
12
57
13
58
create or replace view view_v5(vc2) as select c2 as vc2 from view_t1;
59
select * from view_v5;
60
vc2
61
11
62
12
63
13
64
create or replace view view_v6 as select c1+1 from view_t1;
65
select * from view_v6;
66
c1+1
67
2
68
3
69
4
70
create or replace view view_v7(vc1,vc2) as select c1+1 as vc1, c2+2 as vc2 from view_t1;
71
select * from view_v7;
72
vc1	vc2
73
2	13
74
3	14
75
4	15
76
create or replace view v_2t_1 as select c1,c2,c3,c4 from view_t1, view_t2;
77
select * from v_2t_1;
78
c1	c2	c3	c4
79
1	11	10	111
80
1	11	20	112
81
1	11	30	113
82
2	12	10	111
83
2	12	20	112
84
2	12	30	113
85
3	13	10	111
86
3	13	20	112
87
3	13	30	113
88
create or replace view v_3t_1(vc1, vc2, vc3, vc4, vc5, vc6) as select view_t1.c1 as vc1, view_t1.c2 as vc2, view_t2.c3 as vc3, view_t2.c4 as vc4, view_t3.c1 as vc5, view_t3.c2 as vc6 from view_t1, view_t2, view_t3;
89
select * from v_3t_1;
90
vc1	vc2	vc3	vc4	vc5	vc6
91
1	11	10	111	100	1111
92
1	11	10	111	200	1112
93
1	11	10	111	300	1113
94
1	11	20	112	100	1111
95
1	11	20	112	200	1112
96
1	11	20	112	300	1113
97
1	11	30	113	100	1111
98
1	11	30	113	200	1112
99
1	11	30	113	300	1113
100
2	12	10	111	100	1111
101
2	12	10	111	200	1112
102
2	12	10	111	300	1113
103
2	12	20	112	100	1111
104
2	12	20	112	200	1112
105
2	12	20	112	300	1113
106
2	12	30	113	100	1111
107
2	12	30	113	200	1112
108
2	12	30	113	300	1113
109
3	13	10	111	100	1111
110
3	13	10	111	200	1112
111
3	13	10	111	300	1113
112
3	13	20	112	100	1111
113
3	13	20	112	200	1112
114
3	13	20	112	300	1113
115
3	13	30	113	100	1111
116
3	13	30	113	200	1112
117
3	13	30	113	300	1113
118
create or replace view vv_1 as select c1,c2 from view_v1;
119
select * from vv_1;
120
c1	c2
121
1	11
122
2	12
123
3	13
124
create or replace view vvv_1 as select c1,c2 from vv_1;
125
select * from vvv_1;
126
c1	c2
127
1	11
128
2	12
129
3	13
130
create view v_mix_1 as select c1,c2 from view_t1;
131
create view vv_mix_1 as select c1,c2,c3,c4 from v_mix_1, view_t2;
132
select * from (select * from (select * from view_t1) as v_test, view_t2) as vv_test;
133
c1	c2	c3	c4
134
1	11	10	111
135
1	11	20	112
136
1	11	30	113
137
2	12	10	111
138
2	12	20	112
139
2	12	30	113
140
3	13	10	111
141
3	13	20	112
142
3	13	30	113
143
select * from vv_mix_1;
144
c1	c2	c3	c4
145
1	11	10	111
146
1	11	20	112
147
1	11	30	113
148
2	12	10	111
149
2	12	20	112
150
2	12	30	113
151
3	13	10	111
152
3	13	20	112
153
3	13	30	113
154
create or replace view v_mix_3 as select c1,c2 from view_t1;
155
select * from v_mix_3;
156
c1	c2
157
1	11
158
2	12
159
3	13
160
drop view if exists v_mix_tv, v_mix_1, v_mix_2, v_mix_3, vv_mix_1, vv_mix_2, vv_mix_3, vv_1, vvv_1;
161
drop view if exists v_2t_1, v_3t_1;
162
drop view if exists view_v1, view_v2, view_v3, view_v4, view_v5, view_v6, view_v7;
163
drop table if exists view_t1_not_null, view_t1_null_default, view_t1_not_null_default;
164
drop view if exists view_v1_null_default, view_v1_not_null, view_v1_not_null_default;
165
drop table if exists view_t1, view_t2, view_t3;
166
drop view if exists v1,v2,v3;
167
drop table if exists t1;
168
create table t1(c1 int primary key);
169
insert into t1 values(1),(2);
170
create view v1 as select c1 from t1;
171
create view v2 as (select c1 from t1);
172
create view v3 as ((select c1 from t1));
173
insert into t1 values(3),(4);
174
select * from v1;
175
c1
176
1
177
2
178
3
179
4
180
select * from v2;
181
c1
182
1
183
2
184
3
185
4
186
select * from v3;
187
c1
188
1
189
2
190
3
191
4
192
drop view if exists v1,v2,v3;
193
drop table if exists t1;
194
drop table if exists t1;
195
drop view if exists v1;
196
create table t1(c1 varchar(10));
197
insert into t1 value('test1');
198
insert into t1 value('test2');
199
create view v1 as select  c1 from t1;
200
select v1.c1 from v1 join t1 on v1.c1 = t1.c1;
201
c1
202
test1
203
test2
204
show create view v1;
205
View	Create View	character_set_client	collation_connection
206
v1	CREATE VIEW `v1` AS select `db_view`.`t1`.`c1` AS `c1` from `db_view`.`t1`	utf8mb4	utf8mb4_general_ci
207
drop table if exists t1;
208
drop view if exists v1;
209
drop table if exists t1;
210
drop view if exists v1;
211
create table t1(c1 int, c2 int);
212
insert into t1 values(1,1);
213
select * from t1;
214
c1	c2
215
1	1
216
create or replace view v1 as select c1 from t1;
217
select * from v1;
218
c1
219
1
220
create or replace view v1 as select c1,c2 from t1;
221
select * from v1;
222
c1	c2
223
1	1
224
show create view v1;
225
View	Create View	character_set_client	collation_connection
226
v1	CREATE VIEW `v1` AS select `db_view`.`t1`.`c1` AS `c1`,`db_view`.`t1`.`c2` AS `c2` from `db_view`.`t1`	utf8mb4	utf8mb4_general_ci
227
create view v1 as select c2 from t1;
228
ERROR 42S01: Table 'v1' already exists
229
drop view v1;
230
drop table t1;
231
drop table if exists t1;
232
drop view if exists v1;
233
create table t1(c1 int,c2 int);
234
create table t2(a int,b int);
235
create view v as select c1,c2 from t1;
236
create view vv as select c1,c2,a,b from v,t2;
237
select * from v;
238
c1	c2
239
select c1 from v;
240
c1
241
select * from vv;
242
c1	c2	a	b
243
select c1 from vv;
244
c1
245
alter table t1 drop column c2;
246
select * from v;
247
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
248
select * from vv;
249
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
250
select c1 from v;
251
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
252
select c1 from vv;
253
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
254
select c2 from v;
255
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
256
select c2 from vv;
257
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
258
alter table t1 add column c2 int;
259
select * from v;
260
c1	c2
261
select * from vv;
262
c1	c2	a	b
263
drop table t1;
264
select * from v;
265
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
266
select * from vv;
267
ERROR 42S22: View 'db_view.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
268
create table t1(c1 varchar(10), c2 datetime);
269
select * from v;
270
c1	c2
271
select * from vv;
272
c1	c2	a	b
273
drop table t1;
274
create table t1(c1 varchar(10), c2 datetime, c3 int);
275
select * from v;
276
c1	c2
277
select * from vv;
278
c1	c2	a	b
279
drop table t1;
280
create table t1(c1 int,c2 int);
281
insert into t1 values(1,10),(2,11);
282
select * from v;
283
c1	c2
284
1	10
285
2	11
286
select * from vv;
287
c1	c2	a	b
288
drop table t1;
289
create table t1(c1 varchar(10), c2 double, c3 int);
290
insert into t1 values('hello', 100.10, 1),('hello', 100.11,2);
291
select * from v;
292
c1	c2
293
hello	100.1
294
hello	100.11
295
select * from v;
296
c1	c2
297
hello	100.1
298
hello	100.11
299
drop table t1;
300
drop view v;
301
create table t1(c1 int, c2 int);
302
insert into t1 values(1,1), (2,2);
303
create view v as select 5 from t1 order by 1;
304
select * from v;
305
5
306
5
307
5
308
create view v1 as select * from t1 where c1>'1';
309
show create view v1;
310
View	Create View	character_set_client	collation_connection
311
v1	CREATE VIEW `v1` AS select `db_view`.`t1`.`c1` AS `c1`,`db_view`.`t1`.`c2` AS `c2` from `db_view`.`t1` where (`db_view`.`t1`.`c1` > '1')	utf8mb4	utf8mb4_general_ci
312
select * from v1;
313
c1	c2
314
2	2
315
drop view v, v1;
316
create view v as select 5, 6 from t1 order by 1,2;
317
select * from v;
318
5	6
319
5	6
320
5	6
321
drop view v;
322
create view v as select c1 + 1 from t1 order by 1;
323
select * from v;
324
c1 + 1
325
2
326
3
327
drop view v;
328
create view v as select 7 + 3 from t1 order by 1;
329
select * from v;
330
7 + 3
331
10
332
10
333
drop view v;
334
drop table if exists t1;
335
create table t1(a int, b int);
336
insert into t1 values (1,1);
337
create view v as select group_concat(b) from t1 group by a;
338
select * from v;
339
group_concat(b)
340
1
341
drop table if exists t1;
342
drop view if exists v;
343
create table t1(c1 datetime, c2 int);
344
insert into t1 values('1990-03-03 00:00:00', 2), ('2016-05-31 20:00:00', 3);
345
create view v as select * from t1 where c1>'1990-04-01 00:00:00';
346
show create view v;
347
View	Create View	character_set_client	collation_connection
348
v	CREATE VIEW `v` AS select `db_view`.`t1`.`c1` AS `c1`,`db_view`.`t1`.`c2` AS `c2` from `db_view`.`t1` where (`db_view`.`t1`.`c1` > '1990-04-01 00:00:00')	utf8mb4	utf8mb4_general_ci
349
select * from v;
350
c1	c2
351
2016-05-31 20:00:00	3
352
drop view if exists v;
353
drop table if exists t1;
354
drop view if exists v1, v2;
355
Warnings:
356
Note	1051	Unknown table 'db_view.v1'
357
Note	1051	Unknown table 'db_view.v2'
358
create table t1(col1 varchar(12) character set utf8mb4 collate utf8mb4_general_ci);
359
insert into t1 values('t1_val');
360
create view v1 as select 'v1_val' collate utf8mb4_general_ci as col1;
361
show create view v1;
362
View	Create View	character_set_client	collation_connection
363
v1	CREATE VIEW `v1` AS select 'v1_val' collate utf8mb4_general_ci AS `col1`	utf8mb4	utf8mb4_general_ci
364
create view v2 as select col1 from v1 union select col1 from t1;
365
show create view v2;
366
View	Create View	character_set_client	collation_connection
367
v2	CREATE VIEW `v2` AS (select `db_view`.`v1`.`col1` AS `col1` from `db_view`.`v1`) union (select `db_view`.`t1`.`col1` AS `col1` from `db_view`.`t1`)	utf8mb4	utf8mb4_general_ci
368
select coercibility(col1), collation(col1) from v2;
369
coercibility(col1)	collation(col1)
370
0	utf8mb4_general_ci
371
0	utf8mb4_general_ci
372
drop database if exists db_view;
373