Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <33002450.1159896804065.JavaMail.root@brutus>
Date: Tue, 3 Oct 2006 10:33:24 -0700 (PDT)
From: "Yip Ng (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-630) create trigger fails with null
 pointer exception
In-Reply-To: <1917665868.1129497645522.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-630?page=comments#action_12439569 ] 
            
Yip Ng commented on DERBY-630:
------------------------------

Hi Bryan.  An implicit schema is not the same as a null compilation schema that I described.  An implicit schema or current schema, as we see in DERBY-85:

<snip>
ij> connect 'jdbc:derby:firstDB;create=true;user=someUser;password=somePwd';
ij> create table itko.t1 (i int);
0 rows inserted/updated/deleted
ij> create trigger trig1 after update on itko.t1 for each row mode db2sql select * from sys.systables; 
</snip>

will have a compilation schema 'someUser' (the trigger name is not qualified with a schema name, so it will use the current schema, 'someUser' which will be implicitly created at execution time).

DERBY-630 poses a different problem where the current schema is not explicitly or implicitly created.  

<snip>
ij> connect 'jdbc:derby:myDB;create=true;user=someUser;password=somePwd';
ij> create table itko.t1 (i int);
0 rows inserted/updated/deleted
ij> create trigger itko.trig1 after update on itko.t1 for each row mode db2sql select * from sys.systables; 
</snip>

So this is one edge case where the compilation schema can be null since the current schema is not physically created yet, so the spsCompSchemaId is null.  At the CREATE TRIGGER execution time, it
attempts to locate the CURRENT SCHEMA via the data dictionary but this will ultimately return null.  
Hence, when attempting to retrieve its UUID, NPE occurs. 

A compilation schema can also be set to null if the statement text does not have dependencies on the CURRENT SCHEMA but this logic does not seem to be implemented yet in Derby.  There are some
comments in the code but the actual implementation logic is incomplete.  The latter case can benefit statement cache plan sharing across connections in different schema.  The compilation schema is
used as an identity part for the statement.


> create trigger fails with null pointer exception
> ------------------------------------------------
>
>                 Key: DERBY-630
>                 URL: http://issues.apache.org/jira/browse/DERBY-630
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.1.0
>         Environment: windows 2000, sun jdk 1.5.0
>            Reporter: mardacay
>         Assigned To: Yip Ng
>         Attachments: derby630-trunk-diff01.txt, derby630-trunk-stat01.txt
>
>
> When i create a brand new database, and execute the following statements all in one transaction or each of them in their own transaction, then it fails at trigger creation with null pointer exception. if i exclude the schema names from statement, then it runs fine. (If S1 is ommited from every statement then it runs fine). Once the version without the schema names run fine, i can run the version that has schema names, fine also. 
> create schema S1;
> create table
>   S1.PRODUCT(
>     PRODUCT_ID VARCHAR(255) unique not null,
>     VERSION BIGINT
>   );
>   
> create table
>   S1.CATEGORY(
>     CAT_ID VARCHAR(255),
>     NAME varchar(255) not null,
>     VERSION BIGINT
>   );
> create table
>   S1.PROD_IN_CAT(
>     CAT_ID VARCHAR(255) not null,
>     PRODUCT_ID VARCHAR(255) not null,
>     VERSION BIGINT
>   );
>   
> create trigger S1.product_v 
> after update of version on S1.product
> referencing new as n
> for each row
> mode db2sql
> 	update S1.prod_in_cat set version = n.version where S1.prod_in_cat.product_id=n.product_id;
> java.lang.NullPointerException
> 	at org.apache.derby.impl.sql.catalog.SYSSTATEMENTSRowFactory.makeSYSSTATEMENTSrow(Unknown Source)
> 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addSPSDescriptor(Unknown Source)
> 	at org.apache.derby.impl.sql.execute.CreateTriggerConstantAction.createSPS(Unknown Source)
> 	at org.apache.derby.impl.sql.execute.CreateTriggerConstantAction.executeConstantAction(Unknown Source)Stopping progress indicator for: Executing SQL
> 	at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

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