Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

RETURNING clause in MERGE cannot reference column in aliased target table using qualified reference (alias.column) if DELETE action present [CORE6408] #6646

Closed
firebird-automations opened this issue Sep 26, 2020 · 10 comments
Closed

RETURNING clause in MERGE cannot reference column in aliased target table using qualified reference (alias.column) if DELETE action present [CORE6408] #6646

firebird-automations opened this issue Sep 26, 2020 · 10 comments
Assignees
@asfernandes
Labels
affect-version: 3.0.5 affect-version: 3.0.6 affect-version: 3.0.7 affect-version: 4.0 RC 1 component: engine fix-version: 3.0.8 fix-version: 4.0 RC 1 priority: major qa: done successfully type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @mrotteveel

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.

Specifically, if the target table is aliased, then when a DELETE action is present, it is not possible to reference columns in the target table in the returning clause using alias.columnname.

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 http://d.id = http://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 http://d.id = http://src.id
when matched and http://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 http://d.id = http://src.id
when matched and http://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.

It is possible to use the table name if that target table wasn't aliased:

merge into dummy2
using (select 1, 'ab' from rdb$database) as src(id, val)
on http://dummy2.id = http://src.id
when matched and http://dummy2.id = 2 then delete
when matched then update set dummy2.val = src.val
returning dummy2.val, new.val, old.val, src.val

Commits: 1d2944c 2f3229c
@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

environment: Firebird-4.0.0.2211-0_x64 on Windows 10 => Firebird-3.0.5.33220-0_x64 on Windows 10
Firebird-4.0.0.2211-0_x64 on Windows 10

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Version: 3.0.6 [ 10889 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: 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 http://d.id = http://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 http://d.id = http://src.id
when matched and http://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 http://d.id = http://src.id
when matched and http://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.

=>

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 http://d.id = http://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 http://d.id = http://src.id
when matched and http://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 http://d.id = http://src.id
when matched and http://d.id = 2 then delete
when matched then update set d.val = src.valsrc
returning val, new.val, old.val, src.valsrc

It is also possible to use the table name if that target table wasn't aliased:

merge into dummy2
using (select 1, 'ab' from rdb$database) as src(id, val)
on http://dummy2.id = http://src.id
when matched and http://dummy2.id = 2 then delete
when matched then update set dummy2.val = src.val
returning dummy2.val, new.val, old.val, src.val

I would expect the column in the target table to be referenceable as d.val in all these situations.

summary: RETURNING clause support in MERGE is inconsistent and depends on actions in the WHEN clauses => RETURNING clause in MERGE cannot reference column in aliased target table using qualified reference (alias.column) if DELETE action present

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: 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 http://d.id = http://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 http://d.id = http://src.id
when matched and http://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 http://d.id = http://src.id
when matched and http://d.id = 2 then delete
when matched then update set d.val = src.valsrc
returning val, new.val, old.val, src.valsrc

It is also possible to use the table name if that target table wasn't aliased:

merge into dummy2
using (select 1, 'ab' from rdb$database) as src(id, val)
on http://dummy2.id = http://src.id
when matched and http://dummy2.id = 2 then delete
when matched then update set dummy2.val = src.val
returning dummy2.val, new.val, old.val, src.val

I would expect the column in the target table to be referenceable as d.val in all these situations.

=>

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.

Specifically, if the target table is aliased, then when a DELETE action is present, it is not possible to reference columns in the target table in the returning clause using alias.columnname.

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 http://d.id = http://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 http://d.id = http://src.id
when matched and http://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 http://d.id = http://src.id
when matched and http://d.id = 2 then delete
when matched then update set d.val = src.valsrc
returning val, new.val, old.val, src.valsrc

It is also possible to use the table name if that target table wasn't aliased:

merge into dummy2
using (select 1, 'ab' from rdb$database) as src(id, val)
on http://dummy2.id = http://src.id
when matched and http://dummy2.id = 2 then delete
when matched then update set dummy2.val = src.val
returning dummy2.val, new.val, old.val, src.val

I would expect the column in the target table to be referenceable as d.val in all these situations.

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: 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.

Specifically, if the target table is aliased, then when a DELETE action is present, it is not possible to reference columns in the target table in the returning clause using alias.columnname.

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 http://d.id = http://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 http://d.id = http://src.id
when matched and http://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 http://d.id = http://src.id
when matched and http://d.id = 2 then delete
when matched then update set d.val = src.valsrc
returning val, new.val, old.val, src.valsrc

It is also possible to use the table name if that target table wasn't aliased:

merge into dummy2
using (select 1, 'ab' from rdb$database) as src(id, val)
on http://dummy2.id = http://src.id
when matched and http://dummy2.id = 2 then delete
when matched then update set dummy2.val = src.val
returning dummy2.val, new.val, old.val, src.val

I would expect the column in the target table to be referenceable as d.val in all these situations.

=>

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.

Specifically, if the target table is aliased, then when a DELETE action is present, it is not possible to reference columns in the target table in the returning clause using alias.columnname.

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 http://d.id = http://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 http://d.id = http://src.id
when matched and http://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 http://d.id = http://src.id
when matched and http://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.

It is possible to use the table name if that target table wasn't aliased:

merge into dummy2
using (select 1, 'ab' from rdb$database) as src(id, val)
on http://dummy2.id = http://src.id
when matched and http://dummy2.id = 2 then delete
when matched then update set dummy2.val = src.val
returning dummy2.val, new.val, old.val, src.val

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 3.0.7 [ 10940 ]

Fix Version: 3.0.8 [ 10960 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@asfernandes asfernandes

Labels
affect-version: 3.0.5 affect-version: 3.0.6 affect-version: 3.0.7 affect-version: 4.0 RC 1 component: engine fix-version: 3.0.8 fix-version: 4.0 RC 1 priority: major qa: done successfully type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asfernandes @firebird-automations