oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4

5
#owner: linlin.xll
6
#owner group: sql1
7
#description: test pl
8
#tags: pl
9

10
--result_format 4
11

12
#
13
# Tests of prelocking-free execution of stored procedures.
14
# Currently two properties of prelocking-free SP execution are checked:
15
#  - It is possible to execute DDL statements in prelocking-free stored
16
#    procedure
17
#  - The same procedure can be called in prelocking-free mode and 
18
#    in prelocked mode (from within a function).
19

20
--disable_warnings
21
drop database if exists mysqltest;
22
drop table if exists t1, t2, t3, t4;
23
drop procedure if exists sp1;
24
drop procedure if exists sp2;
25
drop procedure if exists sp3;
26
drop procedure if exists sp4;
27
drop function if exists f1;
28
drop function if exists f2;
29
drop function if exists f3;
30
--enable_warnings
31

32
# BUG#8072 
33

34
create database mysqltest;
35
delimiter //;
36
use mysqltest//
37
create procedure sp1 () 
38
begin
39
  drop table if exists t1;
40
  select 1 as "my-col";
41
end;
42
//
43
delimiter ;//
44

45
select database();
46
call sp1();
47
select database();
48

49
use test;
50
select database();
51
call mysqltest.sp1();
52
select database();
53

54
drop procedure mysqltest.sp1;
55
drop database mysqltest;
56

57
# BUG#8766
58

59
delimiter //;
60
create procedure sp1() 
61
begin 
62
  create table t1 (a int); 
63
  insert into t1 values (10); 
64
end//
65

66
create procedure sp2()
67
begin
68
  create table t2(a int);
69
  insert into t2 values(1);
70
  call sp1();
71
end//
72

73
create function f1() returns int
74
begin 
75
  declare v int default 0;
76
  select max(a) from t1 into v;
77
  return v;
78
  ## return (select max(a) from t1);
79
end//
80

81
create procedure sp3()
82
begin 
83
  call sp1();
84
  select 'func', f1();
85
end//
86

87
delimiter ;//
88

89
call sp1();
90
select 't1',a from t1;
91

92
drop table t1;
93
call sp2();
94
select 't1',a from t1;
95
select 't2',a from t2;
96
drop table t1, t2;
97

98
call sp3();
99
select 't1',a from t1;
100

101
drop table t1;
102

103
drop procedure sp1;
104
drop procedure sp2;
105
drop procedure sp3;
106
drop function f1;
107

108
## delimiter //;
109
## create procedure sp1()
110
## begin
111
##   create temporary table t2(a int);
112
##   insert into t2 select * from t1;
113
## end//
114
## 
115
## create procedure sp2()
116
## begin
117
##   create temporary table t1 (a int);
118
##   insert into t1 values(1);
119
##   call sp1();
120
##   select 't1', a from t1;
121
##   select 't2', a from t2;
122
##   drop table t1;
123
##   drop table t2;
124
## end//
125
## 
126
## delimiter ;//
127
## call sp2();
128

129
## drop procedure sp1;
130
## drop procedure sp2;
131

132
# Miscelaneous tests
133
create table t1 (a int);
134
insert into t1 values(1),(2);
135
create table t2 as select * from t1;
136
create table t3 as select * from t1;
137
create table t4 as select * from t1;
138
delimiter //;
139
create procedure sp1(a int)
140
begin
141
  select a;
142
end //
143

144
create function f1() returns int
145
begin
146
  declare v int default 0;
147
  select max(a) from t1 into v;
148
  return v;
149
  ## return (select max(a) from t1);
150
end //
151

152
delimiter ;//
153

154
CALL sp1(f1());
155

156
#############
157
delimiter //;
158
create procedure sp2(a int)
159
begin
160
  select * from t3;
161
  select a;
162
end //
163

164
create procedure sp3()
165
begin 
166
  select * from t1;
167
  call sp2(5);
168
end //
169

