[jira] Commented: (DERBY-630) create trigger fails with null pointer exception

["Yip Ng (JIRA)" <de...@db.apache.org>]

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

At execution time, it looks like CREATE TRIGGER expects a compilation schema UUID to be available.  Since the above example did not use the schema APP (which already exists after db creation time), the schema "someUser" is not created physically yet, so there won't be any UUID available.  When trying to retrieve its
schema descriptor from the data dictionary, getSchemaDescriptor will return null, so trying to access the
UUID will ultimately result in NPE.

CREATE VIEW also suffers from this problem wth regards to compilation schema.  From looking at the code, it seems it is possible for the non-nullable column COMPILATIONSCHEMAID in SYS.SYSVIEWS to have SQL NULL value.  So to confirm this, I tried the following script on ij:

> connect 'jdbc:derby:wombat;create=true;user=user2;password=pwd' as user2;
> WARNING 01J01: Database 'wombat' not created, connection made to existing database instead.
> ij(USER2)> create table ippo.t1 (i int);
> 0 rows inserted/updated/deleted
> ij(USER2)> create table ippo.t2 (i int);
> 0 rows inserted/updated/deleted
> ij(USER2)> create view ippo.v1(i) as values 1;
> 0 rows inserted/updated/deleted
> ij(USER2)> -- should return 0 row, as SYS.SYSVIEWS.COMPILATIONSCHEMAID column is not nullable
> select t.tablename, v.compilationschemaid
>    from sys.systables t, sys.sysviews v
>    where t.tableid = v.tableid and t.tablename='V1' and v.compilationschemaid is null;
> TABLENAME    |COMPILATIONSCHEMAID
> ------------------------------------------------------------------------------
> V1                       |NULL

ij> describe sys.sysviews;
COLUMN_NAME                 |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
TABLEID                                |CHAR           |NULL|NULL  |36             |NULL                 |72                      |NO
VIEWDEFINITION                |LONG VAR&|NULL|NULL  |32700      |NULL                 |NULL                |NO
CHECKOPTION                   |CHAR           |NULL|NULL  |1               |NULL                 |2                         |NO
COMPILATIONSCHEMAID |CHAR           |NULL|NULL  |36             |NULL                 |72                      |NO
4 rows selected

So the above script shows that CREATE VIEW also have a problem.




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