Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <2956234.53681272446073839.JavaMail.jira@thor>
Date: Wed, 28 Apr 2010 05:14:33 -0400 (EDT)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4610) Error attempting delete with cascade
 and triggers
In-Reply-To: <1441724085.5801270816190773.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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