[jira] [Updated] (DERBY-6652) Compilation error using a view as the source data set for a MERGE statement

["Rick Hillegas (JIRA)" <ji...@apache.org>]

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

Rick Hillegas updated DERBY-6652:
---------------------------------

    Attachment: derby-6652-01-aa-disableSourceViews.diff

Attaching derby-6652-01-aa-disableSourceViews.diff. This patch disables views as the source data sets for MERGE statements. I will run tests.

It is late in the release and it appears that we still have view resolution problems with MERGE statements. So I am disabling the ability to use views as source data sets. I will file a follow-on issue to re-enable this capability after we clean up the namespace resolution problems with MERGE.

And I will need to file a doc issue to clarify that views are no longer allowed in this situation.

As part of this fix, I had to rework some of the tests to use table functions instead of views in order to simulate 1 and 2 row data sources. Alternatively, you could use SYSIBM.SYSDUMMY1 I suppose.  

Touches the following files:

-------------------

M       java/engine/org/apache/derby/impl/sql/compile/MergeNode.java

Prevent views from being used as sources.

-------------------

M       java/engine/org/apache/derby/loc/messages.xml
M       java/shared/org/apache/derby/shared/common/reference/SQLState.java

Changed the wording of an error message to indicate that views are no longer allowed in this situation.

-------------------

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java

Adjustments to tests.


> 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)