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
# tags: optimizer
7
# description:
8
#
9
--disable_warnings
10
drop database if exists view;
11
--enable_warnings
12
create database view;
13
use view;
14
set character_set_client = 45;
15

16
#
17
## test view schema weak-binding
18
#
19

20
#simple
21
--disable_warnings
22
drop table if exists t1;
23
drop view if exists v, vv;
24
--enable_warnings
25
create table t1(c1 int, c2 int);
26
create view v as select c1, c2 from t1;
27
create view vv as select c1 from v;
28
select c1, c2 from v;
29
alter table t1 drop column c2;
30
--error 1356
31
select c1 from v;
32
--error 1356
33
select c2 from v;
34
--error 1356
35
select c1, c2 from v;
36
drop table t1;
37
--error 1356
38
select c1 from v;
39
--error 1356
40
select c2 from v;
41
--error 1356
42
select c1, c2 from v;
43
create table t1(c1 int);
44
--error 1356
45
select c1 from vv;
46
--error 1356
47
select c2 from vv;
48
--error 1356
49
select c1, c2 from vv;
50
--error 1356
51
select 1 as a from vv;
52

53
--disable_warnings
54
drop table if exists t1;
55
drop view if exists v, vv;
56
--enable_warnings
57
create table t1(c1 int, c2 int);
58
create view v as select c1, c2 from t1;
59
create view vv as select c2 from v;
60
select c1, c2 from v;
61
alter table t1 drop column c2;
62
--error 1356
63
select c1 from v;
64
--error 1356
65
select c2 from v;
66
--error 1356
67
select c1, c2 from v;
68
drop table t1;
69
--error 1356
70
select c1 from v;
71
--error 1356
72
select c2 from v;
73
--error 1356
74
select c1, c2 from v;
75
create table t1(c1 int);
76
--error 1356
77
select c1 from vv;
78
--error 1356
79
select c2 from vv;
80
--error 1356
81
select c1, c2 from vv;
82
--error 1356
83
select c2, c1 from vv;
84
--error 1356
85
select 1 as a from vv;
86

87
#select * from view
88
--disable_warnings
89
drop table if exists t1,t2;
90
drop view if exists v,vv;
91
--enable_warnings
92
create table t1(c1 int ,c2 int);
93
create table t2(c1 int ,c2 int);
94
create view v as select c1, c2 from t1;
95
create view vv as select c1 from v;
96
alter table t1 drop column c2;
97
--error 1356
98
select * from v;
99
--error 1356
100
select v.* from v;
101
--error 1356
102
select * from vv;
103
--error 1356
104
select vv.* from vv;
105

106
#join on and using
107
--disable_warnings
108
drop table if exists t1,t2;
109
drop view if exists v;
110
--enable_warnings
111
create table t1(c1 int,c2 int);
112
create table t2(c1 int,c2 int);
113
create view v as select c1, c2 from t1;
114
alter table t1 drop column c2;
115
--error 1356
116
select v.c1 from v join t2 on v.c1 = t2.c1;
117
--error 1356
118
select v.c1 from v join t2 on v.c2 = t2.c2;
119
--error 1356
120
select v.c1 from v join t2 using(c1);
121
--error 1356
122
select v.c1 from v join t2 using(c2);
123

124
#column in where 
125
--disable_warnings
126
drop table if exists t1,t2;
127
drop view if exists v;
128
--enable_warnings
129
create table t1(c1 int,c2 int);
130
create view v as select c1, c2 from t1;
131
select c1 from v where c2 = 1;
132
select c1 from v where (select c2) = 1;
133
alter table t1 drop column c2;
134
--error 1356
135
select c1 from v where c2 = 1;
136
--error 1356
137
select c1 from v where (select c2) = 1;
138

139
#column in order by
140
--disable_warnings
141
drop table if exists t1,t2;
142
drop view if exists v;
143
--enable_warnings
144
create table t1(c1 int,c2 int);
145
create view v as select c1, c2 from t1;
146
select c1 from v order by c2;
147
select c1 from v order by (select c2);
148
alter table t1 drop column c2;
149
--error 1356
150
select c1 from v order by c2;
151
--error 1356
152
select c1 from v order by (select c2);
153

