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

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.11.1.1
    • None
    • SQL
    • None
    • Normal
    • 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

              Unassigned Unassigned
              rhillegas Richard N. Hillegas
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: