db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
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 GMT
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;
+



Mime
View raw message