170
create procedure sp4()
171
begin 
172
  select * from t2;
173
  call sp3();
174
end //
175

176
delimiter ;//
177
call sp4();
178

179
drop procedure sp1;
180
drop procedure sp2;
181
drop procedure sp3;
182
drop procedure sp4;
183
drop function f1;
184

185
# Test that prelocking state restoration works with cursors
186
--disable_warnings
187
drop view if exists v1;
188
--enable_warnings
189
delimiter //;
190

191
create function f1(ab int) returns int
192
begin
193
  declare i int;
194
  select max(a) from t1 where a < ab into i;
195
  ## set i= (select max(a) from t1 where a < ab) ;
196
  return i;
197
end //
198

199
create function f2(ab int) returns int
200
begin
201
  declare i int;
202
  select max(a) from t2 where a < ab into i;
203
  ## set i= (select max(a) from t2 where a < ab) ;
204
  return i;
205
end //
206

207
create view v1 as 
208
  select t3.a as x, t4.a as y ##, f2(3) as z
209
  from t3, t4 where t3.a = t4.a //
210

211
## create procedure sp1()
212
## begin
213
##   declare a int;
214
##   ## 嵌套事务
215
##   select f1(4) + count(*) A from t1, v1 into a;
216
##   ## set a= (select f1(4) + count(*) A from t1, v1);
217
## end //
218

219

220
## create function f3() returns int
221
## begin
222
##   call sp1();
223
##   return 1;
224
## end //
225

226
## call sp1() //
227

228
## select f3() //
229
## select f3() //
230

231
## call sp1() //
232

233
# ---------------
234
## drop procedure sp1//
235
## drop function f3//
236

237
create procedure sp1() 
238
begin 
239
  declare x int;
240
  declare c cursor for select count(*) from v1;
241
  ## declare c cursor for select f1(3) + count(*) from v1;
242
  open c;
243
  fetch c into x;
244
end;//
245

246
create function f3() returns int
247
begin
248
  call sp1();
249
  return 1;
250
end //
251

252
call sp1() //
253
call sp1() //
254

255
select f3() //
256
call sp1() //
257

258
delimiter ;//
259
drop view v1;
260
drop table t1,t2,t3,t4;
261
drop function f1;
262
drop function f2;
263
drop function f3;
264
drop procedure sp1;
265

266
#
267
# Bug#15683 "crash, Function on nested VIEWs, Prepared statement"
268
# Check that when creating the prelocking list a nested view 
269
# is not merged until it's used.
270
#
271
--disable_warnings
272
drop table if exists t1;
273
drop view if exists v1, v2, v3;
274
drop function if exists bug15683;
275
--enable_warnings
276
create table t1 (f1 bigint, f2 varchar(20), f3 bigint);
277
insert into t1 set f1 = 1, f2 = 'schoenenbourg', f3 = 1;
278
create view v1 as select 1 from t1 union all select 1;
279
create view v2 as select 1 from v1;
280
create view v3 as select 1 as f1 from v2;
281

282
delimiter |;
283
create function bug15683() returns bigint
284
begin
285
  declare v bigint default 0;
286
  select count(*) from v3 into v;
287
  return v;
288
  ## return (select count(*) from v3);
289
end|
290
delimiter ;|
291

292
prepare stmt from "select bug15683()";
293
execute stmt;
294
execute stmt;
295
deallocate prepare stmt;
296
drop table t1;
297
drop view v1, v2, v3;
298
drop function bug15683;
299

300

