[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Yip Ng (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12430913 ] 

Yip Ng commented on DERBY-1621:
---

Release note for this jira:

Release Note for DERBY-1621
---

PROBLEM
Trigger action statement is not recompile when there is a change that would 
affect it.


SYMPTOMS

(1)  Trigger action such as an INSERT statement does not get recompiled 
when the underlying
 table is affected by a CREATE or DROP INDEX statement.  e.g.: 

 create table t (i int);
 create table t2 (i int);
 create trigger tt after insert on t for each statement mode db2sql 
insert into t2 values 1;
 insert into t values 1;
 select * from t2;
 create unique index tu on t2(i);
 insert into t values 1;
 select * from t2;
 insert into t values 1;
 1 row inserted/updated/deleted
   
 
 The above example creates an unique index on table t2.  when the 
trigger is fired, it did not 
 raise an unique constraint error.

(2)  When the trigger action statement underlying view gets dropped, the 
trigger statement did not get 
   recompiled.  e.g.:

 create table t11 (c111 int not null primary key, c112 int);
 insert into t11 values(1,1);
 insert into t11 values(2,2);
 create view v21 as select * from user1.t11;
 create table t31 (c311 int);
 create table t32 (c321 int);
 create trigger tr31t31 after insert on t31 for each statement mode 
db2sql insert into t32 values (select c111 from user1.v21 where c112=1);
 insert into t31 values(1);
 select * from t31;
 select * from t32;
 drop view v21;
 insert into t31 values(1);

 In the above example, a view which the trigger action references is 
dropped; however, the last SQL
 INSERT statement did not throw an error.

(3)  Conglomerate does not exist occurs in a specific case after dropping a 
table referenced by a trigger.
  The trigger action is not being recompiled and raises SQLSTATE XSAI2 
even though the table being 
  dropped was recreated again.  e.g.:

 create table t1 (id int, name varchar(20));
 create table t2 (id int);
 create trigger test_trigger after insert on t2 for each row mode 
db2sql insert into t1 values(100, 'hundred');
 insert into t2 values(1);
 insert into t2 values(1);
 select * from t1;
 drop table t1;
 insert into t2 values(1);
 create table t1 (id int, name varchar(20));
 insert into t2 values(1); 

 In the above example, a table which the trigger action references is 
dropped.  The last INSERT  
 statement should execute successfully but it raises SQLSTATE XSAI2: 
The conglomerate (896) 
 requested does not exist.  


CAUSE
   Derby did not perform invalidation of the trigger action when object(s) that 
the trigger
   references are modified or dropped; hence, resulting in the stated problem 
above.  The 
   affected versions are Derby 10.0 and 10.1.

SOLUTION
A fix to resolve the above Derby symptoms is available in 10.2.


WORKAROUND
   None.


> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.1.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.1.0
>
> Attachments: derby1621trunkdiff01.txt, derby1621trunkdiff02.txt, 
> derby1621trunkdiff03.txt, derby1621trunkdiff04.txt, derby1621trunkstat01.txt, 
> derby1621trunkstat02.txt, derby1621trunkstat03.txt, derby1621trunkstat04.txt
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> in

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Yip Ng (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12430612 ] 

Yip Ng commented on DERBY-1621:
---

Mamta, yes, I included the testcases for all the related duplicate jiras into 
this jira.
Thanks for porting this to 10.2, Rick.

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.1.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.1.0
>
> Attachments: derby1621trunkdiff01.txt, derby1621trunkdiff02.txt, 
> derby1621trunkdiff03.txt, derby1621trunkdiff04.txt, derby1621trunkstat01.txt, 
> derby1621trunkstat02.txt, derby1621trunkstat03.txt, derby1621trunkstat04.txt
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Rick Hillegas (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12430609 ] 

Rick Hillegas commented on DERBY-1621:
--

Ported DERBY-1621 (434046) to 10.2 branch at subversion revision 436921.

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.1.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.1.0
>
> Attachments: derby1621trunkdiff01.txt, derby1621trunkdiff02.txt, 
> derby1621trunkdiff03.txt, derby1621trunkdiff04.txt, derby1621trunkstat01.txt, 
> derby1621trunkstat02.txt, derby1621trunkstat03.txt, derby1621trunkstat04.txt
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Mamta A. Satoor (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12430189 ] 

Mamta A. Satoor commented on DERBY-1621:


Yip, thanks for working on this ciritical issue. 

DERBY-1613 is a duplicate of this bug. I just wondered if the test changes for 
this bug included the test case mentioned in DERBY-1613. 

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.1.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.1.0
>
> Attachments: derby1621trunkdiff01.txt, derby1621trunkdiff02.txt, 
> derby1621trunkdiff03.txt, derby1621trunkdiff04.txt, derby1621trunkstat01.txt, 
> derby1621trunkstat02.txt, derby1621trunkstat03.txt, derby1621trunkstat04.txt
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Rick Hillegas (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12430008 ] 

Rick Hillegas commented on DERBY-1621:
--

Thanks, Yip, this looks good. I have committed derby1621trunkdiff04.txt at 
subversion revision 434046.

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.1.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.1.0
>
> Attachments: derby1621trunkdiff01.txt, derby1621trunkdiff02.txt, 
> derby1621trunkdiff03.txt, derby1621trunkdiff04.txt, derby1621trunkstat01.txt, 
> derby1621trunkstat02.txt, derby1621trunkstat03.txt, derby1621trunkstat04.txt
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Yip Ng (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12427363 ] 

Yip Ng commented on DERBY-1621:
---

Another problem with SPS recompilation is that it is using 2 different 
transactions - one from the user for stored dependencies (SYSDEPENDS) updates 
and the other one is using an internal nested transaction to update the 
SYSSTATEMENTS system table.  If the user transaction rollback due to a 
constraint violation error at execution time, Derby will get an inconsistent 
view of the catalog.  In the above example, it will fail to invalidate the SPS 
when a drop index statement is issued since the conglomerate tu (index) is not 
registered as a provider for the SPS.  

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.0.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.0.0
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Yip Ng (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12426162 ] 

Yip Ng commented on DERBY-1621:
---

Running some initial tests on this patch as well, will post it up for review 
soon.  While going through the SPSDescriptor implementation, I found something 
perplexing and need some clarifications.  I'll address 
them on a follow up comment.

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.0.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.0.0
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Yip Ng (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12425587 ] 

Yip Ng commented on DERBY-1621:
---

Here is an analysis of the problem and the cause:

At create trigger time, the stored prepared statement 'insert into t2 values 1' 
will get compiled.  All the dependencies of this stored prep stmt will be 
cleared out and will be copied to the SPSDescriptor
which will be serialized to SYSDEPENDS as a stored dependency (provider is 
table descriptor t2)  

When inserting the value 1 to table t1, the table descriptor t1 will have all 
the relevent trigger(s) associated with it at bind time.  At execution time, 
the after trigger action is fired.  The StatementTriggerExecutor will request 
the trigger descriptor tt for its action routine via the SPSDescriptor.  Since 
it is not in the sps cache, it will load a 
copy of SPSDescriptor from SYSSTATEMENTS system table.  Since the valid field 
is still true, there is no need to recompile the stored prep statement.  The 
trigger action gets executed.  Also note that the trigger descriptor tt 
associated with the 'insert into t1 values 1' statement has saved the reference 
for the SPSDescriptor.

At execution time of the create unique index statement, it will invalidated all 
the dependents of the table descriptor t2.  The stored dependency SPSDescriptor 
will load from SYSDEPENDS, so Derby is invalidating this "copy" and also 
successfully updates the SYSSTATEMENTS entry to make this trigger action 's 
valid column to false.

Now the problem arises when we execute "insert into t1 values 1" again since 
its trigger descriptor tt still references with its copy of the SPSDescriptor 
which is still valid, it won't recompile the stored prep stmt causing the 
symptoms described above.

Also note that with the drop index tu statement, at execution time, it 
invalidates all its dependents but the SPSDescriptor is not one of its 
dependents.
 

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.0.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.0.0
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

Re: [jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Yip Ng]

Thanks for the info, Dan.   I briefly looked at the reported issue, I think I know what the problem is.  When Derby binds the trigger's action node (insert stmt in this case) in CreateTriggerNode, the action node bind phase creates a dependency from the compilation context but however, the context's current dependent is not the insert statement itself but of the trigger statement.  Hence, that is probably the reason why the insert statement didn't get invalidated when create/drop index is executed.
On 8/1/06, Daniel John Debrunner (JIRA)  wrote:
[ http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12424968 ]Daniel John Debrunner commented on DERBY-1621:
--Thanks for looking at this Yip - note that Derby has an existing dependency system already, thus it should be possible to fix this using the existing scheme rather than inventing anything new. 
E.g. in my example script the select * from t2 does receive to invalidations for the create and drop indexes which allow it to be recompiled to go against the base table or index.> Trigger action statement is not recompile when there is a change that would affect it.
> -->> Key: DERBY-1621> URL: 
http://issues.apache.org/jira/browse/DERBY-1621> Project: Derby>  Issue Type: Bug>  Components: SQL>Affects Versions: 10.2.0.0
>Reporter: Daniel John Debrunner> Assigned To: Yip Ng>Priority: Critical> Fix For: 10.2.0.0>>> A trigger action statement, such as an INSERT statement is not recompiled when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into by the trigger's action statement. When the tirgger fires it does not raise any error (should raise a unique constraint violated error) and does not insert the value into the index. A select from t2 does not show the additional rows in t2 as it is performing an index scan, once the index is dropped the rows appear to the select.
> ij version 10.2> ij> connect 'jdbc:derby:cs;create=true';> ij> create table t (i int);> 0 rows inserted/updated/deleted> ij> create table t2 (i int);> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql> insert into t2 values 1;> 0 rows inserted/updated/deleted> ij> insert into t values 1;> 1 row inserted/updated/deleted
> ij> select * from t2;> I> ---> 1> 1 row selected> ij> create unique index tu on t2(i);> 0 rows inserted/updated/deleted> ij> insert into t values 1;
> 1 row inserted/updated/deleted> ij> select * from t2;> I> ---> 1> 1 row selected> ij> insert into t values 1;> 1 row inserted/updated/deleted> ij> select * from t2;
> I> ---> 1> 1 row selected> ij> drop index tu;> 0 rows inserted/updated/deleted> ij> select * from t2;> I> ---> 1> 1> 1
> 3 rows selected--This message is automatically generated by JIRA.-If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa-For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Daniel John Debrunner (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12424968 ] 

Daniel John Debrunner commented on DERBY-1621:
--

Thanks for looking at this Yip - note that Derby has an existing dependency 
system already, thus it should be possible to fix this using the existing 
scheme rather than inventing anything new. E.g. in my example script the select 
* from t2 does receive to invalidations for the create and drop indexes which 
allow it to be recompiled to go against the base table or index.

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.0.0
>Reporter: Daniel John Debrunner
> Assigned To: Yip Ng
>Priority: Critical
> Fix For: 10.2.0.0
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

[jira] Commented: (DERBY-1621) Trigger action statement is not recompile when there is a change that would affect it.

[Mamta A. Satoor (JIRA)]

[ 
http://issues.apache.org/jira/browse/DERBY-1621?page=comments#action_12424931 ] 

Mamta A. Satoor commented on DERBY-1621:


While on the topic of recompilation, I think there might be an issue with views 
as well. I don't see any code in ViewDescriptor invalidation related methods 
where the view gets recompiled. May be it is being done somewhere else but I 
wanted to raise the possible issue. If there indeed is a problem, we can open 
another Jira entry for it if required.

> Trigger action statement is not recompile when there is a change that would 
> affect it.
> --
>
> Key: DERBY-1621
> URL: http://issues.apache.org/jira/browse/DERBY-1621
> Project: Derby
>  Issue Type: Bug
>  Components: SQL
>Affects Versions: 10.2.0.0
>Reporter: Daniel John Debrunner
>Priority: Critical
> Fix For: 10.2.0.0
>
>
> A trigger action statement, such as an INSERT statement is not recompiled 
> when there is some DDL change on the underlying table, such as a CREATE INDEX.
> In the example below a unique index is added to the table (t2) inserted into 
> by the trigger's action statement. When the tirgger fires it does not raise 
> any error (should raise a unique constraint violated error) and does not 
> insert the value into the index. A select from t2 does not show the 
> additional rows in t2 as it is performing an index scan, once the index is 
> dropped the rows appear to the select.
> ij version 10.2
> ij> connect 'jdbc:derby:cs;create=true';
> ij> create table t (i int);
> 0 rows inserted/updated/deleted
> ij> create table t2 (i int);
> 0 rows inserted/updated/deleted
> ij> create trigger tt after insert on t for each statement mode db2sql
> insert into t2 values 1;
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> create unique index tu on t2(i);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1 row selected
> ij> drop index tu;
> 0 rows inserted/updated/deleted
> ij> select * from t2;
> I
> ---
> 1
> 1
> 1
> 3 rows selected

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira