[jira] [Updated] (DERBY-6474) Add support for synonyms in MERGE statements

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6474?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-6474:
---------------------------------

    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;


  was:
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;



> Add support for synonyms in MERGE statements
> --------------------------------------------
>
>                 Key: DERBY-6474
>                 URL: https://issues.apache.org/jira/browse/DERBY-6474
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>            Priority: Minor
>
>  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;



--
This message was sent by Atlassian JIRA
(v6.2#6252)