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
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5250
URL: http://tracker.firebirdsql.org/browse/CORE-5250
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.0, 4.0 Initial, 2.5.5
Reporter: 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
new.id = coalesce(new.id, gen_id(g,1));
if (mod( new.id, 2) = 1) then
insert into test_a(id, x) values( new.id, new.x);
else
insert into test_b(id, x) values( new.id, new.x);
end
else if (updating) then
begin
if (mod( old.id, 2 ) = 1) then
update test_a set x = new.x where id = old.id;
else
update test_b set x = new.x where id = old.id;
end
else
begin
if (mod( old.id, 2) = 1) then
delete from test_a where id = old.id;
else
delete from test_b where id = 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 CORE-1036, but I've made separate ticket
after brief discuss with dimitr.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Mobile security can be enabling, not merely restricting. Employees who
bring their own devices (BYOD) to work are irked by the imposition of MDM
restrictions. Mobile Device Manager Plus allows you to control only the
apps on BYO-devices by containerizing them, leaving personal data untouched!
https://ad.doubleclick.net/ddm/clk/304595813;131938128;j
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
