INSERT / RETURNING: NULL is issued for the value of field which is defined either with DEFAULT literal or as COMPUTED-BY and DML is applied to complex view with TRIGGER which decides target table(s) for data storing [CORE5250]

[firebird-automations]

Submitted by: @pavel-zotov

Consider following script:

set bail on;
set term ^;
execute block as
begin
    begin
        execute statement 'drop trigger v\_complex\_biu';
        when any do begin end
    end
    begin
        execute statement 'drop sequence g';
        when any do begin end
    end
end^
set term ;^
commit;
create sequence g;
commit;

create or alter view v\_complex as select 1 i from rdb$database;
create or alter view v\_trivial as select 1 i from rdb$database;

recreate table test\_a\(
    id int primary key, 
    s varchar\(8\) default 'table\_A',  \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- #⁠#⁠#⁠ NB\-1: this field has DEFAULT value  #⁠#⁠#⁠
    x int, 
    y computed by\( x \* x \), ,  \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- #⁠#⁠#⁠ NB\-2: this and next fields have COMPUTED\-BY definition #⁠#⁠#⁠
    z computed by\( 111 \)
\);
recreate table test\_b\(
    id int primary key, 
    s varchar\(8\) default 'table\_B', 
    x int, 
    y computed by\( x \* x \), 
    z computed by\( 222\)
\);


recreate view v\_trivial as
select s, id, x, y, z from test\_a
;

recreate view v\_complex as 
select s, id, x, y, z from test\_a
union all
select s, id, x, y, z from test\_b
;

set term ^;
create or alter trigger v\_complex\_biu for v\_complex before insert or update or delete as
    declare v int;
begin
    if \(inserting\) then
        begin
            <http://new.id> = coalesce\(<http://new.id>, gen\_id\(g,1\)\);
            if \(mod\( <http://new.id>, 2\) = 1\) then
                insert into test\_a\(id, x\) values\( <http://new.id>, new\.x\);
            else
                insert into test\_b\(id, x\) values\( <http://new.id>, new\.x\);
        end
    else if \(updating\) then
        begin
            if \(mod\( <http://old.id>, 2 \) = 1\) then
                update test\_a set x = new\.x where id = <http://old.id>;
            else
                update test\_b set x = new\.x where id = <http://old.id>;
        end
    else
        begin
            if \(mod\( <http://old.id>, 2\) = 1\) then
                delete from test\_a where id = <http://old.id>;
            else
                delete from test\_b where id = <http://old.id>;
        end
end^
set term ;^
commit;

set list on;
set echo on;

insert into test\_a\(id, x\) values\( gen\_id\(g,1\), 10\) returning id,s,y,z;
select \* from test\_a;
rollback;

insert into v\_trivial\(id, x\) values\( gen\_id\(g,1\), 11\) returning id,s,y,z;
select \* from v\_trivial;
rollback;


insert into v\_complex\(id, x\) values\( gen\_id\(g,1\), 12\) returning id,s,y,z;
insert into v\_complex\(id, x\) values\( gen\_id\(g,1\), 13\) returning id,s,y,z;
select \* from v\_complex;
rollback;

===

It's output will finish with rows:

insert into v\_trivial\(id, x\) values\( gen\_id\(g,1\), 11\) returning id,s,y,z;

ID 2
S table_A
Y 121
Z 111

select \* from v\_trivial;

S table_A
ID 2
X 11
Y 121
Z 111

rollback;


insert into v\_complex\(id, x\) values\( gen\_id\(g,1\), 12\) returning id,s,y,z;

ID 3
S <null> -- #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ ??? #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
Y <null> -- #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ ??? #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
Z <null> -- #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ ??? #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

insert into v\_complex\(id, x\) values\( gen\_id\(g,1\), 13\) returning id,s,y,z;

ID 4
S <null> -- #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ ??? #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
Y <null> -- #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ ??? #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
Z <null> -- #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠ ??? #⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

select \* from v\_complex;

S table_A
ID 3
X 12
Y 144
Z 111

S table_B
ID 4
X 13
Y 169
Z 222

rollback;

===

So, when a view is defined in such manner that TRIGGER it required for proper target of incoming data, we have a problem with RETURNING such of fields which have either DEFAULT or COMPUTED-BY clause in DDL.

PS.Perhaps this issue is related to CORE1036, but I've made separate ticket after brief discuss with dimitr.

[mrotteveel]

The workaround is to explicitly map the columns into the new context inside the trigger.

[asfernandes]

I think this is not a bug, just how things (should) works.

[mrotteveel]

Maybe, but back in 2013, Dmitry seemed to agree that this might be a bug, but would unlikely be changed: http://fb-list-archive.s3-website-eu-west-1.amazonaws.com/firebird-support/2013/2/120659.html (though that interpretation is a bit of reading between the lines on my part)

[asfernandes]

I don't see problem with DEFAULT and COMPUTED in master, and here I agree, they must work.

But views with triggers uses custom user logic. It may insert many records, delete records in insert trigger, etc.

Engine could only read the original record source used in the records iteration.