You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Yip Ng (JIRA)" <ji...@apache.org> on 2006/12/15 20:02:24 UTC
[jira] Created: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Trigger recompilation problem when trigger action has its table not qualified with a schema
-------------------------------------------------------------------------------------------
Key: DERBY-2183
URL: http://issues.apache.org/jira/browse/DERBY-2183
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.1.6, 10.1.3.1, 10.3.0.0
Environment: Any
Reporter: Yip Ng
Trigger recompilation problem when trigger action has its table not qualified with a schema.
SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
ij version 10.3
ij> connect 'jdbc:derby:wombat;create=true';
ij> create table app.t1 (i int, j int);
0 rows inserted/updated/deleted
ij> insert into app.t1 values (1,10);
1 row inserted/updated/deleted
-- notice trigger action's update statement did not qualify table t1 with a schema name
ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
0 rows inserted/updated/deleted
ij> update app.t1 set i=i+1;
1 row inserted/updated/deleted
ij> select * from app.t1;
I |J
-----------------------
2 |1
1 row selected
-- this action invalidates the SPS and mark for recompilation
ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
0 rows inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat' user 'user1';
-- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
ij> update app.t1 set i=i+1;
ERROR 42Y07: Schema 'USER1' does not exist
ij>
--
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-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12462379 ]
Yip Ng commented on DERBY-2183:
-------------------------------
Hi Mike, attaching derby2183-10.1-diff01.txt for 10.1.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.2.3.0, 10.3.0.0
>
> Attachments: derby2183-10.1-diff01.txt, derby2183-10.1-stat01.txt, derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yip Ng updated DERBY-2183:
--------------------------
Derby Info: (was: [Patch Available])
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yip Ng updated DERBY-2183:
--------------------------
Attachment: derby2183-10.1-diff01.txt
derby2183-10.1-stat01.txt
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.2.3.0, 10.3.0.0
>
> Attachments: derby2183-10.1-diff01.txt, derby2183-10.1-stat01.txt, derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12462090 ]
Bryan Pendleton commented on DERBY-2183:
----------------------------------------
Committed derby2183-trunk-diff02.txt to subversion as revision 492353.
Should this fix be merged to 10.2?
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Pendleton resolved DERBY-2183.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.3.0.0
10.2.3.0
Derby Info: (was: [Patch Available])
Applied derby2183-10.2-diff01.txt to the 10.2 branch and
committed it to subversion as revision 492734.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.2.3.0, 10.3.0.0
>
> Attachments: derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2183:
----------------------------------
yip, I need this to get fixed in 10.1 - will a simple merge do it. Or do you think there is more work to get it to 10.1? I can
do the commit/test running - just let me know.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.2.3.0, 10.3.0.0
>
> Attachments: derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-2183?page=all ]
Yip Ng updated DERBY-2183:
--------------------------
Derby Info: [Patch Available]
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: http://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.3.0.0, 10.1.3.1
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
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-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12461876 ]
Yip Ng commented on DERBY-2183:
-------------------------------
Thanks Bryan for reviewing the patch! Let me attempt to answer your questions/comments. =)
1) Yes, I agree that the Javadoc needs to be updated with regard to the current changes.
I'll resubmit a follow up patch to address the code comments. In the original code,
it appears that the SchemaDescriptor input parameter is never set even though the
javadoc states so.
2) There are 3 possible values(of interest) that can get passed into pushCompilerContext(SchemaDescriptor ):
a) A null SchemaDescriptor which indicates to the system to use the CURRENT SCHEMA as the compilation
schema. (This is the same behavior as the original code.)
b) A SchemaDescriptor with its UUID == null, this means that either the schema has not been physically
created yet or that the LCC's getDefaultSchema() is not yet up-to-date with its actual UUID. Use the
CURRENT SCHEMA as the compilation schema. (This is the same behavior as the original code.)
c) A schemaDescriptor with its UUID != null, this means that the schema has been physiclally created.
Use this schema as the compilation schema (e.g.: for trigger or view recompilation cases).
On a side note, if the check for UUID != null is removed, it will reveal another bug in the view binding or execution code logic if the compilation schema is set with case b) above. e.g.:
connect 'jdbc:derby:wombat;create=true' user 'user1';
create table ta (i int);
create view sva as select * from ta;
connect 'jdbc:derby:wombat' user 'user2';
create schema user2;
create view svc (i) as select * from user1.sva;
ERROR 42X05: Table/View 'TA' does not exist.
The cause for the above error is due to the view SVA's compilation schema UUID being null
which is not correct, so setting compilation schema with case b) early has some side effect
on the schema binding logic in later stage. It is probably due to a stale default schema in LCC.
I think I'll file a separate jira for this issue. With the current fix, this problem will
not manifest itself.
I'll add the above comments to the pushCompilerContext() javadoc also.
3) Actually, the view recompilation does not use pushCompilerContext(SchemaDescriptor) directly but
instead sets the actual compilation schema to the compiler context directly with a call
cc.setCompilerSchema(sd). This may explain why the problem does not occur in view recompilation
cases. The comment is there to remind developers in the future to pay attention when using LCC's
pushCompilerContext(SchemaDescriptor).
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yip Ng updated DERBY-2183:
--------------------------
Attachment: derby2183-trunk-diff02.txt
derby2183-trunk-stat02.txt
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Closed: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll closed DERBY-2183.
---------------------------------
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.1.4
> Environment: Any
> Reporter: Yip Ng
> Assignee: Yip Ng
> Fix For: 10.1.4.0, 10.2.3.0, 10.3.1.4
>
> Attachments: derby2183-10.1-diff01.txt, derby2183-10.1-stat01.txt, derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12461880 ]
Yip Ng commented on DERBY-2183:
-------------------------------
Attaching follow-up derby2183-trunk-diff02.txt patch to address code comments. Please review.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12461631 ]
Bryan Pendleton commented on DERBY-2183:
----------------------------------------
Hi Yip, thanks for the patch and for the notes. It's a little startling that the
change turned out to be in such a fundamental and oft-called part of
the code, isn't it?
I think your change is very good, I just had a few comments:
1) Your description of the change makes it sound as though we
were using the default schema when we shouldn't be, but the actual
code change appears to be to instruct the system to use "sd", which
is described in the javadoc for this method as "the default schema".
I'm just reading the comments in the code, not doing any deep analysis.
I guess what I'm suggesting here is it seems like the javadoc for
pushCompilerContext(SchemaDescriptor sd) needs to be changed,
because it seems like this method (as opposed to the no-arg version
of pushCompilerContext()) is supposed to be used precisely when
the passed "sd" is *not* the default schema, but is in fact some special
non-default schema, such as the saved SPS schema in your case.
2) Related to the above, I think it would be nice to see some javadoc
for the pushCompilerContext(SchemaDescriptor sd) overload
explaining what the meaning of a NULL "sd" argument is. In particular,
it's not clear to me from reading the code why we need to test sd != null
and only call setCompilationSchema in that case. Why would it not
be correct to *always* call cc.setCompilationSchema(sd)?
3) Lastly, in your code comments in the patch you mention that this
change is significant not just for trigger SPS recompilation but also
for view recompilation, but I do not see any tests in the test section of
your patch which demonstrate how you change affects view recompilation.
Would it be possible to add a test which demonstrates this new behavior?
Thanks very much!
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: http://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
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] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yip Ng updated DERBY-2183:
--------------------------
Derby Info: [Patch Available]
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yip Ng updated DERBY-2183:
--------------------------
Attachment: derby2183-10.2-diff01.txt
derby2183-10.2-stat01.txt
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2183:
----------------------------------
Fix Version/s: 10.1.4.0
committed fix patch to 10.1 branch:
m101_ibm142:118>svn commit
Sending java\engine\org\apache\derby\impl\sql\conn\GenericLanguageConnect
ionContext.java
Sending java\testing\org\apache\derbyTesting\functionTests\master\trigger
General.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\tri
ggerGeneral.sql
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\tri
ggerGeneral_app.properties
Transmitting file data ....
Committed revision 493253.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Fix For: 10.1.4.0, 10.2.3.0, 10.3.0.0
>
> Attachments: derby2183-10.1-diff01.txt, derby2183-10.1-stat01.txt, derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12462136 ]
Yip Ng commented on DERBY-2183:
-------------------------------
Thanks Bryan. Submitting derby2183-10.2-diff01.txt for 10.2. Running derbyall now, will post result here when it completes.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-2183?page=all ]
Yip Ng updated DERBY-2183:
--------------------------
Attachment: derby2813-trunk-stat01.txt
derby2813-trunk-diff01.txt
Attaching initial patch derby2813-trunk-diff01.txt for review. derbyall and junit test suite passes successfully.
The problem was that the compilation schema for the SPS was never set during its recompilation, it was using the current schema to perform the recompilation which is not correct. It should have use the SPS's compilation schema to do the recompilation when we push the compiler context.
On a side note, I think I might have found/encountered another problem with trigger while I was fixing this bug. Specifically in the area of trigger execution context(TEC) in the LCC. For instance, when a trigger reaches its maximum recursion allowed, it appears that the TEC count is not being reset properly, causing the maximum recursion to be thrown again for a statement that merely triggers no more than two TECs. I'll file a separate jira for this issue.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: http://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.3.0.0, 10.1.3.1
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
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-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12462174 ]
Yip Ng commented on DERBY-2183:
-------------------------------
derbyall passes successfully for 10.2 with this patch.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-10.2-diff01.txt, derby2183-10.2-stat01.txt, derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12462020 ]
Bryan Pendleton commented on DERBY-2183:
----------------------------------------
The updated javadoc looks great; I think it is much clearer now and it
will be quite helpful to future readers of the code.
I intend to commit the "02" version of this patch; if anyone else is reviewing
this change and needs more time, please let us know.
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: https://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.1.6, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
> Attachments: derby2183-trunk-diff02.txt, derby2183-trunk-stat02.txt, derby2813-trunk-diff01.txt, derby2813-trunk-stat01.txt
>
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-2183) Trigger recompilation problem when
trigger action has its table not qualified with a schema
Posted by "Yip Ng (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-2183?page=all ]
Yip Ng reassigned DERBY-2183:
-----------------------------
Assignee: Yip Ng
> Trigger recompilation problem when trigger action has its table not qualified with a schema
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-2183
> URL: http://issues.apache.org/jira/browse/DERBY-2183
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.3.0.0, 10.1.3.1
> Environment: Any
> Reporter: Yip Ng
> Assigned To: Yip Ng
>
> Trigger recompilation problem when trigger action has its table not qualified with a schema.
> SPSs in SYS.SYSSTATEMENTS get invalidated for recompilation when sqlj.install_jar, sqlj.remove_jar, sqlj.replace_jar are called, or when a database upgrade is performed. The problem arises when the trigger action statement does not qualify the table with an explicit schema name. During recompilation it uses the default schema instead of using the original schema that was persisted in SYS.SYSSTATEMENTS causing an exception to occur. e.g.:
> C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.ij
> ij version 10.3
> ij> connect 'jdbc:derby:wombat;create=true';
> ij> create table app.t1 (i int, j int);
> 0 rows inserted/updated/deleted
> ij> insert into app.t1 values (1,10);
> 1 row inserted/updated/deleted
> -- notice trigger action's update statement did not qualify table t1 with a schema name
> ij> create trigger app.tr1 after update of i on app.t1 update t1 set j = 1;
> 0 rows inserted/updated/deleted
> ij> update app.t1 set i=i+1;
> 1 row inserted/updated/deleted
> ij> select * from app.t1;
> I |J
> -----------------------
> 2 |1
> 1 row selected
> -- this action invalidates the SPS and mark for recompilation
> ij> call sqlj.install_jar('c:/derby/procs/Procs.jar', 'APP.Procs', 0);
> 0 rows inserted/updated/deleted
> ij> disconnect;
> ij> connect 'jdbc:derby:wombat' user 'user1';
> -- recompilation occurs but uses 'USER1' as the schema to compile instead of 'APP', resulting in error
> ij> update app.t1 set i=i+1;
> ERROR 42Y07: Schema 'USER1' does not exist
> ij>
--
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