oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: dachuan.sdc
5
# owner group: SQL2
6
# description:
7
#
8
# 新建数据库作为测试环境，以下三句勿删
9
--disable_warnings
10
drop database if exists db_view;
11
create database db_view;
12
use db_view;
13

14
drop view if exists v_mix_tv, v_mix_1, v_mix_2, v_mix_3, vv_mix_1, vv_1, vvv_1;
15
--enable_warnings
16
--disable_warnings
17
drop view if exists view_v1, view_v2, view_v3, view_v4, view_v5, view_v6, view_v7;
18
--enable_warnings
19
--disable_warnings
20
drop table if exists view_t1_not_null, view_t1_null_default, view_t1_not_null_default;
21
--enable_warnings
22
--disable_warnings
23
drop view if exists view_v1_null_default, view_v1_not_null, view_v1_not_null_default;
24
--enable_warnings
25
--disable_warnings
26
drop table if exists view_t1, view_t2, view_t3;
27
--enable_warnings
28

29
set character_set_client = 45;
30

31
#
32
# view test
33
#
34
create table view_t1(c1 int primary key, c2 int);
35
create table view_t2(c3 int primary key, c4 int);
36
create table view_t3(c1 int primary key, c2 int);
37

38
insert into view_t1 values(1, 11), (2, 12), (3, 13);
39
insert into view_t2 values(10, 111), (20, 112), (30, 113);
40
insert into view_t3 values(100, 1111), (200, 1112), (300, 1113);
41

42
############################################
43
# debugging
44
############
45
#
46
# error: dupl column name of 'c1'
47
############################################
48
#
49
# nullable/defaultable
50
#
51
create table view_t1_not_null(c1 int primary key, c2 int not null);
52
create table view_t1_null_default(c1 int primary key, c2 int default 22222);
53
create table view_t1_not_null_default(c1 int primary key, c2 int not null default 2222);
54
#create view view_v1_not_null as select * from view_t1_not_null;
55
create view view_v1_not_null as select c1,c2 from view_t1_not_null;
56
#create view view_v1_null_default as select * from view_t1_null_default;
57
create view view_v1_null_default as select c1,c2 from view_t1_null_default;
58
#create view view_v1_not_null_default as select * from view_t1_not_null_default;
59
create view view_v1_not_null_default as select c1,c2 from view_t1_not_null_default;
60
#
61
## Bug 
62
#
63
create table table_t8(c1 int null,c2 int not null);
64
create view view_v8 as select c1+c2 from table_t8;
65
desc view_v8;
66
create view view_v9 as select c1+c2 from table_t8 a;
67
desc view_v9;
68
drop table table_t8;
69
drop view view_v8;
70
drop view view_v9;
71

72
#
73
# other types (TODO)
74
#
75
#
76
#
77
# single table with columns and alias
78
#
79
#create or replace view view_v1 as select * from view_t1;
80
create or replace view view_v1 as select c1,c2 from view_t1;
81
select * from view_v1;
82
create or replace view view_v2(c1, c2) as select c1, c2 from view_t1;
83
select * from view_v2;
84
create or replace view view_v3(c2) as select c2 from view_t1;
85
select * from view_v3;
86
#create or replace view view_v4(vc2) as select c2 from view_t1;
87
create or replace view view_v4(vc2) as select c2 as vc2 from view_t1;
88
select * from view_v4;
89
#create or replace view view_v5(vc2) as select c2 as tc2 from view_t1;
90
create or replace view view_v5(vc2) as select c2 as vc2 from view_t1;
91
select * from view_v5;
92
# todo select 1+5
93
#
94
# single table with mixed expr
95
#
96
create or replace view view_v6 as select c1+1 from view_t1;
97
select * from view_v6;
98
#create or replace view view_v7(vc1,vc2) as select c1+1 as tc1, c2+2 as tc2 from view_t1;
99
create or replace view view_v7(vc1,vc2) as select c1+1 as vc1, c2+2 as vc2 from view_t1;
100
select * from view_v7;
101
#
102
# multi-table with alias
103
#
104
#create or replace view v_2t_1 as select * from view_t1, view_t2;
105
create or replace view v_2t_1 as select c1,c2,c3,c4 from view_t1, view_t2;
106
--sorted_result
107
select * from v_2t_1;
108
# DUP
109
#create or replace view v_2t_1 as select * from view_t1, view_t2;
110
#create or replace view v_3t_1(vc1, vc2, vc3, vc4, vc5, vc6) as select * from view_t1, view_t2, view_t3;
111
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;
112
--sorted_result
113
select * from v_3t_1;
114
#
115
# sub-view
116
#
117
#create or replace view vv_1 as select * from view_v1;
118
create or replace view vv_1 as select c1,c2 from view_v1;
119
--sorted_result
120
select * from vv_1;
121
#create or replace view vvv_1 as select * from vv_1;
122
create or replace view vvv_1 as select c1,c2 from vv_1;
123
--sorted_result
124
select * from vvv_1;
125
#
126
# sub-view/table
127
#
128
# OB_ERR_COLUMN_DUPLICATE
129
#create or replace view v_mix_tv as select * from view_v1, view_t1;
130

