You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "ASF subversion and git services (JIRA)" <ji...@apache.org> on 2014/07/10 14:20:05 UTC
[jira] [Commented] (DERBY-6652) Compilation error using a view as
the source data set for a MERGE statement
[ 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)