Derby
  1. Derby
  2. DERBY-6006

NullPointerException in INSERT INTO ... SELECT FROM ... ORDER BY

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.2.1, 10.7.1.1, 10.8.3.0, 10.9.1.0
    • Fix Version/s: 10.8.3.3, 10.9.2.2, 10.10.1.1
    • Component/s: Store
    • Labels:
      None
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Seen in production

      Description

      A NullPointerException was reported by Harm-Jan Zwinderman on derby-user:
      http://mail-archives.apache.org/mod_mbox/db-derby-user/201211.mbox/%3C50B66527.5040906%40gmail.com%3E

      I've managed to reproduce it on 10.9.1.0 like this:

      ij version 10.9
      ij> connect 'jdbc:derby:memory:db;create=true';
      ij> create table t(x double);
      0 rows inserted/updated/deleted
      ij> insert into t values (0);
      1 row inserted/updated/deleted
      ij> prepare ps as 'insert into t select 1 from t order by x';
      ij> execute ps;
      1 row inserted/updated/deleted
      ij> execute ps;
      2 rows inserted/updated/deleted
      ij> execute ps;
      4 rows inserted/updated/deleted
      ij> execute ps;
      ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

      Full stack trace:

      java.lang.NullPointerException
      at org.apache.derby.impl.store.access.conglomerate.ConglomerateUtil.createFormatIds(Unknown Source)
      at org.apache.derby.impl.store.access.heap.Heap.create(Unknown Source)
      at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(Unknown Source)
      at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(Unknown Source)
      at org.apache.derby.impl.sql.execute.TemporaryRowHolderImpl.insert(Unknown Source)
      at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(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.EmbedPreparedStatement.executeStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(Unknown Source)
      at org.apache.derby.impl.tools.ij.ij.ExecuteStatement(Unknown Source)
      at org.apache.derby.impl.tools.ij.ij.ijStatement(Unknown Source)
      at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
      at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
      at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
      at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
      at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
      at org.apache.derby.tools.ij.main(Unknown Source)
      at org.apache.derby.iapi.tools.run.main(Unknown Source)

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Debug versions produce another error. And all executions of the INSERT ... ORDER BY statements fail with the debug version, not only the fourth execution.

          Stack trace with 10.9.1.0 debug:

          ERROR XSCH5: In a base table there was a mismatch between the requested column number 1 and the maximum number of columns 1.
          at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:295)
          at org.apache.derby.impl.store.access.heap.HeapController.doInsert(HeapController.java:235)
          at org.apache.derby.impl.store.access.heap.HeapController.insert(HeapController.java:575)
          at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(RowChangerImpl.java:457)
          at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1155)
          at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:508)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:443)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:324)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1242)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1715)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(EmbedPreparedStatement.java:1370)
          at org.apache.derby.impl.tools.ij.ij.ExecuteStatement(ij.java:2521)
          at org.apache.derby.impl.tools.ij.ij.ijStatement(ij.java:1158)
          at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:347)
          at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245)
          at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
          at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
          at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
          at org.apache.derby.tools.ij.main(ij.java:59)
          at org.apache.derby.iapi.tools.run.main(run.java:53)

          Show
          Knut Anders Hatlen added a comment - Debug versions produce another error. And all executions of the INSERT ... ORDER BY statements fail with the debug version, not only the fourth execution. Stack trace with 10.9.1.0 debug: ERROR XSCH5: In a base table there was a mismatch between the requested column number 1 and the maximum number of columns 1. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:295) at org.apache.derby.impl.store.access.heap.HeapController.doInsert(HeapController.java:235) at org.apache.derby.impl.store.access.heap.HeapController.insert(HeapController.java:575) at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(RowChangerImpl.java:457) at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1155) at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:508) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:443) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:324) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1242) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1715) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(EmbedPreparedStatement.java:1370) at org.apache.derby.impl.tools.ij.ij.ExecuteStatement(ij.java:2521) at org.apache.derby.impl.tools.ij.ij.ijStatement(ij.java:1158) at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:347) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:245) at org.apache.derby.impl.tools.ij.Main.go(Main.java:229) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184) at org.apache.derby.impl.tools.ij.Main.main(Main.java:75) at org.apache.derby.tools.ij.main(ij.java:59) at org.apache.derby.iapi.tools.run.main(run.java:53)
          Hide
          Knut Anders Hatlen added a comment -

          Before DERBY-4397 (Derby 10.6.1.0), ORDER BY wasn't allowed in INSERT statements, so the statement would fail to compile. The NullPointerException is reproducible on 10.6 and all subsequent branches.

          Show
          Knut Anders Hatlen added a comment - Before DERBY-4397 (Derby 10.6.1.0), ORDER BY wasn't allowed in INSERT statements, so the statement would fail to compile. The NullPointerException is reproducible on 10.6 and all subsequent branches.
          Hide
          Knut Anders Hatlen added a comment -

          The insert statement fails because it gets confused as to how many columns the nested select returns. Since the order by column is not in the select list, it gets added to the result columns of the underlying select. It should normally be removed before the insert sees it. However, since the select list contains an INT, and the target type in the table is DOUBLE, a NormalizeResultSetNode is added on top of the SelectNode. NormalizeResultSetNode.init() has code to remove columns added for GROUP BY, but not for ORDER BY. I think it should work if NRSN.init() also removed ORDER BY columns.

          Show
          Knut Anders Hatlen added a comment - The insert statement fails because it gets confused as to how many columns the nested select returns. Since the order by column is not in the select list, it gets added to the result columns of the underlying select. It should normally be removed before the insert sees it. However, since the select list contains an INT, and the target type in the table is DOUBLE, a NormalizeResultSetNode is added on top of the SelectNode. NormalizeResultSetNode.init() has code to remove columns added for GROUP BY, but not for ORDER BY. I think it should work if NRSN.init() also removed ORDER BY columns.
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch (d6006-1a-remove-extra-columns.diff) makes the suggested change to NormalizeResultSetNode.init() and adds a test case to verify the fix.

          I'm running the full regression test suite now to verify that it doesn't break anything.

          Show
          Knut Anders Hatlen added a comment - The attached patch (d6006-1a-remove-extra-columns.diff) makes the suggested change to NormalizeResultSetNode.init() and adds a test case to verify the fix. I'm running the full regression test suite now to verify that it doesn't break anything.
          Hide
          Knut Anders Hatlen added a comment -

          All regression tests passed with the patch.

          Show
          Knut Anders Hatlen added a comment - All regression tests passed with the patch.
          Hide
          Dag H. Wanvik added a comment -

          The fix looks correct to me, +1. Cf also similar code in the new patch I made for DERBY-6008 in SetOperatorNode#buildRCL.

          Show
          Dag H. Wanvik added a comment - The fix looks correct to me, +1. Cf also similar code in the new patch I made for DERBY-6008 in SetOperatorNode#buildRCL.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Dag.

          Committed revision 1417991.

          Show
          Knut Anders Hatlen added a comment - Thanks, Dag. Committed revision 1417991.
          Hide
          Bryan Pendleton added a comment -

          As yes, the generated order by columns appearing where code doesn't expect them to.

          A nice clean patch, clearly described. Thanks for cleaning this up!

          Perhaps a future enhancement would be to have a single method that
          removed both generated GROUP BY columns and generated ORDER BY
          columns, so that code like:

          prRCList.removeGeneratedGroupingColumns();
          prRCList.removeOrderByColumns();

          would instead be a single call to something like:

          prRCList.removeGeneratedColumns();

          Show
          Bryan Pendleton added a comment - As yes, the generated order by columns appearing where code doesn't expect them to. A nice clean patch, clearly described. Thanks for cleaning this up! Perhaps a future enhancement would be to have a single method that removed both generated GROUP BY columns and generated ORDER BY columns, so that code like: prRCList.removeGeneratedGroupingColumns(); prRCList.removeOrderByColumns(); would instead be a single call to something like: prRCList.removeGeneratedColumns();
          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Bryan. I agree that it would be nice with a single call. But it looks like we still need to preserve the two original methods, since SelectNode.genProjectRestrict() and SelectNode.preprocess() handle GROUP BY and ORDER BY separately. It might of course be possible reorganize it somehow.

          On the bright side, it looks like we've squashed this particular class of bugs for now. There are three calls to removeGeneratedGroupingColumns() in the code base: one in SelectNode.genProjectRestrict(), one in NormalizeResultSet.init(), and one in SetOperator.buildRCL(). SelectNode already had calls to removeOrderByColumns(). This issue adds it to NormalizeResultSet. And Dag will add it to SetOperator in DERBY-6008.

          Show
          Knut Anders Hatlen added a comment - Thanks, Bryan. I agree that it would be nice with a single call. But it looks like we still need to preserve the two original methods, since SelectNode.genProjectRestrict() and SelectNode.preprocess() handle GROUP BY and ORDER BY separately. It might of course be possible reorganize it somehow. On the bright side, it looks like we've squashed this particular class of bugs for now. There are three calls to removeGeneratedGroupingColumns() in the code base: one in SelectNode.genProjectRestrict(), one in NormalizeResultSet.init(), and one in SetOperator.buildRCL(). SelectNode already had calls to removeOrderByColumns(). This issue adds it to NormalizeResultSet. And Dag will add it to SetOperator in DERBY-6008 .
          Hide
          Mike Matrigali added a comment - - edited

          temp assigning to myself for backport.

          backporting the following change from the 10.10 branch:
          r1417991 | kahatlen | 2012-12-06 08:33:42 -0800 (Thu, 06 Dec 2012) | 4 lines

          DERBY-6006: NullPointerException in INSERT INTO ... SELECT FROM ... ORDER BY

          A NormalizeResultSet should not expose generated ORDER BY columns to
          its parent.

          Show
          Mike Matrigali added a comment - - edited temp assigning to myself for backport. backporting the following change from the 10.10 branch: r1417991 | kahatlen | 2012-12-06 08:33:42 -0800 (Thu, 06 Dec 2012) | 4 lines DERBY-6006 : NullPointerException in INSERT INTO ... SELECT FROM ... ORDER BY A NormalizeResultSet should not expose generated ORDER BY columns to its parent.
          Hide
          ASF subversion and git services added a comment -

          Commit 1502371 from mikem@apache.org
          [ https://svn.apache.org/r1502371 ]

          DERBY-6006: NullPointerException in INSERT INTO ... SELECT FROM ... ORDER BY

          backporting change #1417991 by kahatlen from 10.10 to 10.9 branch.

          A NormalizeResultSet should not expose generated ORDER BY columns to
          its parent.

          Show
          ASF subversion and git services added a comment - Commit 1502371 from mikem@apache.org [ https://svn.apache.org/r1502371 ] DERBY-6006 : NullPointerException in INSERT INTO ... SELECT FROM ... ORDER BY backporting change #1417991 by kahatlen from 10.10 to 10.9 branch. A NormalizeResultSet should not expose generated ORDER BY columns to its parent.
          Hide
          ASF subversion and git services added a comment -

          Commit 1502717 from mikem@apache.org
          [ https://svn.apache.org/r1502717 ]

          DERBY-6006: NullPointerException in INSERT INTO ... SELECT FROM ... ORDER BY

          backporting change #1417991 by kahatlen from 10.10 to 10.8 branch.

          A NormalizeResultSet should not expose generated ORDER BY columns to
          its parent.

          Show
          ASF subversion and git services added a comment - Commit 1502717 from mikem@apache.org [ https://svn.apache.org/r1502717 ] DERBY-6006 : NullPointerException in INSERT INTO ... SELECT FROM ... ORDER BY backporting change #1417991 by kahatlen from 10.10 to 10.8 branch. A NormalizeResultSet should not expose generated ORDER BY columns to its parent.
          Hide
          Mike Matrigali added a comment -

          backported to 10.9 and 10.8. Could be backported more, but don't plan on it for now. setting original owner back.

          Show
          Mike Matrigali added a comment - backported to 10.9 and 10.8. Could be backported more, but don't plan on it for now. setting original owner back.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development