Derby
  1. Derby
  2. DERBY-3963

Referential action ON DELETE SET DEFAULT does not work correctly

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major 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

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.

            People

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

              Dates

              • Created:
                Updated:

                Development