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

Violation of deferred constraints not detected when conglomerates are erroneously shared

    XMLWordPrintableJSON

    Details

    • Urgency:
      Blocker
    • Bug behavior facts:
      Data corruption, Wrong query result

      Description

      See the following script:

      ij version 10.11
      ij> connect 'jdbc:derby:memory:db;create=true';
      ij> create table t1(x int primary key);
      0 rows inserted/updated/deleted
      ij> create table t2(x int primary key);
      0 rows inserted/updated/deleted
      ij> create table t3(x int, constraint fk1 foreign key (x) references t1 initially deferred, constraint fk2 foreign key (x) references t2 initially deferred);
      0 rows inserted/updated/deleted
      ij> insert into t1 values 1;
      1 row inserted/updated/deleted
      ij> autocommit off;
      ij> insert into t3 values 1;
      1 row inserted/updated/deleted
      ij> insert into t2 values 1;
      1 row inserted/updated/deleted
      ij> delete from t1;
      1 row inserted/updated/deleted
      ij> commit;
      ij> select * from t1;
      X          
      -----------
      
      0 rows selected
      ij> select * from t2;
      X          
      -----------
      1          
      
      1 row selected
      ij> select * from t3;
      X          
      -----------
      1          
      
      1 row selected
      

      Since T3.X contains a value (1) that is not present in T1, the foreign key FK1 is violated, and the COMMIT statement should have failed.

        Attachments

        1. braindump.diff
          67 kB
          Knut Anders Hatlen
        2. derby-6665-01-aa-remove-uniquePKConstraintModes.diff
          72 kB
          Richard N. Hillegas
        3. derby-6665-01-ab-useTableUUIDforCheckConstraints.diff
          82 kB
          Richard N. Hillegas
        4. derby-6665-01-ac-unpositionedScan.diff
          84 kB
          Richard N. Hillegas
        5. derby-6665-01-ad-import-and-unique.diff
          84 kB
          Richard N. Hillegas
        6. derby-6665-01-ae-deferredCheckAndDroppedFK.diff
          83 kB
          Richard N. Hillegas
        7. derby-6665-dhw-dont-share.diff
          13 kB
          Dag H. Wanvik
        8. junit.diff
          4 kB
          Knut Anders Hatlen

          Issue Links

            Activity

              People

              • Assignee:
                dagw Dag H. Wanvik
                Reporter:
                knutanders Knut Anders Hatlen
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: