[jira] Commented: (DERBY-2183) Trigger recompilation problem when trigger action has its table not qualified with a schema

["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