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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 09/Jul/14 12:38
• Updated: 09/Sep/16 09:53
• Resolved: 10/Jul/14 12:19

Description

Mamta found the following problem when using a view as the source data set of a MERGE statement:

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

Issue Links

• is related to: DERBY-6657 Need to document the fact that views can't be the source data sets of MERGE statements
• is related to: DERBY-6656 Re-enable views as the source data sets of MERGE statements
• relates to: DERBY-3155 Support for SQL:2003 MERGE statement