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 Thu, 29 Apr 2010 12:42:54 GMT

    [ 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.


Mime
View raw message