301
#
302
# Bug#19634 "Re-execution of multi-delete which involve trigger/stored 
303
#            function crashes server"
304
#
305
## --disable_warnings
306
## drop table if exists t1, t2, t3;
307
## drop function if exists bug19634;
308
## --enable_warnings
309
## create table t1 (id int, data int);
310
## create table t2 (id int);
311
## create table t3 (data int);
312
## create function bug19634() returns int return (select count(*) from t3);
313
## prepare stmt from "delete t1 from t1, t2 where t1.id = t2.id and bug19634()";
314
## # This should not crash server
315
## execute stmt;
316
## execute stmt;
317
## deallocate prepare stmt;
318
## 
319
## create trigger t1_bi before delete on t1 for each row insert into t3 values (old.data);
320
## prepare stmt from "delete t1 from t1, t2 where t1.id = t2.id";
321
## 
322
## execute stmt;
323
## execute stmt;
324
## deallocate prepare stmt;
325
## 
326
## drop function bug19634;
327
## drop table t1, t2, t3;
328

329
#
330
# Bug #27907 Misleading error message when opening/locking tables
331
#
332

333
## --disable_warnings
334
## drop table if exists bug_27907_logs;
335
## drop table if exists bug_27907_t1;
336
## --enable_warnings
337
## 
338
## create table bug_27907_logs (a int);
339
## create table bug_27907_t1 (a int);
340
## 
341
## delimiter |;
342
## 
343
## create trigger bug_27907_t1_ai after insert on bug_27907_t1
344
## for each row
345
## begin
346
##   insert into bug_27907_logs (a) values (1);
347
## end|
348
## 
349
## delimiter ;|
350
## 
351
## drop table bug_27907_logs;
352
## 
353
## #
354
## # was failing before with error ER_NOT_LOCKED
355
## #
356
## --error ER_NO_SUCH_TABLE
357
## insert into bug_27907_t1(a) values (1);
358
## 
359
## drop table bug_27907_t1;
360

361
--echo
362
--echo Bug#22427 create table if not exists + stored function results in
363
--echo inconsistent behavior
364
--echo
365
--echo Add a test case, the bug itself was fixed by the patch for
366
--echo Bug#20662
367
--echo
368
--disable_warnings
369
drop table if exists t1;
370
drop function if exists f_bug22427;
371
--enable_warnings
372
create table t1 (i int);
373
insert into t1 values (1);
374

375
delimiter |;
376
create function f_bug22427() returns int 
377
begin
378
  declare v int default 0;
379
  select max(i) from t1 into v;
380
  return v;
381
  ## return (select max(i) from t1);
382
end|
383
delimiter ;|
384

385
select f_bug22427();
386
# Until this bug was fixed, the following emitted error
387
# ERROR 1213: Deadlock found when trying to get lock
388
## 嵌套事务
389
## create table if not exists t1 select f_bug22427() as i;
390
## --error ER_TABLE_EXISTS_ERROR
391
## create table t1 select f_bug22427() as i;
392
## drop table t1;
393
drop function f_bug22427;
394

395
## --echo #
396
## --echo # Bug #29929 LOCK TABLES does not pre-lock tables used in triggers of the locked tables
397
## --echo #
398
## --disable_warnings
399
## DROP table IF EXISTS t1,t2;
400
## --enable_warnings
401
## CREATE TABLE t1 (c1 INT);
402
## CREATE TABLE t2 (c2 INT);
403
## INSERT INTO t1 VALUES (1);
404
## INSERT INTO t2 VALUES (2);
405
## DELIMITER //;
406
## CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
407
## BEGIN
408
## UPDATE t2 SET c2= c2 + 1;
409
## END//
410
## DELIMITER ;//
411
## --echo # Take a table lock on t1.
412
## --echo # This should pre-lock t2 through the trigger.
413
## LOCK TABLE t1 WRITE;
414
## INSERT INTO t1 VALUES (3);
415
## UNLOCK TABLES;
416
## LOCK TABLE t1 READ;
417
## --error ER_TABLE_NOT_LOCKED
418
## INSERT INTO t2 values(4);
419
## UNLOCK TABLES;
420
## SELECT * FROM t1;
421
## SELECT * FROM t2;
422
## DROP TRIGGER t1_ai;
423
## DROP TABLE t1, t2;
424
## 
425
## --echo End of 5.0 tests
426

427