db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6652) Compilation error using a view as the source data set for a MERGE statement
Date Thu, 10 Jul 2014 12:20:05 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14057414#comment-14057414
] 

ASF subversion and git services commented on DERBY-6652:
--------------------------------------------------------

Commit 1609450 from [~rhillegas] in branch 'code/trunk'
[ https://svn.apache.org/r1609450 ]

DERBY-6652: Disable the use of views as the source data sets of MERGE statements; tests passed
cleanly for me on derby-6652-01-aa-disableSourceViews.diff.

> Compilation error using a view as the source data set for a MERGE statement
> ---------------------------------------------------------------------------
>
>                 Key: DERBY-6652
>                 URL: https://issues.apache.org/jira/browse/DERBY-6652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-6652-01-aa-disableSourceViews.diff
>
>
> Mamta found the following problem when using a view as the source data set of a MERGE
statement:
> {noformat}
> connect 'jdbc:derby:memory:db;create=true';
> CREATE TABLE employee (
> employee_id int,
> first_name  VARCHAR(20),
> last_name   VARCHAR(20),
> dept_no     int,
> salary      int);
> create view v1employee as select * from employee;
> CREATE TABLE bonuses (
> employee_id int, bonus int DEFAULT 100);
> create view v2bonuses as select * from bonuses;
> -- compiles without the view
> MERGE INTO bonuses B
> USING employee E
> ON B.employee_id = E.employee_id
> WHEN MATCHED AND E.dept_no=20 THEN
>   UPDATE SET B.bonus = E.salary * 0.1
> WHEN NOT MATCHED AND dept_no=20 THEN
>   INSERT (employee_id, bonus)
>   VALUES (E.employee_id, E.salary * 0.05);
> -- compilation error with the view
> MERGE INTO bonuses B
> USING v1employee E
> ON B.employee_id = E.employee_id
> WHEN MATCHED AND E.dept_no=20 THEN
>   UPDATE SET B.bonus = E.salary * 0.1
> WHEN NOT MATCHED AND dept_no=20 THEN
>   INSERT (employee_id, bonus)
>   VALUES (E.employee_id, E.salary * 0.05);
> {noformat}



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

Mime
View raw message