You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Eric Long (JIRA)" <ji...@apache.org> on 2010/04/09 14:29:50 UTC
[jira] Created: (DERBY-4610) Error attempting delete with cascade
and triggers
Error attempting delete with cascade and triggers
-------------------------------------------------
Key: DERBY-4610
URL: https://issues.apache.org/jira/browse/DERBY-4610
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.5.3.0
Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
Reporter: Eric Long
Fix For: 10.5.3.1
The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
First, compile TestFunctions.java and put it on the classpath:
public class TestFunctions
{
public static void test(String str)
{
}
}
Next, enter commands into interactive SQL:
create table testtable (id integer, name varchar(20), primary key(id));
create table testchild (
id integer
constraint fk_id references testtable on delete cascade,
ordernum int,
primary key(id));
create procedure testproc (str varchar(20))
PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
create trigger testtabletrigger after delete on testtable referencing old as old
for each row mode db2sql call testproc(char(old.id));
create trigger testchildtrigger after delete on testchild referencing old as old
for each row mode db2sql call testproc(char(old.ordernum));
insert into testtable values (1, 'test1');
insert into testchild values (1, 10);
delete from testtable where id = 1;
The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
type 'INTEGER'.
SQLState: XCL12
ErrorCode: 30000
There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12862589#action_12862589 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
Possibly. I'm afraid I'm not very familiar with this code and don't know how it's supposed to work. I've noticed though that there is some code that attempts something similar in GenericTriggerExecutor.executeSPS(), by setting up a separate activation for the SPS.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12887805#action_12887805 ]
Mamta A. Satoor commented on DERBY-4610:
----------------------------------------
I will work on backporting this to 10.5
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861292#action_12861292 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
The bug was caused by this part of the 572753 check-in:
Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (revision 572752)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (working copy)
@@ -251,7 +251,7 @@
}
// Fill in the column types
- resultDescription = theResults.getResultDescription();
+ resultDescription = theResults.getActivation().getResultDescription();
// Only incur the cost of allocating and maintaining
// updated column information if the columns can be updated.
One assumption for the DERBY-3049 changes was that the result description was an attribute of the activation. However, DeleteResultSet and UpdateResultSet, when called from a trigger, get their result descriptions from the saved plan, not from the activation. So the above change appears to have made EmbedResultSet pick up the result description for the statement that caused the trigger to execute, not the one executing in the trigger.
Reverting the changes on that single line in revision 572753 makes the test case pass. However, the getResultDescription() methods in the language result set classes have later been removed, so that simple fix is no longer possible.
We may fix this by reintroducing some of the getResultDescription() methods removed in DERBY-3049. Since DERBY-3049 removed this method because its name could cause confusion, we should probably also rename it to something that makes it clearer what it does, for example getTopLevelResultDescription().
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861710#action_12861710 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
Hi Bryan,
Here's the stack trace for the call to getResultDescription() from EmbedResultSet:
java.lang.Exception: Stack trace
at java.lang.Thread.dumpStack(Thread.java:1206)
at org.apache.derby.impl.jdbc.EmbedResultSet.<init>(EmbedResultSet.java:259)
at org.apache.derby.impl.jdbc.EmbedResultSet20.<init>(EmbedResultSet20.java:71)
at org.apache.derby.impl.jdbc.EmbedResultSet40.<init>(EmbedResultSet40.java:53)
at org.apache.derby.jdbc.Driver40.newEmbedResultSet(Driver40.java:136)
at org.apache.derby.impl.jdbc.EmbedConnectionContext.getResultSet(EmbedConnectionContext.java:132)
at org.apache.derby.impl.sql.execute.InternalTriggerExecutionContext.getOldRowSet(InternalTriggerExecutionContext.java:496)
at org.apache.derby.impl.sql.execute.InternalTriggerExecutionContext.getOldRow(InternalTriggerExecutionContext.java:561)
at org.apache.derby.exe.ac0b5b0099x0128x435ex16eex0000003b00901.g0(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(ReflectMethod.java:46)
at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(CallStatementResultSet.java:75)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatement(GenericPreparedStatement.java:306)
at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:159)
at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:111)
at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:269)
at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(DeleteResultSet.java:459)
at org.apache.derby.impl.sql.execute.DeleteCascadeResultSet.fireAfterTriggers(DeleteCascadeResultSet.java:263)
at org.apache.derby.impl.sql.execute.DeleteCascadeResultSet.fireAfterTriggers(DeleteCascadeResultSet.java:256)
at org.apache.derby.impl.sql.execute.DeleteCascadeResultSet.open(DeleteCascadeResultSet.java:136)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367)
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890224#action_12890224 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
Mamta, I've posted my comments to the patch over at DERBY-4601.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: DERBY4601_patch1_diff.txt, DERBY4601_stat1_diff.txt, enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886792#action_12886792 ]
Mamta A. Satoor commented on DERBY-4610:
----------------------------------------
Will backport this to 10.5
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4610:
--------------------------------------
Attachment: enable_test.diff
Attaching a patch that enables the regression test case for this bug.
Committed revision 940469.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890314#action_12890314 ]
Mamta A. Satoor commented on DERBY-4610:
----------------------------------------
Yes, I did mean to post the patch to DERBY-4601.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: DERBY4601_patch1_diff.txt, enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reassigned DERBY-4610:
-----------------------------------------
Assignee: Knut Anders Hatlen (was: Mamta A. Satoor)
Reassigning the issue now that the back-port to 10.5 has been completed.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Fix For: 10.5.3.1, 10.6.2.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12863358#action_12863358 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
I reverse merged the commits from DERBY-3049 for now and committed revision 940462. If we find a safe way to add those changes, we can always reintroduce them later. I'll look into back-porting to 10.6 in a couple of days if there are no problems in the nightly tests.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor reassigned DERBY-4610:
--------------------------------------
Assignee: Mamta A. Satoor (was: Knut Anders Hatlen)
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reopened DERBY-4610:
-----------------------------------
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861660#action_12861660 ]
Bryan Pendleton commented on DERBY-4610:
----------------------------------------
Hmmm. That's a fairly subtle interaction. Maybe Dan wasn't thinking about
triggers when he did the DERBY-3049 work?
What does the call stack look like when the repro script reaches the
code at line 251 of EmbedResultSet?
I see the following interesting code at around line 185 of UpdateResultSet:
ResultDescription resultDescription;
if(passedInRsd ==null)
resultDescription = activation.getResultDescription();
else
resultDescription = passedInRsd;
/*
** We NEED a result description when we are going to
** to have to kick off a trigger. In a replicated environment
** we don't get a result description when we are replaying
** source xacts on the target, which should never be the
** case for an UpdateResultSet.
*/
if (SanityManager.DEBUG)
{
if (resultDescription == null)
{
SanityManager.ASSERT(triggerInfo == null, "triggers need a result description to pass to result sets given to users");
}
}
Would it work to have UpdateResultSet send 'passedInRsd' up to the
superclass constructor, and then EmbedResultSet's constructor could
have similar logic to use either passedInRsd or the activation's resultDescription,
depending on whether passedInRsd was null or not?
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4610:
--------------------------------------
Attachment: junit.diff
I wrote a JUnit test case based on the repro in the bug description. See the attached patch junit.diff.
The test case is not yet enabled as part of a test suite, since it currently fails.
Committed to trunk with revision 938959.
The test case can be run with the following command:
java junit.textui.TestRunner -m org.apache.derbyTesting.functionTests.tests.lang.TriggerTest.xtestDerby4610WrongDataType
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865338#action_12865338 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
Hi Glenn,
Unfortunately, the fix was too late to get into the 10.6.1.0 release candidate that's currently being tested and voted on. So unless the current release candidate is rejected, it won't be part of the upcoming 10.6.1 release and will have to wait to the first 10.6 maintenance release (no plans exist for that release yet, I'm afraid).
If you're interested in testing the fix without building from source, the binaries built for the nightly regression tests of the 10.6 branch can be found here: http://dbtg.foundry.sun.com/derby/bits/10.6/
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reassigned DERBY-4610:
-----------------------------------------
Assignee: Knut Anders Hatlen
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4610:
-----------------------------------
Attachment: DERBY4601_patch1_diff.txt
DERBY4601_stat1_diff.txt
Attaching a patch which will log the Engine shutdown message in derby.log. The existing message logging for each individual connection close which happen because of engine shutdown will still be logged but they will now print the name of the database being shutdown.
The existing regression tests ran fine. If there is no objection to this patch, I will commit it by Wednesday morning.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: DERBY4601_patch1_diff.txt, DERBY4601_stat1_diff.txt, enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Glenn Bradford (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12865164#action_12865164 ]
Glenn Bradford commented on DERBY-4610:
---------------------------------------
Thanks Knut Anders for resolving this! When can I expect to see a stable version with the fix in it? Or is 10.6.1.1 available now.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12860222#action_12860222 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
The test case in the bug description started failing with this commit:
------------------------------------------------------------------------
r572753 | djd | 2007-09-04 19:48:20 +0200 (Tue, 04 Sep 2007) | 1 line
DERBY-3049 (partial) Change EmbedResultSet to get a ResultDescription from the activation. Cleanup some ResultSet implementations of getResultDescription()
------------------------------------------------------------------------
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.0.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-4610:
---------------------------------
Affects Version/s: 10.6.0.0
Fix Version/s: (was: 10.5.3.1)
Bug behavior facts: (was: [Wrong query result])
Thanks for finding this bug, Eric. I have verified this failure on the trunk. I unchecked the "Wrong query result" box. To the derby developers, that box indicates that a query succeeds but silently does the wrong thing. In this case the query complains as it outright fails. I also set Fix Versions to "Unknown" since we don't know yet what release will fix this defect. Thanks.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.3.0, 10.6.0.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4610:
-----------------------------------
Attachment: (was: DERBY4601_stat1_diff.txt)
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: DERBY4601_patch1_diff.txt, enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890220#action_12890220 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
I suppose the patch was meant to be attached to DERBY-4601?
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: DERBY4601_patch1_diff.txt, DERBY4601_stat1_diff.txt, enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Glenn Bradford (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12859382#action_12859382 ]
Glenn Bradford commented on DERBY-4610:
---------------------------------------
Is anyone looking at this bug? I would be very interested in trying a patch fix for this when it's available, or being notified of a possible workaround, as this is a serious issue for our project. Thank you.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.3.0, 10.6.0.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861787#action_12861787 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
All the regression tests ran cleanly with the DERBY-3049 changes backed out.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor reassigned DERBY-4610:
--------------------------------------
Assignee: Knut Anders Hatlen (was: Mamta A. Satoor)
sorry, unassigning myself from this backport. Another backport (for a different jira) of mine is failing and I want to see if I can figure out what might be wrong there before doing a new backport.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12862184#action_12862184 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
I agree that a test case that demonstrates the path through UpdateResultSet would be useful. Unfortunately, I don't know how to construct one that fails. There seems to be some interaction between delete triggers and cascading deletes in the original repro, and since we don't have cascading updates (DERBY-735), there's no obvious translation into a test case for update.
One interesting point, though: When running the original repro, none of the DeleteResultSets use the passed in ResultDescription. They all take the one from the activation. What seems to happen, is that the Activation instance is reinitialized (setupActivation() is called) sometime between the call to DeleteResultSet's constructor and the call to getResultDescription() in EmbedResultSet. So even though it's true that ResultSet.getResultDescription() returns the same as Activation.getResultSetDescription() at the time the DeleteResultSet is created, this is not true when we reach the point in around line 251 in EmbedResultSet, because the ResultDescription in the activation has changed.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4610:
--------------------------------------
Bug behavior facts: [Regression]
Affects Version/s: 10.4.2.0
10.4.1.3
Hi Glenn,
Thanks for volunteering to help testing a fix.
I found that this error is not seen on Derby 10.3.3.0 and earlier, so I'm marking it as a regression. This will increase the visibility of the bug and the likelihood that someone picks it up.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.0.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-4610.
---------------------------------------
Fix Version/s: 10.6.1.1
10.7.0.0
Resolution: Fixed
Merged the fix and the test to the 10.6 branch and committed revision 942027.
Marking the issue as resolved.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Knut Anders Hatlen
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-4610.
------------------------------------
Fix Version/s: 10.5.3.1
Resolution: Fixed
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12862207#action_12862207 ]
Bryan Pendleton commented on DERBY-4610:
----------------------------------------
Perhaps this problem can be managed in InternalTriggerExecutionContext?
It seems like that is the place where we are trying to manage the beforeResultSet
and ensure it can be used by the trigger. So maybe when
InternalTriggerExecutionContext.setBeforeResultSet is called, it can, in addition
to saving the beforeResultSet, also save a clone of the beforeResultSet's activation,
or perhaps just the resultDescription, so that, later, when
InternalTriggerExecutionContext.getOldResultSet is called, it can arrange for
the beforeResultSet's activation to point to the correct result description.
This would confine the work to trigger context management, which seems like
the appropriate place for the work to be occurring.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861766#action_12861766 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
We always have the option to back out DERBY-3049. Since that issue was just a cleanup issue that wasn't supposed to fix or change the behaviour of anything, backing it out shouldn't do much harm. As long as it seems that one of the assumptions in that issue isn't sound and we don't fully understand which consequences this has for the various parts of the code, backing out the whole thing may be safer than trying to guess which parts have to be changed and which are safe to leave as they are.
For the record, the following svn command successfully reverted all the commits from DERBY-3049, with no conflicts:
$ svn merge -c -601395,-600678,-598739,-572753 .
To get it to compile, I had to add import statements for the ResultDescription class in BasicNoPutResultSetImpl and TemporaryRowHolderImpl, since they apparently had been removed later. With that change, it compiled and the repro script ran without error. I'm running derbyall and suites.All now to see if any of the regression tests are affected by it.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861805#action_12861805 ]
Bryan Pendleton commented on DERBY-4610:
----------------------------------------
> backing out the whole thing may be safer than trying to guess
> which parts have to be changed and which are safe to leave as they are.
I agree. I'm comfortable with that resolution, though I might use a
word like "discover" or "determine" rather than "guess" :)
Since backing out DERBY-3049 reveals no new problems, and since it
resolves the scenarios raised by this issue, it definitely seems the safest.
Your new test case seems good to me. Is it worth having a separate
test case which fires the trigger by way of an UPDATE statement rather
than a DELETE statement, to demonstrate the code path via UpdateResultSet?
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Attachments: junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor reassigned DERBY-4610:
--------------------------------------
Assignee: Mamta A. Satoor (was: Knut Anders Hatlen)
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-4610:
-----------------------------------
Attachment: (was: DERBY4601_patch1_diff.txt)
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
> Assignee: Mamta A. Satoor
> Fix For: 10.5.3.1, 10.6.1.1, 10.7.0.0
>
> Attachments: enable_test.diff, junit.diff
>
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4610) Error attempting delete with cascade
and triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12861739#action_12861739 ]
Knut Anders Hatlen commented on DERBY-4610:
-------------------------------------------
> Would it work to have UpdateResultSet send 'passedInRsd' up to the
> superclass constructor, and then EmbedResultSet's constructor could
> have similar logic to use either passedInRsd or the activation's resultDescription,
> depending on whether passedInRsd was null or not?
That might work. A couple of things to notice are:
- There's similar logic in DeleteResultSet's constructor. I'm not
quite sure if it's UpdateResultSet or DeleteResultSet that comes
into play here. We probably need to change both.
- The result set in EmbedResultSet.theResults is a
TemporaryRowHolderImplResultSet, and there's a number of indirection
levels between the source and the holder result set. The code that
passes the result description from the source to the holder result
set would need to be restored.
- In the DERBY-3049 commits that followed 572753, lots of other calls
to ResultSet.getResultDescription() were replaced by calls to
Activation.getResultDescription(). It's difficult to say if those
are safe, or if the same logic as you suggested in EmbedResultSet
needs to be added to each of these calls.
> Error attempting delete with cascade and triggers
> -------------------------------------------------
>
> Key: DERBY-4610
> URL: https://issues.apache.org/jira/browse/DERBY-4610
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Apache Derby 10.5.3.0 and Sun JDK 1.6.0_07
> Reporter: Eric Long
>
> The scenario is a parent and child table with a cascade delete and triggers on both tables. Here are the steps to reproduce.
> First, compile TestFunctions.java and put it on the classpath:
> public class TestFunctions
> {
> public static void test(String str)
> {
> }
> }
> Next, enter commands into interactive SQL:
> create table testtable (id integer, name varchar(20), primary key(id));
> create table testchild (
> id integer
> constraint fk_id references testtable on delete cascade,
> ordernum int,
> primary key(id));
> create procedure testproc (str varchar(20))
> PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME 'TestFunctions.test';
> create trigger testtabletrigger after delete on testtable referencing old as old
> for each row mode db2sql call testproc(char(old.id));
> create trigger testchildtrigger after delete on testchild referencing old as old
> for each row mode db2sql call testproc(char(old.ordernum));
> insert into testtable values (1, 'test1');
> insert into testchild values (1, 10);
> delete from testtable where id = 1;
> The expected result is that deleting a row from "testtable" will cascade the delete to "testchild", and the triggers will be called for each delete. The actual result is that the delete is rolled back with the following error:
> Error: An attempt was made to put a data value of type 'java.lang.String' into a data value of
> type 'INTEGER'.
> SQLState: XCL12
> ErrorCode: 30000
> There are no additional entries in the derby.log after the error. If only one trigger is used, or if the cascade is removed, then the delete will succeed.
> This issue was found while using SymmetricDS, which uses triggers to replicate tables between Derby databases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.