154
#column in group by
155
--disable_warnings
156
drop table if exists t1,t2;
157
drop view if exists v;
158
--enable_warnings
159
create table t1(c1 int,c2 int);
160
create view v as select c1, c2 from t1;
161
select c1 from v group by c2;
162
select c1 from v group by (select c2);
163
alter table t1 drop column c2;
164
--error 1356
165
select c1 from v group by c2;
166
--error 1356
167
select c1 from v group by (select c2);
168

169
#column in having 
170
#drop table if exists t1,t2;
171
#drop view if exists v;
172
#create table t1(c1 int,c2 int);
173
#create view v as select c1, c2 from t1;
174
#select c1 from v having c2 = 1;
175
#select c1 from v having (select c2) = 1;
176
#alter table t1 drop column c2;
177
#--error 1054
178
#select c1 from v having c2 = 1;
179
#--error 1356
180
#select c1 from v having (select c2) = 1;
181

182

183
--disable_warnings
184
drop table if exists t1;
185
drop view if exists v;
186
--enable_warnings
187
create table t1(c1 int,c2 int);
188
insert into t1 values(1,2),(2,3),(3,4);
189
create view v as select c1,c2 from t1 order by c2; 
190
alter table t1 drop column c2;
191
#order by 即使是从select里找到了c2(select中的c2不报错是因为view_stmt)，因为select里面的c2也是无效列，因此最终结果也是视图无效
192
--error 1356
193
select c1 from v;
194

195
--disable_warnings
196
drop table if exists t1;
197
drop view if exists v;
198
--enable_warnings
199
create table t1(c1 int,c2 int);
200
insert into t1 values(1,2),(2,3),(3,4);
201
create view v as select c1, -1 as c2 from t1 order by (select c2);
202
select c1 from v;
203
alter table t1 drop column c2;
204
# a select c2提升上来之后从t1基础列里面找不到就直接报错，不会再去select_items找
205
--error 1356
206
select c1 from v;
207
# b (bug)
208
#select c1 from (select c1, -1 as c2 from t1 order by (select c2)) v;
209

210
--disable_warnings
211
drop table if exists t1;
212
drop view if exists v;
213
--enable_warnings
214
create table t1(c1 int,c2 int);
215
create view v as select c1,c2 from t1 where c2=1;
216
alter table t1 drop column c2;
217
--error 1356
218
select c1 from v;
219

220
--disable_warnings
221
drop table if exists t1;
222
drop view if exists v;
223
--enable_warnings
224
create table t1(c1 int,c2 int);
225
insert into t1 values(1,2),(2,3),(3,4);
226
create view v as select c1, -1 as c2 from t1 order by (select c2);
227
alter table t1 drop column c2;
228
--error 1356
229
select c1 from v;
230

231
# agg
232
--disable_warnings
233
drop table if exists t1;
234
drop view if exists v;
235
--enable_warnings
236
create table t1(c1 int,c2 int);
237
create view v as select c1,sum(c2) from t1;
238
alter table t1 drop column c2;
239
--error 1356
240
select c1 from v;
241

242
#insert
243
--disable_warnings
244
drop table if exists t1;
245
drop view if exists v;
246
--enable_warnings
247
create table t1(c1 int,c2 int);
248
insert into t1 values(1,2),(2,3),(3,4),(4,5);
249
create view v as select c1,c2 from t1;
250
select * from v;
251
alter table t1 drop column c2;
252
--error 1356
253
select c1 from v;
254
--error 1356
255
select c2 from v;
256
--error 1356
257
select * from v;
258
alter table t1 add column c2 int default 100;
259
select * from v;
260
alter table t1 alter column c2 set default 200;
261
insert into t1(c1) values(5);
262
select * from v;
263
#alter table t1 change column c1 c1 varchar(20) default 'test';
264
#insert into t1 values();
265
#select * from v;
266

267

268
#
269
##test desc (show columns from table)
270
#
271
--disable_warnings
272
drop table if exists t1;
273
drop view if exists v;
274
--enable_warnings
275
create table t1(c1 int);
276
create view v as select c1 from t1;
277
desc v;
278

