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

10
# Fibonacci, for recursion test. (Yet Another Numerical series :)
11
# Split from main.sp due to problems reported in Bug#15866
12

13
--result_format 4
14

15
--disable_warnings
16
drop table if exists t3;
17
--enable_warnings
18
create table t3 ( f bigint unsigned not null );
19

20
# We deliberately do it the awkward way, fetching the last two
21
# values from the table, in order to exercise various statements
22
# and table accesses at each turn.
23
--disable_warnings
24
drop procedure if exists fib;
25
--enable_warnings
26

27
# Now for multiple statements...
28
delimiter |;
29

30
create procedure fib(n int unsigned)
31
begin
32
  if n > 1 then
33
    begin
34
      declare x, y bigint unsigned;
35
      declare c cursor for select f from t3 order by f desc limit 2;
36
      open c;
37
      fetch c into y;
38
      fetch c into x;
39
      insert into t3 values (x+y);
40
      call fib(n-1);
41
      ## Close the cursor AFTER the recursion to ensure that the stack
42
      ## frame is somewhat intact.
43
      close c;
44
    end;
45
  end if;
46
end|
47

48
# Enable recursion
49
set @@max_sp_recursion_depth= 20|
50

51
insert into t3 values (0), (1)|
52

53
# The small number of recursion levels is intentional.
54
# We need to avoid
55
# Bug#15866 main.sp fails (thread stack limit
56
#           insufficient for recursive call "fib(20)")
57
# which affects some platforms.
58
call fib(4)|
59

60
select * from t3 order by f asc|
61

62
drop table t3|
63
drop procedure fib|
64
set @@max_sp_recursion_depth= 0|
65

66