db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yip Ng (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-630) create trigger fails with null pointer exception
Date Mon, 02 Oct 2006 22:19:20 GMT
    [ http://issues.apache.org/jira/browse/DERBY-630?page=comments#action_12439330 ] 
            
Yip Ng commented on DERBY-630:
------------------------------

I think to properly address this issue, the current implementation for CREATE TRIGGER needs

to handle null compilation schema.  A compilation schema with null value indicates that the

trigger action statement text does not depend on the current schema.  This means:

1.  It is safe to compile this statement in any schema since there is no dependency with 
    the CURRENT SCHEMA.  i.e.:  All relevent fields are qualified with a specific schema.

2.  The statement cache mechanism can utilize this piece of information to enable 
    better statement plan sharing across different schemas; thus, avoiding unnecessary 
    statement compilation.  

 
So the proposal is:

a) Allow null compilation schema.  The system catalog first needs to change the 
   compiliation schema id column of SYS.SYSSTATEMENTS and SYS.SYSVIEWS system 
   table's nullability to true.  This would also require a soft upgrade for the 
   previous versions of Derby.  This step will resolve the NPE problem of this jira.

b) Handle null compilation schema in statement preparation logic.  This can be filed
   as a separate jira as improvement.  It may require some work on the SQL parser to 
   determine whether the compiled statement depends on the CURRENT SCHEMA or not, so 
   that the compilation schema can be properly set.


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

        

Mime
View raw message