Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 18237 invoked from network); 4 Dec 2006 21:08:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 4 Dec 2006 21:08:10 -0000 Received: (qmail 83951 invoked by uid 500); 4 Dec 2006 21:08:19 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 83913 invoked by uid 500); 4 Dec 2006 21:08:19 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 83902 invoked by uid 99); 4 Dec 2006 21:08:19 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 04 Dec 2006 13:08:19 -0800 X-ASF-Spam-Status: No, hits=-9.2 required=10.0 tests=ALL_TRUSTED,MAILTO_TO_SPAM_ADDR,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 04 Dec 2006 13:08:09 -0800 Received: by eris.apache.org (Postfix, from userid 65534) id E29821A9846; Mon, 4 Dec 2006 13:07:28 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r482358 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/ Date: Mon, 04 Dec 2006 21:07:28 -0000 To: derby-commits@db.apache.org From: bpendleton@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20061204210728.E29821A9846@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: bpendleton Date: Mon Dec 4 13:07:25 2006 New Revision: 482358 URL: http://svn.apache.org/viewvc?view=rev&rev=482358 Log: DERBY-1204: TRIGGER with multi-row INSERT action throws OutOfBounds exception This patch was contributed by Yip Ng (yipng168@gmail.com) The statement fails because the bindReferencesClause() method in CreateTriggerNode did not filter those column references that are not relevent to the conversion processing for OLD/NEW transition variables. The above trigger action will have the following parse tree: (roughly) InsertNode | UnionNode / \ UnionNode RowRSNode / \ | RowRSNode RowRSNode NumericConstantNode | | | ColRef NumericConstantNode 333 | | n.i 999 Note that the CollectNodesVisitor collects *ALL* the ColumnReferences in the trigger action. In the above case, it returned 3 column references nodes. One in the top level Union node, one in top level Union Node's left child which it is also an UnionNode and the last one is found in the RowResultSetNode. The UnionNodes will have a copy of the CRs from its left child and those CRs will not have its beginOffset set which indicates they are not relevant for the conversion processing here, so the corrective action here is to skip these entries. Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?view=diff&rev=482358&r1=482357&r2=482358 ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Mon Dec 4 13:07:25 2006 @@ -390,6 +390,31 @@ for (int i = 0; i < cols.length; i++) { ColumnReference ref = (ColumnReference) cols[i]; + + /* + ** Only occurrences of those OLD/NEW transition tables/variables + ** are of interest here. There may be intermediate nodes in the + ** parse tree that have its own RCL which contains copy of + ** column references(CR) from other nodes. e.g.: + ** + ** CREATE TRIGGER tt + ** AFTER INSERT ON x + ** REFERENCING NEW AS n + ** FOR EACH ROW + ** INSERT INTO y VALUES (n.i), (999), (333); + ** + ** The above trigger action will result in InsertNode that + ** contains a UnionNode of RowResultSetNodes. The UnionNode + ** will have a copy of the CRs from its left child and those CRs + ** will not have its beginOffset set which indicates they are + ** not relevant for the conversion processing here, so we can + ** safely skip them. + */ + if (ref.getBeginOffset() == -1) + { + continue; + } + TableName tableName = ref.getTableNameNode(); if ((tableName == null) || ((oldTableName == null || !oldTableName.equals(tableName.getTableName())) && Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?view=diff&rev=482358&r1=482357&r2=482358 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out Mon Dec 4 13:07:25 2006 @@ -921,16 +921,7 @@ ----------- ij> drop trigger tgood; 0 rows inserted/updated/deleted -ij> -- DERBY-1204 trigger causes StringIndexOutOfBoundsException --- which half closes connection and causes rest of test to --- fail. Enable this trigger test case to resolve 1204. --- create trigger tgood after insert on x --- referencing new as n --- for each row insert into x values (n.x), (999), (333); --- insert into x values 1; --- select * from x order by 1; --- drop trigger tgood; -drop table x; +ij> drop table x; 0 rows inserted/updated/deleted ij> -- Derby-388: When a set of inserts/updates is performed on a table -- and each update fires a trigger that in turn performs other updates, @@ -1399,5 +1390,123 @@ ij(USER1)> drop table topt2; 0 rows inserted/updated/deleted ij(USER1)> drop table topt1; +0 rows inserted/updated/deleted +ij(USER1)> -- DERBY-1204 +-- trigger causes StringIndexOutOfBoundsException +-- which half closes connection and causes rest of test to +-- fail. Enable this trigger test case to resolve 1204. +create table x (x int); +0 rows inserted/updated/deleted +ij(USER1)> -- ok +create trigger tgood after insert on x +referencing new as n +for each row insert into x values (n.x), (999), (333); +0 rows inserted/updated/deleted +ij(USER1)> insert into x values 1; +ERROR 54038: Maximum depth of nested triggers was exceeded. +ij(USER1)> select * from x order by 1; +X +----------- +ij(USER1)> drop trigger tgood; +0 rows inserted/updated/deleted +ij(USER1)> drop table x; +0 rows inserted/updated/deleted +ij(USER1)> create table x (i int); +0 rows inserted/updated/deleted +ij(USER1)> create table y (i int); +0 rows inserted/updated/deleted +ij(USER1)> -- ok +create trigger tgood after insert on x +for each statement insert into y values (666), (999), (333); +0 rows inserted/updated/deleted +ij(USER1)> drop trigger tgood; +0 rows inserted/updated/deleted +ij(USER1)> -- ok +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (n.i); +0 rows inserted/updated/deleted +ij(USER1)> drop trigger tgood; +0 rows inserted/updated/deleted +ij(USER1)> -- ok +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (333), (999), (333); +0 rows inserted/updated/deleted +ij(USER1)> drop trigger tgood; +0 rows inserted/updated/deleted +ij(USER1)> -- ok. This used to throw StringIndexOutOfBoundsException +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (n.i), (999), (333); +0 rows inserted/updated/deleted +ij(USER1)> insert into x values (888); +1 row inserted/updated/deleted +ij(USER1)> select * from y; +I +----------- +888 +999 +333 +ij(USER1)> drop trigger tgood; +0 rows inserted/updated/deleted +ij(USER1)> delete from x; +1 row inserted/updated/deleted +ij(USER1)> delete from y; +3 rows inserted/updated/deleted +ij(USER1)> create trigger tgood after insert on x +referencing new as n +for each row insert into y values (n.i), (n.i+1), (n.i+2); +0 rows inserted/updated/deleted +ij(USER1)> insert into x values (1), (4), (7); +3 rows inserted/updated/deleted +ij(USER1)> select * from y; +I +----------- +1 +2 +3 +4 +5 +6 +7 +8 +9 +ij(USER1)> drop trigger tgood; +0 rows inserted/updated/deleted +ij(USER1)> drop table x; +0 rows inserted/updated/deleted +ij(USER1)> drop table y; +0 rows inserted/updated/deleted +ij(USER1)> create table x (i int, j varchar(10)); +0 rows inserted/updated/deleted +ij(USER1)> create table y (i int, j varchar(10)); +0 rows inserted/updated/deleted +ij(USER1)> create trigger tgood after insert on x +referencing new as n +for each row insert into y values (0, 'X'), (n.i, 'Y'), (0, n.j), (n.i,n.j); +0 rows inserted/updated/deleted +ij(USER1)> insert into x values (1,'A'), (2,'B'), (3, 'C'); +3 rows inserted/updated/deleted +ij(USER1)> select * from y; +I |J +---------------------- +0 |X +1 |Y +0 |A +1 |A +0 |X +2 |Y +0 |B +2 |B +0 |X +3 |Y +0 |C +3 |C +ij(USER1)> drop trigger tgood; +0 rows inserted/updated/deleted +ij(USER1)> drop table x; +0 rows inserted/updated/deleted +ij(USER1)> drop table y; 0 rows inserted/updated/deleted ij(USER1)> Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?view=diff&rev=482358&r1=482357&r2=482358 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql Mon Dec 4 13:07:25 2006 @@ -515,17 +515,6 @@ insert into x values 1; select * from x order by 1; drop trigger tgood; - --- DERBY-1204 trigger causes StringIndexOutOfBoundsException --- which half closes connection and causes rest of test to --- fail. Enable this trigger test case to resolve 1204. --- create trigger tgood after insert on x --- referencing new as n --- for each row insert into x values (n.x), (999), (333); --- insert into x values 1; --- select * from x order by 1; --- drop trigger tgood; - drop table x; -- Derby-388: When a set of inserts/updates is performed on a table @@ -779,3 +768,62 @@ drop trigger tropt; drop table topt2; drop table topt1; + +-- DERBY-1204 +-- trigger causes StringIndexOutOfBoundsException +-- which half closes connection and causes rest of test to +-- fail. Enable this trigger test case to resolve 1204. +create table x (x int); +-- ok +create trigger tgood after insert on x +referencing new as n +for each row insert into x values (n.x), (999), (333); +insert into x values 1; +select * from x order by 1; +drop trigger tgood; +drop table x; + +create table x (i int); +create table y (i int); +-- ok +create trigger tgood after insert on x +for each statement insert into y values (666), (999), (333); +drop trigger tgood; +-- ok +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (n.i); +drop trigger tgood; +-- ok +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (333), (999), (333); +drop trigger tgood; +-- ok. This used to throw StringIndexOutOfBoundsException +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (n.i), (999), (333); +insert into x values (888); +select * from y; +drop trigger tgood; +delete from x; +delete from y; +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (n.i), (n.i+1), (n.i+2); +insert into x values (1), (4), (7); +select * from y; +drop trigger tgood; +drop table x; +drop table y; +create table x (i int, j varchar(10)); +create table y (i int, j varchar(10)); +create trigger tgood after insert on x +referencing new as n +for each row insert into y values (0, 'X'), (n.i, 'Y'), (0, n.j), (n.i,n.j); +insert into x values (1,'A'), (2,'B'), (3, 'C'); +select * from y; +drop trigger tgood; +drop table x; +drop table y; +