NPE when firing a trigger

• Key: DERBY-3718
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Mamta A. Satoor
• Reporter: Rick Hillegas
• Component/s: SQL
• Affects Version/s: 10.4.1.3, 10.5.1.1
• Fix Version/s: 10.4.2.0, 10.5.1.1

Description

NullPointerException raised when firing a trigger. Originally reported by Thiyagu P on the Derby user list: http://www.nabble.com/Trigger-function-broken-in-10.4.1.3--td17760208.html#a17760208 Thiyagu reports that the same script works fine in 10.3.3.0. Here's the script:

CREATE TABLE TRADE(
      ID INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1000),
      BUYID INT NOT NULL,
      QTY FLOAT(2) NOT NULL
   );
  
   CREATE TABLE TOTAL(BUYID INT NOT NULL, TOTALQTY FLOAT(2) NOT NULL);

   CREATE TRIGGER TRADE_INSERT
     AFTER INSERT ON TRADE
     REFERENCING NEW AS NEWROW
     FOR EACH ROW MODE DB2SQL
     UPDATE TOTAL SET TOTALQTY = NEWROW.QTY WHERE BUYID = NEWROW.BUYID;

   INSERT INTO TOTAL VALUES (1, 0);

   INSERT INTO TRADE VALUES(1, 1, 10);

Here's the stack trace:

java.lang.NullPointerException
at org.apache.derby.iapi.types.DataTypeDescriptor.getNull(DataTypeDescriptor.java:1008)
at org.apache.derby.iapi.types.DataTypeDescriptor.normalize(DataTypeDescriptor.java:645)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(NormalizeResultSet.java:329)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:189)
at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127)
at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:424)
at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:246)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:384)
at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:159)
at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:111)
at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:269)
at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1150)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:487)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:384)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:508)
at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:350)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:248)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
at org.apache.derby.tools.ij.main(ij.java:59)

Comments

[Mamta A. Satoor]

I am looking at this jira entry to see if something rings a bell in the code which might cause this npe.

[Mamta A. Satoor]

I think this got broken in 10.4 codeline after the checkin r619995 -
DERBY-2917 Changed the runtime type DataTypeDescriptor to no longer be a TypeDescriptor (catalog type) but instead only have a catalog type (TypeDescriptor). Added code to handle upgrade issue of DataTypeDescriptor's being written as catalog types in RoutineAliasInfo.

iapi.types.DataTypeDescriptor.writeExternal used to write the TypeId and the TypeDescriptor in 10.3 codeline. So, the code for writeExternal for DataTypeDescriptor was as follows in 10.3
public void writeExternal( ObjectOutput out )
throws IOException
{
out.writeObject( typeId );
out.writeObject( typeDescriptor );
}
This code was changed in 10.4 codeline to look as follows
public void writeExternal( ObjectOutput out )
throws IOException
{
out.writeObject(typeDescriptor);
out.writeInt(getCollationDerivation());
}
Starting 10.4, we now rely on loading the TypeId on the fly rather than storing it on the disk as shown below in the readExternal code
public void readExternal( ObjectInput in )
throws IOException, ClassNotFoundException
{
typeDescriptor = (TypeDescriptorImpl) in.readObject();
        
typeId = TypeId.getBuiltInTypeId(this.getJDBCTypeId());
        
collationDerivation = in.readInt();
}

The problem is for some reason, we are not able to read the TypeId for the REF datatype(ie in the code above, TypeId.getBuiltInTypeId(this.getJDBCTypeId()); returns null when dealing with REF datatype). I am going to see if we just need a simple case clause for REF in the switch statement in TypeId.getBuiltInTypeId or something else to fix the problem. I will also spend some time figuring out how we are able to get
a good TypeId object for REF object in other places when it appears that it is definitely not available to iapi.types.DataTypeDescriptor.readExternal

Dan, if this rings a bell to you, will you let me know if my analysis is correct and why we are not able to load TypeId for REF datatype.

[Mamta A. Satoor]

I added a case clause for Types.REF as shown below in TypeId.getBuiltInTypeId(int)
        case Types.REF:
         return REF_ID;

I thought adding this code will get us the correct TypeId for REF datatypes when DataTypeDescriptor.readExternal does following
        typeId = TypeId.getBuiltInTypeId(this.getJDBCTypeId());

But even with the addition of case statement for Types.REF, I get null pointer exception. The reason is that for some reason, for REF datatypes, Derby does not use Types.REF. Instead it uses Types.OTHER (see BaseTypeIdImpl.setTypeIdSpecificInstanceVariables():line 300
          case StoredFormatIds.REF_TYPE_ID_IMPL:
                SQLTypeName = TypeId.REF_NAME;
                JDBCTypeId = Types.OTHER;
                break;

My question is why did we decide to use Types.OTHER rather than Types.REF for REF datatype? Also, I do not think it would be correct for me to check for Types.OTHER in TypeId.getBuiltInTypeId(int) and then always return TypeId for REF. Would appreciate any feedback on this.

[Daniel John Debrunner]

Derby does not support the REF datatype, so using Types.REF would be incorrect for an internal type.

[Mamta A. Satoor]

Since we are dealing with the internal type which will be assigned a Types.OTHER type then we can't rely on Types.xxx to get us the correct data type. I am attaching a patch which relies on using the name of the data type rather than it's Type.xxx to construct the correct TypeId. I am accomplising that by changing DataTypeDescriptor.readExternal to use this.getTypeName rather than this.getJDBCTypeId to construct the TypeId. In addition to this change, I had to change DataTypeDescriptor.getTypeName() to use TypeDescriptor to get the type name rather than TypeId. This is because TypeId is not available when DataTypeDescriptor.readExternal calls DataTypeDescriptor.getTypeName. This will match how we already implement DataTypeDescriptor.getJDBCTypeId(). With my patch, I have run Junit and old harness suite with no new regressions. Please let me know if anyone has any comments on the patch. I will plan on committing it early next week if there are no comments. I will also work on writing a test case for this Jira entry.

[Mamta A. Satoor]

Committed fix for this jira entry with revision 670778. I will work on migrating this to 10.4 codeline.

[Mamta A. Satoor]

Backported the changes to 10.4 codeline with revision 671619