DECLARED TEMP TABLE. Problems with usage of autonomous transaction

[Pavel Zotov]

Please consider script:

==============

SET BAIL ON;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
commit;

set list on;
set autoterm on;
commit;

create or alter procedure sp_test returns(id int) as
    declare temporary table tbase(id int);
begin
    insert into tbase(id) values(-1);
    in autonomous transaction do
    begin
        insert into tbase(id) select -id from tbase; ----------- [  1 ]

         -- insert into tbase(id) select -2 * id from tbase;  -------- [ 2 ]
    end
    ----------------------------------
    for
        select id from tbase into id
    do begin
        suspend;
    end
end;

set sqlda_display on;
select p.* from sp_test p;

==============

If we run it then it will display:

==============

01: sqltype: 496 LONG Nullable scale: 0 subtype: 0 len: 4
  :  name: ID  alias: ID
  : table: SP_TEST  schema: PUBLIC  owner: SYSDBA

==============

--- and hang.

This is URL to dump and stack trace for firebird.exe & isql.exe processes in such state.

(also there are firebird.conf and FB snapshot itself)

There is funny nuance: if we comment out line [ 1 ] and UNcomment line marked as [ 2 ] then outcome suddenly changes and script issues:

==============

OUTPUT message field count: 1
01: sqltype: 496 LONG Nullable scale: 0 subtype: 0 len: 4
  :  name: ID  alias: ID
  : table: SP_TEST  schema: PUBLIC  owner: SYSDBA

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
-At procedure "PUBLIC"."SP_TEST" line: 7, col: 9

==============

And, finally, if we comment out "in autonomous transaction" then script works fine:

==============

set list on;
set autoterm on;
commit;

create or alter procedure sp_test returns(id int) as
    declare temporary table tbase(id int);
begin
    insert into tbase(id) values(-1);
    -- in autonomous transaction do
    begin
        insert into tbase(id) select id from tbase;
    end
    ----------------------------------
    for
        select id from tbase into id
    do begin
        suspend;
    end
end;

set sqlda_display on;
select p.* from sp_test p;

==============

Outcome (expected):

ID                              -1
ID                              1

/*

or for second case: 

ID                              -1
ID                              2

*/

[Adriano dos Santos Fernandes]

Fixed.


Adriano

[Pavel Zotov]

Fixed.

Confirmed fix on 6.0.0.2097-0592438, test has been committed.