131
# mixed case with x1/x2 but different column names
132
#create view v_mix_1 as select * from view_t1;
133
create view v_mix_1 as select c1,c2 from view_t1;
134
#create view vv_mix_1 as select * from v_mix_1, view_t2;
135
create view vv_mix_1 as select c1,c2,c3,c4 from v_mix_1, view_t2;
136
--sorted_result
137
select * from (select * from (select * from view_t1) as v_test, view_t2) as vv_test;
138
--sorted_result
139
select * from vv_mix_1;
140
# mixed case with x1/x3 but same column names
141
#create or replace view v_mix_2(v_t1_c1, v_t1_c2) as select * from view_t1;
142
#select * from v_mix_2;
143
#create or replace view vv_mix_2 as select * from v_mix_2, view_t3;
144
#select * from (select * from (select c1 v_t1_c1, c2 v_t1_c2 from view_t1) as v_test, view_t3) as vv_test;
145
#select * from vv_mix_2;
146

147
#create or replace view v_mix_3 as select * from view_t1;
148
create or replace view v_mix_3 as select c1,c2 from view_t1;
149
--sorted_result
150
select * from v_mix_3;
151
#create or replace view vv_mix_3(vv_c1, vv_c2, vv_c3, vv_c4) as select * from v_mix_3, view_t3;
152
#select * from (select v_test.c1 vv_c1, v_test.c2 vv_c2, view_t3.c1 vv_c3, view_t3.c2 vv_c4 from (select * from view_t1) as v_test, view_t3) as vv_test;
153
#select * from vv_mix_3;
154
#
155
# view join table
156
# Unknown column 'c1'
157
#select * from vv_mix_3 join view_v6 on vv_mix_3.vv_c1=view_v6.c1+1;
158
#
159
# clean
160
#
161
--disable_warnings
162
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;
163
--enable_warnings
164
--disable_warnings
165
drop view if exists v_2t_1, v_3t_1;
166
--enable_warnings
167
--disable_warnings
168
drop view if exists view_v1, view_v2, view_v3, view_v4, view_v5, view_v6, view_v7;
169
--enable_warnings
170
--disable_warnings
171
drop table if exists view_t1_not_null, view_t1_null_default, view_t1_not_null_default;
172
--enable_warnings
173
--disable_warnings
174
drop view if exists view_v1_null_default, view_v1_not_null, view_v1_not_null_default;
175
--enable_warnings
176
--disable_warnings
177
drop table if exists view_t1, view_t2, view_t3;
178
--enable_warnings
179
#
180
#drop view if exists xy1.view_v1, xy2.view_v2, xy3.view_v3;
181
#drop view if exists xy1.view_v1, view_v2, xy3.view_v3;
182

183
# select_stmt with brackets or not
184
--disable_warnings
185
drop view if exists v1,v2,v3;
186
--enable_warnings
187
--disable_warnings
188
drop table if exists t1;
189
--enable_warnings
190
create table t1(c1 int primary key);
191
insert into t1 values(1),(2);
192
#create view v1 as select * from t1;
193
create view v1 as select c1 from t1;
194
#$create view v2 as (select * from t1);
195
create view v2 as (select c1 from t1);
196
#create view v3 as ((select * from t1));
197
create view v3 as ((select c1 from t1));
198
insert into t1 values(3),(4);
199
select * from v1;
200
select * from v2;
201
select * from v3;
202
--disable_warnings
203
drop view if exists v1,v2,v3;
204
--enable_warnings
205
#--error TODO xiyu
206
#select * from v;
207
--disable_warnings
208
drop table if exists t1;
209
--enable_warnings
210

211
# Bug 
212
--disable_warnings
213
drop table if exists t1;
214
--enable_warnings
215
--disable_warnings
216
drop view if exists v1;
217
--enable_warnings
218
create table t1(c1 varchar(10));
219
insert into t1 value('test1');
220
insert into t1 value('test2');
221
create view v1 as select  c1 from t1;
222
--sorted_result
223
select v1.c1 from v1 join t1 on v1.c1 = t1.c1;
224
--source mysql_test/include/show_create_table_old_version_replica2.inc
225
show create view v1;
226
--disable_warnings
227
drop table if exists t1;
228
--enable_warnings
229
--disable_warnings
230
drop view if exists v1;
231
--enable_warnings
232

