[jira] [Commented] (DERBY-6666) Deferred constraint validation fails with "dead statement" when query plan logging is enabled

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6666?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14078684#comment-14078684 ] 

Dag H. Wanvik commented on DERBY-6666:
--------------------------------------

 With the patch derby-6670-2-b, I can no longer see this problem.
{code}
$ java -Dderby.language.logQueryPlan=true -jar jars/sane/derbyrun.jar ij foo.sql
ij version 10.11
ij> connect 'jdbc:derby:memory:db;create=true';
ij> create table t1(x int primary key);
0 rows inserted/updated/deleted
ij> create table t2(y int, constraint c check(y > 0) initially deferred, constraint fk foreign key(y) references t1 initially deferred);
0 rows inserted/updated/deleted
ij> autocommit off;
ij> insert into t1 values -1, 1;
2 rows inserted/updated/deleted
ij> insert into t2 values 1;
1 row inserted/updated/deleted
ij> update t2 set y = -1;
1 row inserted/updated/deleted
ij> delete from t1 where x = -1;
1 row inserted/updated/deleted
ij> commit;
ERROR 23516: The transaction was aborted because of a deferred constraint violation: Foreign key 'FK' defined on "APP"."T1" referencing constraint 'SQL140730022103580' defined on "APP"."T1", key '(-1)'.
ij> 
{code}

> Deferred constraint validation fails with "dead statement" when query plan logging is enabled
> ---------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6666
>                 URL: https://issues.apache.org/jira/browse/DERBY-6666
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Knut Anders Hatlen
>
> Run the following script with the {{derby.language.logQueryPlan}} system property set to {{true}}:
> {code:sql}
> connect 'jdbc:derby:memory:db;create=true';
> create table t1(x int primary key);
> create table t2(y int, constraint c check(y > 0) initially deferred, constraint fk foreign key(y) references t1 initially deferred);
> autocommit off;
> insert into t1 values -1, 1;
> insert into t2 values 1;
> update t2 set y = -1;
> delete from t1 where x = -1;
> commit;
> {code}
> The commit statement will fail with the following error message: "ERROR 40XC0: Dead statement. This may be caused by catching a transaction severity error inside this statement."
> If you run the script without setting the {{derby.language.logQueryPlan}} system property, it will fail (correctly) with this error message: "ERROR 23514: The transaction was aborted because of a deferred constraint violation: Check constraint identified by 'C' defined on "APP"."T2" as '(y > 0)'."



--
This message was sent by Atlassian JIRA
(v6.2#6252)