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

[Rick Hillegas (JIRA)]

Rick Hillegas created DERBY-6652:
------------------------------------

             Summary: 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


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)