233
#replace view
234
--disable_warnings
235
drop table if exists t1;
236
--enable_warnings
237
--disable_warnings
238
drop view if exists v1;
239
--enable_warnings
240
create table t1(c1 int, c2 int);
241
insert into t1 values(1,1);
242
select * from t1;
243
create or replace view v1 as select c1 from t1;
244
select * from v1;
245
#create or replace view v1 as select * from t1;
246
create or replace view v1 as select c1,c2 from t1;
247
select * from v1;
248
--source mysql_test/include/show_create_table_old_version_replica2.inc
249
show create view v1;
250
--error 1050
251
create view v1 as select c2 from t1;
252
drop view v1;
253
drop table t1;
254

255
##
256
--disable_warnings
257
drop table if exists t1;
258
--enable_warnings
259
--disable_warnings
260
drop view if exists v1;
261
--enable_warnings
262
# schema
263
create table t1(c1 int,c2 int);
264
create table t2(a int,b int);
265
create view v as select c1,c2 from t1;
266
create view vv as select c1,c2,a,b from v,t2;
267
#create view vv as select * from t1;
268
select * from v;
269
select c1 from v;
270
select * from vv;
271
select c1 from vv;
272
# drop column
273
alter table t1 drop column c2;
274
--error 1356
275
select * from v;
276
--error 1356
277
select * from vv;
278
--error 1356
279
select c1 from v;
280
--error 1356
281
select c1 from vv;
282
--error 1356
283
select c2 from v;
284
--error 1356
285
select c2 from vv;
286
# recover dropped column
287
alter table t1 add column c2 int;
288
select * from v;
289
select * from vv;
290
# drop table
291
drop table t1;
292
--error 1356
293
select * from v;
294
--error 1356
295
select * from vv;
296
# recover table with different column_type
297
create table t1(c1 varchar(10), c2 datetime);
298
select * from v;
299
select * from vv;
300
drop table t1;
301
# recover table with different column_count
302
create table t1(c1 varchar(10), c2 datetime, c3 int);
303
select * from v;
304
select * from vv;
305
# insert and select
306
drop table t1;
307
create table t1(c1 int,c2 int);
308
insert into t1 values(1,10),(2,11);
309
select * from v;
310
select * from vv;
311
drop table t1;
312
create table t1(c1 varchar(10), c2 double, c3 int);
313
insert into t1 values('hello', 100.10, 1),('hello', 100.11,2);
314
select * from v;
315
select * from v;
316
drop table t1;
317
drop view v;
318

319
create table t1(c1 int, c2 int);
320
insert into t1 values(1,1), (2,2);
321
create view v as select 5 from t1 order by 1;
322
select * from v;
323
create view v1 as select * from t1 where c1>'1';
324
--source mysql_test/include/show_create_table_old_version_replica2.inc
325
show create view v1;
326
select * from v1;
327
drop view v, v1;
328

329
create view v as select 5, 6 from t1 order by 1,2;
330
select * from v;
331
drop view v;
332

333
create view v as select c1 + 1 from t1 order by 1;
334
select * from v;
335
drop view v;
336

337
create view v as select 7 + 3 from t1 order by 1;
338
select * from v;
339
drop view v;
340
--disable_warnings
341
drop table if exists t1;
342
--enable_warnings
343
create table t1(a int, b int);
344
insert into t1 values (1,1);
345
create view v as select group_concat(b) from t1 group by a;
346
select * from v;
347

348
drop table if exists t1;
349
drop view if exists v;
350
create table t1(c1 datetime, c2 int);
351
insert into t1 values('1990-03-03 00:00:00', 2), ('2016-05-31 20:00:00', 3);
352
create view v as select * from t1 where c1>'1990-04-01 00:00:00';
353
--source mysql_test/include/show_create_table_old_version_replica2.inc
354
show create view v;
355
select * from v;
356
drop view if exists v;
357

358
drop table if exists t1;
359
drop view if exists v1, v2;
360
create table t1(col1 varchar(12) character set utf8mb4 collate utf8mb4_general_ci);
361
insert into t1 values('t1_val');
362
create view v1 as select 'v1_val' collate utf8mb4_general_ci as col1;
363
--source mysql_test/include/show_create_table_old_version_replica2.inc
364
show create view v1;
365
create view v2 as select col1 from v1 union select col1 from t1;
366
--source mysql_test/include/show_create_table_old_version_replica2.inc
367
show create view v2;
368
select coercibility(col1), collation(col1) from v2;
369
# 本句勿删，且保证始终是该脚本的最后一行
370
drop database if exists db_view;
371