FB2.5/FB5 different behaviour in stored procedure and left join

[A. Casati]

This is the code to create the case:

create sequence seq_test

create table t_test (test_id integer, test_descr varchar(30), constraint pk_test primary key(test_id))

create procedure pr_insert_test(i_test_descr varchar(30)) returns (o_test_id integer) AS
begin
  o_test_id = gen_id(seq_test, 1);
  insert into t_test(test_id, test_descr) values (:o_test_id, :i_test_descr);
  suspend;
end

The following SELECT:

select * from pr_insert_test('test #1') left join t_test on test_id = o_test_id

in FB2.5 returns populated columns from t_test, record exists
in FB5 returns null values, record in t_test does not exist when the join is applied

Any way to resolve this?

Thank you

[Tomasz Tyrakowski]

AFAIK not in the logic you use. The behavior is related to cursor
stability (introduced in FB3 if I remember correctly). Simplifying, the
SELECT sees t_test as it was when the SELECT started. It doesn't see the
inserts made when the SELECT is already running (and that's what happens
in your case). In general, cursor stability is a desired property. In
your particular case it seems to be a disadvantage, because earlier you
relied on side effects of the procedure being selected from.
I'm afraid you'll need to change the logic, e.g. insert first, select
and join later.

regards
Tomasz

[Mark Rotteveel]

To resolve this, you should stop joining to t_test, and instead return
all columns values from the stored procedure.

create procedure pr_insert_test(i_test_descr varchar(30)) returns

(o_test_id integer, o_test_descr varchar(30)) AS
begin
insert into t_test(test_id, test_descr) values (gen_id(seq_test, 1),
:i_test_descr)
returning test_id, test_descr into :o_test_id, :o_test_descr;
suspend;
end

You could even change the table to use an identity column:

create table t_test (
test_id integer generated always as identity
constraint pk_test primary key(test_id),
test_descr varchar(30)
)

You no longer need the separate sequence, and the insert can then become:

insert into t_test(test_descr) values (:i_test_descr)
returning test_id, test_descr into :o_test_id, :o_test_descr;

Mark
--
Mark Rotteveel

[Mark Rotteveel]

As an aside, depending on why you're using that procedure and that
select, you could also just directly execute

insert into t_test(test_descr) values ('test #1') returning *;

(or, still using the sequence):

insert into t_test(test_id, test_descr) values (gen_id(seq_test, 1),

'test #1') returning *;

Mark
--
Mark Rotteveel

[Attila Molnár]

Try to turn off  OuterJoinConversionn firebird.conf