db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4610) Error attempting delete with cascade and triggers
Date Tue, 27 Apr 2010 07:51:34 GMT

    [ 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:,,,
>         Environment: Apache Derby 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))
> 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.

View raw message