Firebird 5: Possible bug in Stored Procedures with subroutines

[Martijn Tonies (Upscene Productions)]

Hi all,

 

I’m testing something with variable use in subroutines and stumbled upon something I cannot explain.

 

Take this procedure, connect with SYSDBA.

 

SET TERM ^^ ;

CREATE OR ALTER PROCEDURE P_2

returns (

  O_CHAR VarChar(200)  character set utf8)

SQL SECURITY INVOKER

AS

declare variable i integer;

declare variable a integer;

declare variable o integer;

declare variable outer_v varchar(200) character set utf8;

 

  declare function m (v varchar(10)) returns varchar(10) as

  begin

    return v || outer_v;

  end

 

  declare function myfunc (a2 integer) returns integer as

  declare variable inner_char varchar(200);

  begin                

    i = i + 1;

    select current_user from rdb$database where current_user = 'SYSDBA' into :outer_v;

    inner_char = m('test');

    if (a2 = 4)

    then a2 = myfunc(a2 + 1);

    return a2 * :a * :i;

  end

 

begin

/* i = 5;

  i = package_test.MyFunction(2);

  i = mult(4, 2);      

  o = myfunc(a);*/

  outer_v = current_user;

  o_char = outer_v;

end ^^

SET TERM ; ^^

 

 

When executing, it returns “S” for output parameter O_CHAR.

 

Now, recreate the procedure, but comments out the “declare functions”:

 

SET TERM ^^ ;

CREATE OR ALTER PROCEDURE P_2

returns (

  O_CHAR VarChar(200)  character set utf8)

SQL SECURITY INVOKER

AS

declare variable i integer;

declare variable a integer;

declare variable o integer;

declare variable outer_v varchar(200) character set utf8;

 

/*  declare function m (v varchar(10)) returns varchar(10) as

  begin

    return v || outer_v;

  end

 

  declare function myfunc (a2 integer) returns integer as

  declare variable inner_char varchar(200);

  begin                

    i = i + 1;

    select current_user from rdb$database where current_user = 'SYSDBA' into :outer_v;

    inner_char = m('test');

    if (a2 = 4)

    then a2 = myfunc(a2 + 1);

    return a2 * :a * :i;

  end */

 

begin

/* i = 5;

  i = package_test.MyFunction(2);

  i = mult(4, 2);      

  o = myfunc(a);*/

  outer_v = current_user;

  o_char = outer_v;

end ^^

SET TERM ; ^^

 

 

execute again: the routine returns “SYSDBA”.

 

The lines executed are no different, but the result is now correct. At first I thought it had something to do with the character set, so I added the UTF8 thing everywhere, even though thats the default character set.

 

 

Can someone confirm?

 

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, MariaDB, InterBase, SQLite, NexusDB and Firebird.

[Mark Rotteveel]

This sounds more like something for firebird-support than
firebird-general, or maybe even something to directly report on
https://github.com/FirebirdSQL/firebird/issues

Mark

--
Mark Rotteveel

[Martijn Tonies (Upscene Productions)]

Oops, wrong address ;)

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL,
MySQL, MariaDB, InterBase, SQLite, NexusDB and Firebird.