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

          Dag H. Wanvik created issue -
          Dag H. Wanvik made changes -
          Field Original Value New Value
          Attachment SetDefaultOnDeleteError.java [ 12394663 ]
          Dag H. Wanvik made changes -
          Link This issue is blocked by DERBY-3962 [ DERBY-3962 ]
          Dag H. Wanvik made changes -
          Link This issue is blocked by DERBY-3962 [ DERBY-3962 ]
          Dag H. Wanvik made changes -
          Link This issue relates to DERBY-3962 [ DERBY-3962 ]
          Rick Hillegas made changes -
          Link This issue is duplicated by DERBY-3968 [ DERBY-3968 ]
          Mike Matrigali made changes -
          Component/s SQL [ 11408 ]
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.
          Knut Anders Hatlen made changes -
          Affects Version/s 10.5.1.1 [ 12313771 ]
          Urgency Normal
          Issue & fix info [Repro attached]
          Dag H. Wanvik made changes -
          Affects Version/s 10.4.2.0 [ 12313345 ]
          Affects Version/s 10.4.1.3 [ 12313111 ]
          Affects Version/s 10.3.3.0 [ 12313142 ]
          Affects Version/s 10.3.2.1 [ 12312876 ]
          Affects Version/s 10.3.1.4 [ 12312590 ]
          Affects Version/s 10.2.2.0 [ 12312027 ]
          Affects Version/s 10.2.1.6 [ 11187 ]
          Affects Version/s 10.1.3.1 [ 12311953 ]
          Affects Version/s 10.1.2.1 [ 12310615 ]
          Affects Version/s 10.1.1.0 [ 10993 ]
          Affects Version/s 10.0.2.1 [ 10991 ]
          Affects Version/s 10.0.2.0 [ 10920 ]
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Gavin made changes -
          Workflow jira [ 12447001 ] Default workflow, editable Closed status [ 12800005 ]

            People

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

              Dates

              • Created:
                Updated:

                Development