oceanbase

1
# owner: linlin.xll
2
# owner group: SQL1
3
# description: 
4
#tags: pl
5

6
--disable_query_log
7
set @@session.explicit_defaults_for_timestamp=off;
8
--enable_query_log
9
--result_format 4
10
#
11
# Bug #11602: SP with very large body not handled well
12
#
13

14
--disable_warnings
15
drop procedure if exists test.longprocedure;
16
drop table if exists t1, t2;
17
--enable_warnings
18

19
create table t1 (a int);
20
insert into t1 values (1),(2),(3);
21

22
#let $body=`select repeat('select count(*) from t1 into out1;\n', 3072)`;
23
let $body=`select repeat('select count(*) from t1 into out1;\n', 100)`;
24

25
delimiter //;
26
--disable_query_log
27
eval select length('$body') as length//
28
eval create procedure test.longprocedure (out out1 int) deterministic
29
begin
30
  $body
31
end//
32
--enable_query_log
33

34
delimiter ;//
35

36
# this is larger than the length above, because it includes the 'begin' and
37
# 'end' bits and some whitespace
38
select length(routine_definition) from information_schema.routines where routine_schema = 'test' and routine_name = 'longprocedure';
39

40
set ob_query_timeout=10000000000;
41
set ob_trx_timeout=10000000000;
42
call test.longprocedure(@value); select @value;
43
set ob_query_timeout=default;
44
set ob_trx_timeout=default;
45

46
drop procedure test.longprocedure;
47
drop table t1;
48
#
49
# Bug #9819 "Cursors: Mysql Server Crash while fetching from table with 5
50
# million records.": 
51
# To really test the bug, increase the number of loop iterations ($1).
52
# For 4 millions set $1 to 22.
53
create table t1 (f1 char(100) , f2 mediumint , f3 int , f4 real, f5 numeric);
54
insert into t1 (f1, f2, f3, f4, f5) values
55
("This is a test case for for Bug#9819", 1, 2, 3.0, 4.598);
56
create table t2 like t1;
57
let $1=8;
58
--disable_query_log
59
--disable_result_log
60
while ($1)
61
{
62
  eval insert into t1 select * from t1;
63
  dec $1;
64
}
65
--enable_result_log
66
--enable_query_log
67
select count(*) from t1;
68
select count(*) from t2;
69
--disable_warnings
70
drop procedure if exists p1;
71
--enable_warnings
72
delimiter |;
73
create procedure p1()
74
begin
75
  declare done integer default 0;
76
  declare vf1 char(100) ;
77
  declare vf2 mediumint;
78
  declare vf3 int ;
79
  declare vf4 real ;
80
  declare vf5 numeric ;
81
  declare cur1 cursor for select f1,f2,f3,f4,f5 from t1;  
82
  declare continue handler for sqlstate '02000' set done = 1; 
83
  open cur1;
84
  while done <> 1 do
85
    fetch cur1 into vf1, vf2, vf3, vf4, vf5;
86
    if not done then
87
      insert into t2 values (vf1, vf2, vf3, vf4, vf5);
88
    end if;
89
  end while;
90
  close cur1;
91
end|
92
delimiter ;|
93
set ob_query_timeout=10000000000;
94
set ob_trx_timeout=10000000000;
95
call p1();
96
set ob_query_timeout=default;
97
set ob_trx_timeout=default;
98
select count(*) from t1;
99
select count(*) from t2;
100
select f1 from t1 limit 1;
101
select f1 from t2 limit 1;
102
drop procedure p1;
103
drop table t1, t2;
104