Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6652

Compilation error using a view as the source data set for a MERGE statement

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

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

        Attachments

        1. derby-6652-01-aa-disableSourceViews.diff
          15 kB
          Richard N. Hillegas

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                rhillegas Richard N. Hillegas
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: