RETURNING clause support in MERGE is inconsistent and depends on actions in the
WHEN clauses
--------------------------------------------------------------------------------------------
Key: CORE-6408
URL: http://tracker.firebirdsql.org/browse/CORE-6408
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.5, 4.0 RC 1
Environment: Firebird-4.0.0.2211-0_x64 on Windows 10
Reporter: Mark Rotteveel
The support for the RETURNING clause in MERGE is inconsistent, the types of
column reference seem to depend on the specified actions in the WHEN clause.
As an example
create table dummy2 (
id integer constraint pk_dummy2 primary key,
val varchar(50)
);
commit;
insert into dummy2 (id) values (1);
The following statement will work fine
merge into dummy2 as d
using (select 1, 'ab' from rdb$database) as src(id, val)
on d.id = src.id
when matched then update set d.val = src.val
returning d.val, new.val, old.val, src.val
However if another a `WHEN MATCHED` clause is added with a DELETE action, then
suddenly this doesn't work anymore:
merge into dummy2 as d
using (select 1, 'ab' from rdb$database) as src(id, val)
on d.id = src.id
when matched and d.id = 2 then delete
when matched then update set d.val = src.val
returning d.val, new.val, old.val, src.val
This results in
"""
Dynamic SQL Error; SQL error code = -206; Column unknown; D.VAL; At line 7,
column 13 [SQLState:42S22, ISC error code:335544578]
"""
It is possible to reference the column unqualified (after renaming the val
column in src to valsrc to avoid an ambiguous field name error):
merge into dummy2 as d
using (select 1, 'ab' from rdb$database) as src(id, valsrc)
on d.id = src.id
when matched and d.id = 2 then delete
when matched then update set d.val = src.valsrc
returning val, new.val, old.val, src.valsrc
I would expect the column in the target table to be referenceable as d.val in
all these situations.
--
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
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
