[
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
