[jira] Updated: (DERBY-3963) Referential action ON DELETE SET DEFAULT does not work correctly
[ https://issues.apache.org/jira/browse/DERBY-3963?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-3963: - 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 > Referential action ON DELETE SET DEFAULT does not work correctly > > > Key: DERBY-3963 > URL: https://issues.apache.org/jira/browse/DERBY-3963 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 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 >Reporter: Dag H. Wanvik > Attachments: SetDefaultOnDeleteError.java > > > 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
[jira] Updated: (DERBY-3963) Referential action ON DELETE SET DEFAULT does not work correctly
[ https://issues.apache.org/jira/browse/DERBY-3963?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Knut Anders Hatlen updated DERBY-3963: -- Issue & fix info: [Repro attached] Urgency: Normal Affects Version/s: 10.5.1.1 Triaged for 10.5.2. > Referential action ON DELETE SET DEFAULT does not work correctly > > > Key: DERBY-3963 > URL: https://issues.apache.org/jira/browse/DERBY-3963 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.5.1.1 >Reporter: Dag H. Wanvik > Attachments: SetDefaultOnDeleteError.java > > > 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.a
[jira] Updated: (DERBY-3963) Referential action ON DELETE SET DEFAULT does not work correctly
[ https://issues.apache.org/jira/browse/DERBY-3963?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali updated DERBY-3963: -- Component/s: SQL > Referential action ON DELETE SET DEFAULT does not work correctly > > > Key: DERBY-3963 > URL: https://issues.apache.org/jira/browse/DERBY-3963 > Project: Derby > Issue Type: Bug > Components: SQL >Reporter: Dag H. Wanvik > Attachments: SetDefaultOnDeleteError.java > > > 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.Del
[jira] Updated: (DERBY-3963) Referential action ON DELETE SET DEFAULT does not work correctly
[ https://issues.apache.org/jira/browse/DERBY-3963?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-3963: - Attachment: SetDefaultOnDeleteError.java > Referential action ON DELETE SET DEFAULT does not work correctly > > > Key: DERBY-3963 > URL: https://issues.apache.org/jira/browse/DERBY-3963 > Project: Derby > Issue Type: Bug >Reporter: Dag H. Wanvik > Attachments: SetDefaultOnDeleteError.java > > > 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.DeleteRe
