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

Oxana Dolmatova

ferreira

Oxana Dolmatova

ferreira

tags

participants (2)