[DERBY-6474] Add support for synonyms in MERGE statements

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 10.11.1.1
• Component/s: SQL
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 10/Feb/14 13:44
• Updated: 04/Mar/14 19:03

Description

Synonyms are not allowed in Derby's implementation of the MERGE statement. They are tricky to resolve. If you comment out the call to forbidSynonyms() in MergeNode, you will see the following behavior:

connect 'jdbc:derby:memory:db;create=true';

create table t1( x int, y int );
create table t2( x int, y int );
create synonym syn_t1 for t1;
create synonym syn_t2 for t2;

– ok
merge into syn_t1 a
using t2 on a.x = t2.x
when matched then update set a.y = t2.y;

– ok
merge into syn_t1 a
using t2 on a.x = t2.x
when matched then update set y = t2.y;

– can't resolve the left side of the SET clause
merge into syn_t1
using t2 on syn_t1.x = t2.x
when matched then update set syn_t1.y = t2.y;

– can't resolve the left side of the SET clause
merge into syn_t1
using t2 on syn_t1.x = t2.x
when matched then update set y = t2.y;

– ok
merge into t1
using syn_t2 a on a.x = a.x
when matched then update set y = a.y;

– assertion failure trying to resolve right side of SET clause
merge into t1
using syn_t2 on syn_t2.x = syn_t2.x
when matched then update set y = syn_t2.y;

Issue Links

• relates to: DERBY-3155 Support for SQL:2003 MERGE statement