Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-85

NPE when creating a trigger on a table and default schema doesn't exist.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      Description

      BACKGROUND:

      When connecting to a Derby db with a user id and password, the default schema is USER. For example, if I connect with:

      ij> connect 'jdbc:derby:myDB;user=someUser;password=somePwd';

      then the default schema is "SOMEUSER".

      PROBLEM:

      It turns out that if a table t1 exists in a non-default schema and the default schema (in this case, "SOMEUSER") doesn't exist yet (because no objects have been created in that schema), then attempts to create a trigger on t1 using its qualified name will lead to a null pointer exception in the Derby engine.

      REPRO:

      In ij:

      – Create database with default schema "SOMEUSER".
      ij> connect 'jdbc:derby:myDB;create=true;user=someUser;password=somePwd';

      – Create table t1 in a non-default schema; in this case, call it "ITKO".
      ij> create table itko.t1 (i int);
      0 rows inserted/updated/deleted

      – Now schema ITKO exists, and T1 exists in schema ITKO, but default schema SOMEUSER does NOT exist, because we haven't created any objects in that schema yet.

      – So now we try to create a trigger in the ITKO (i.e. the non-default) schema...
      ij> create trigger trig1 after update on itko.t1 for each row mode db2sql select * from sys.systables;
      ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

      A look at the derby.log file shows the stack trace given below. In a word, it looks like the "compilation schema" field of SYS.SYSTRIGGERS isn't getting set, and so it ends up being null. That causes the NPE in subsequent processing...

      java.lang.NullPointerException
      at org.apache.derby.impl.sql.catalog.SYSSTATEMENTSRowFactory.makeSYSSTATEMENTSrow(SYSSTATEMENTSRowFactory.java:200)
      at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addSPSDescriptor(DataDictionaryImpl.java:2890)
      at org.apache.derby.impl.sql.execute.CreateTriggerConstantAction.createSPS(CreateTriggerConstantAction.java:354)
      at org.apache.derby.impl.sql.execute.CreateTriggerConstantAction.executeConstantAction(CreateTriggerConstantAction.java:258)
      at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:56)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:366)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1100)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:509)
      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:467)
      at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:299)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
      at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
      at org.apache.derby.impl.tools.ij.Main.go(Main.java:210)
      at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:176)
      at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:56)
      at org.apache.derby.tools.ij.main(ij.java:60)

        Attachments

        1. derbyall_report.merge-to-10.1.txt
          19 kB
          Dyre Tjeldvoll
        2. derby-85_2.diff
          3 kB
          Dyre Tjeldvoll
        3. derby-85_2.stat
          0.3 kB
          Dyre Tjeldvoll
        4. derbyall_report_2.txt
          1.94 MB
          Dyre Tjeldvoll
        5. derbyall_report.txt
          26 kB
          Dyre Tjeldvoll
        6. derby-85.diff
          4 kB
          Dyre Tjeldvoll
        7. derby-85.stat
          0.5 kB
          Dyre Tjeldvoll

          Activity

            People

            • Assignee:
              dyret Dyre Tjeldvoll
              Reporter:
              army A B
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: