You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/12/05 05:40:01 UTC
svn commit: r482484 - in /db/derby/code/branches/10.2/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bpendleton
Date: Mon Dec 4 20:39:58 2006
New Revision: 482484
URL: http://svn.apache.org/viewvc?view=rev&rev=482484
Log:
DERBY-1204: TRIGGER with multi-row INSERT action throws OutOfBounds exception
This patch was contributed by Yip Ng (yipng168@gmail.com)
This change contains derby1204-10.2-diff01.txt, which merges the changes
for DERBY-1204 from the trunk to the 10.2 branch.
Modified:
db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?view=diff&rev=482484&r1=482483&r2=482484
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Mon Dec 4 20:39:58 2006
@@ -408,6 +408,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/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out?view=diff&rev=482484&r1=482483&r2=482484
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/triggerGeneral.out Mon Dec 4 20:39:58 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 mode db2sql 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,
@@ -1279,4 +1270,122 @@
C321
-----------
1
+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 table x (x int);
+0 rows inserted/updated/deleted
+ij> -- ok
+create trigger tgood after insert on x
+referencing new as n
+for each row mode db2sql insert into x values (n.x), (999), (333);
+0 rows inserted/updated/deleted
+ij> insert into x values 1;
+ERROR 54038: Maximum depth of nested triggers was exceeded.
+ij> select * from x order by 1;
+X
+-----------
+ij> drop trigger tgood;
+0 rows inserted/updated/deleted
+ij> drop table x;
+0 rows inserted/updated/deleted
+ij> create table x (i int);
+0 rows inserted/updated/deleted
+ij> create table y (i int);
+0 rows inserted/updated/deleted
+ij> -- ok
+create trigger tgood after insert on x
+for each statement mode db2sql insert into y values (666), (999), (333);
+0 rows inserted/updated/deleted
+ij> drop trigger tgood;
+0 rows inserted/updated/deleted
+ij> -- ok
+create trigger tgood after insert on x
+referencing new as n
+for each row mode db2sql insert into y values (n.i);
+0 rows inserted/updated/deleted
+ij> drop trigger tgood;
+0 rows inserted/updated/deleted
+ij> -- ok
+create trigger tgood after insert on x
+referencing new as n
+for each row mode db2sql insert into y values (333), (999), (333);
+0 rows inserted/updated/deleted
+ij> drop trigger tgood;
+0 rows inserted/updated/deleted
+ij> -- ok. This used to throw StringIndexOutOfBoundsException
+create trigger tgood after insert on x
+referencing new as n
+for each row mode db2sql insert into y values (n.i), (999), (333);
+0 rows inserted/updated/deleted
+ij> insert into x values (888);
+1 row inserted/updated/deleted
+ij> select * from y;
+I
+-----------
+888
+999
+333
+ij> drop trigger tgood;
+0 rows inserted/updated/deleted
+ij> delete from x;
+1 row inserted/updated/deleted
+ij> delete from y;
+3 rows inserted/updated/deleted
+ij> create trigger tgood after insert on x
+referencing new as n
+for each row mode db2sql insert into y values (n.i), (n.i+1), (n.i+2);
+0 rows inserted/updated/deleted
+ij> insert into x values (1), (4), (7);
+3 rows inserted/updated/deleted
+ij> select * from y;
+I
+-----------
+1
+2
+3
+4
+5
+6
+7
+8
+9
+ij> drop trigger tgood;
+0 rows inserted/updated/deleted
+ij> drop table x;
+0 rows inserted/updated/deleted
+ij> drop table y;
+0 rows inserted/updated/deleted
+ij> create table x (i int, j varchar(10));
+0 rows inserted/updated/deleted
+ij> create table y (i int, j varchar(10));
+0 rows inserted/updated/deleted
+ij> create trigger tgood after insert on x
+referencing new as n
+for each row mode db2sql insert into y values (0, 'X'), (n.i, 'Y'), (0, n.j), (n.i,n.j);
+0 rows inserted/updated/deleted
+ij> insert into x values (1,'A'), (2,'B'), (3, 'C');
+3 rows inserted/updated/deleted
+ij> 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> drop trigger tgood;
+0 rows inserted/updated/deleted
+ij> drop table x;
+0 rows inserted/updated/deleted
+ij> drop table y;
+0 rows inserted/updated/deleted
ij>
Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql?view=diff&rev=482484&r1=482483&r2=482484
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/triggerGeneral.sql Mon Dec 4 20:39:58 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 mode db2sql 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
@@ -725,3 +714,62 @@
insert into t31TriggerTest values(1);
select * from t31TriggerTest;
select * from t32TriggerTest;
+
+-- 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 mode db2sql 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 mode db2sql 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 mode db2sql insert into y values (n.i);
+drop trigger tgood;
+-- ok
+create trigger tgood after insert on x
+referencing new as n
+for each row mode db2sql 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 mode db2sql 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 mode db2sql 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 mode db2sql 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;
+