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

Referential action ON DELETE SET DEFAULT does not work correctly

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      The ON DELETE SET DEFAULT action is not documented (cf. DERBY-3962), but Derby accepts the construct.
      Example from JUnit repro SetDefaultOnDeleteError.java:

      > create table dhw( a int default 2, constraint y foreign key(a) references referencedTable(a) on delete set default)

      However, it does not work as expected. It would seem that when the referenced table's row is deleted,
      the constraint is checked with the old value of the referencing table (DHW.A) instead of the value
      after updating the column with the default as required. After updating to the default value the new value should
      be checked again against the referenced table, and if there is a row corresponding to the default value, the
      operation should succeed. Derby fails with error:

      ERROR 23503: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back

      Stacktrace:

      1) test_1(org.apache.derbyTesting.functionTests.tests.lang.SetDefaultOnDeleteError)java.sql.SQLIntegrityConstraintViolationException: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:81)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
      at org.apache.derbyTesting.functionTests.tests.lang.SetDefaultOnDeleteError.test_1(SetDefaultOnDeleteError.java:58)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105)
      at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
      at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
      at junit.extensions.TestSetup.run(TestSetup.java:25)
      at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
      at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
      at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
      at junit.extensions.TestSetup.run(TestSetup.java:25)
      at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
      Caused by: java.sql.SQLException: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
      ... 37 more
      Caused by: ERROR 23503: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back.
      at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:373)
      at org.apache.derby.impl.sql.execute.ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
      at org.apache.derby.impl.sql.execute.RISetChecker.doPKCheck(RISetChecker.java:97)
      at org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows(DeleteResultSet.java:393)
      at org.apache.derby.impl.sql.execute.DeleteResultSet.open(DeleteResultSet.java:137)
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
      ... 31 more

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                dagw Dag H. Wanvik
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: