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