279
--disable_warnings
280
drop table if exists t1,t2;
281
drop view if exists v;
282
--enable_warnings
283
create table t1(c1 int);
284
create table t2(c1 int);
285
create view v as (select c1 from t1) union (select c1 from t2);
286
desc v;
287

288
--disable_warnings
289
drop table if exists t1;
290
drop view if exists v;
291
--enable_warnings
292
create table t1(c1 int,c2 int);
293
create view v as select c1,c2 from t1;
294
desc v;
295
alter table t1 drop column c2;
296
--error 1356
297
desc v;
298

299
--disable_warnings
300
drop table if exists t1;
301
drop view if exists v,vv;
302
--enable_warnings
303
create table t1(c1 int,c2 int);
304
create view v as select c1,c2 from t1;
305
create view vv as select c1,c2 from v;
306
alter table t1 drop column c2;
307
--error 1356
308
desc v;
309
--error 1356
310
desc vv;
311

312
--disable_warnings
313
drop table if exists t1;
314
drop view if exists v,vv;
315
--enable_warnings
316
create table t1(c1 int,c2 int);
317
create view v as select c1,c2 from t1;
318
create view vv as select c1,c2 from v;
319
create or replace view v as select c1 from t1;
320
desc v;
321
--error 1356
322
desc vv;
323
create or replace view vv as select c1 from v;
324
desc vv;
325

326
--disable_warnings
327
drop table if exists t1,t2,t3,t4;
328
drop view if exists v,vv;
329
--enable_warnings
330
create table t1(c1 int,c2 int);
331
create table t2(c1 int,c2 int);
332
create table t3(c1 int,c2 int);
333
create table t4(c1 int,c2 int);
334
create view v as ((select * from t1) union (select c1,c2 from t2)) union (select * from t3);
335
desc v;
336
create view vv as (select * from v) union (select * from t4);
337
desc vv;
338

339
--disable_warnings
340
drop table if exists t1,t2;
341
drop view if exists v;
342
--enable_warnings
343
create table t1(c1 int,c2 int);
344
create table t2(c1 int,c2 int);
345
create view v as select c1,c2 from t1;
346
select (select c2 from v limit 1) from t2;
347
alter table t1 drop column c2;
348
--error 1356
349
select (select c2 from v limit 1) from t2;
350
--error 1356
351
select (select c3 from v limit 1) from t2;
352
alter table t2 add column c3 int;
353
--error 1356
354
select (select c3 from v limit 1) from t2;
355

356
#
357
##Bug 
358
#
359
--disable_warnings
360
drop table if exists t1;
361
drop view if exists t1;
362
drop table if exists v1,v2,v3;
363
drop view if exists v1,v2,v3;
364
--enable_warnings
365
create table t1(c1 int);
366
create view v1 as select * from t1;
367
create view v2 as select * from v1;
368
create view v3 as select * from v2;
369
drop table t1;
370
rename table v3 to t1;
371
--error 1462
372
select * from v1;
373
--error 1462
374
select * from v2;
375
--error 1146
376
select * from v3;
377

378
--disable_warnings
379
drop table if exists t1,t2;
380
drop view if exists t1,t2;
381
drop table if exists v1,v2,v3;
382
drop view if exists v1,v2,v3;
383
--enable_warnings
384
create table t1(c1 int);
385
create table t2(c1 int);
386
create view v1 as select * from t1;
387
create view v2 as select * from t2;
388
create view v3 as select v1.c1 as v1c1, v2.c1 as v2c1 from v1,v2;
389
drop table t2;
390
rename table v3 to t2;
391
--error 1462
392
select * from t2;
393
--error 1462
394
select * from v2;
395

396
--disable_warnings
397
drop table if exists t1,t2;
398
drop view if exists t1,t2;
399
drop table if exists v1,v2;
400
drop view if exists v1,v2;
401
--enable_warnings
402
create table t1(c1 int);
403
create table t2(c1 int);
404
create view v1 as select * from t1;
405
create view v2 as select (select c1 from v1 limit 1) from t2;
406
drop table t1;
407
rename table v2 to t1;
408
--error 1462
409
select * from t1;
410
--error 1462
411
select * from v1;
412

413
--disable_warnings
414
drop table if exists t1;
415
drop view if exists t1;
416
drop table if exists v1,v2,v3,v4;
417
drop view if exists v1,v2,v3,v4;
418
--enable_warnings
419
create table t1(c1 int);
420
create view v1 as select * from t1;
421
create view v2 as select * from v1;
422
create view v3 as select * from v2;
423
create view v4 as select * from v2;
424
drop view v1;
425
rename table v4 to v1;
426
--error 1462
427
select * from v1;
428
--error 1462
429
select * from v2;
430
--error 1462
431
select * from v3;
432

433
#
434
## Bug : 
435
## 主要测试在非select语句引用select_stmt(view展开)的问题，select结果作为其它stmt的输入暂时不支持，
436
## 这里仅仅针对bug加入回归case
437
#
438
--error 1235
439
SET optimizer_switch = (SELECT variable_value FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'optimizer_switch');
440
--disable_warnings
441
drop table if exists t1,t2;
442
drop view if exists v1;
443
--enable_warnings
444
create table t1(c1 int,c2 int);
445
create table t2(c1 int,c2 int);
446
insert into t1(c1,c2) values(1,2),(2,3);
447
create view v1 as select * from t1;
448
insert into t2(c1,c2) select * from v1;
449
select * from t2;
450

451
#Bug 对于基表schema失效的视图，查询成功后不应该有warning
452
--disable_warnings
453
drop table if exists t1;
454
drop view if exists v;
455
--enable_warnings
456
create table t1(c1 int,c2 int);
457
create view v as select * from t1;
458
alter table t1 drop column c2;
459
--error 1356
460
select c1 from v;
461
show warnings;
462

463
#Bug 对于基表schema失效的视图，查询成功后不应该有warning
464
--disable_warnings
465
drop table if exists t1; 
466
drop view if exists v;
467
--enable_warnings
468
create table t1(c1 int,c2 int);
469
create view v as select * from t1; 
470
alter table t1 drop column c2; 
471
--error 1356
472
select c1 from v;
473
show warnings;
474

475
#Bug 创建视图中包含not exists子查询出错
476
--disable_warnings
477
drop table if exists t1;
478
drop view if exists v;
479
--enable_warnings
480
create table t1(c1 int,c2 int);
481
insert into t1 values(1, 1);
482
create view v as select * from t1 where not exists(select * from t1 where c1>0);
483
--source mysql_test/include/show_create_table_old_version_replica2.inc
484
show create view v;
485
select * from v;
486
create view xy as select 123 from dual where not exists (select 1 from dual) limit 1;
487
select * from xy;
488

489
#Bug 创建视图中包含union子句并且类型为char的时候长度出错
490
--disable_warnings
491
drop table if exists t1; 
492
drop table if exists t2;
493
drop view if exists v;
494
--enable_warnings
495
create table t1 (k int primary key, v1 int, v2 int);
496
create table t2 (k int primary key, v1 char(1), v2 varchar(10));
497
create view v as select k, v1, v2 from t1 union select k, v1, v2 from t2;
498
--source mysql_test/include/show_create_table_old_version_replica2.inc
499
show create view v;
500
select * from v;
501

502
--disable_warnings
503
drop table if exists t1;
504
drop view if exists v;
505
--enable_warnings
506
create table t1 (k int, v int);
507
create view v as (select k, v from t1) union (select 1, 1 from t1);
508
--source mysql_test/include/show_create_table_old_version_replica2.inc
509
show create view v;
510

511
--disable_warnings
512
drop table if exists t1; 
513
drop table if exists t2;
514
--enable_warnings
515
CREATE TABLE `t11` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`) );
516
CREATE TABLE `t21` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`) );
517

518
--real_sleep 3
519
explain extended_noaddr select t3.* from (select t11.a as X, t21.a as Y from t11 left join t21 on t11.a = t21.a) as t3 where t3.y is not null and t3.x > 10;
520
drop database if exists view;
521