Predicates in UDFs

24 Jun 2020

      Hello,

Could you please explain why predicates with subqueries in UDFs do not work?

For example, I have the following statements:

create table itab (x int, y int);
insert into itab values (1, 0);
insert into itab values (2, 1);

create function test1(n int)
returns int
begin
while (n > 9) do
    set n = n -1;
end while;
return n;
end;

create function test2(n int)
returns int
begin
while (n > (select 9 from itab) ) do
    set n = n -1;
end while;
return n;
end;

create function test3(n int)
returns int
begin
while (exists (select x from itab where x < n)) do
    set n = n -1;
end while;
return n;
end;

The function test1 can be successfully created and executed, but functions
test2 and test3 cannot be created and executed.

Thank you and best regards,
Oksana

Predicates in UDFs

Oxana Dolmatova