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

          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Resolved Resolved
          3d 3h 32m 1 Knut Anders Hatlen 06/Dec/12 16:34
          Resolved Resolved Closed Closed
          192d 16h 52m 1 Knut Anders Hatlen 17/Jun/13 09:27
          Mike Matrigali made changes -
          Assignee Mike Matrigali [ mikem ] Knut Anders Hatlen [ knutanders ]
          Fix Version/s 10.8.3.1 [ 12323475 ]
          Fix Version/s 10.9.2.2 [ 12323562 ]
          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.
          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
          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.
          Mike Matrigali made changes -
          Assignee Knut Anders Hatlen [ knutanders ] Mike Matrigali [ mikem ]
          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.
          Gavin made changes -
          Workflow jira [ 12736667 ] Default workflow, editable Closed status [ 12802876 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          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
          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();
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Issue & fix info Patch Available,Repro attached [ 10102, 10424 ] Repro attached [ 10424 ]
          Fix Version/s 10.10.0.0 [ 12321550 ]
          Resolution Fixed [ 1 ]
          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
          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.
          Knut Anders Hatlen made changes -
          Issue & fix info Repro attached [ 10424 ] Patch Available,Repro attached [ 10102, 10424 ]
          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.
          Knut Anders Hatlen made changes -
          Attachment d6006-1a-remove-extra-columns.diff [ 12555948 ]
          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 -

          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.
          Knut Anders Hatlen made changes -
          Assignee Knut Anders Hatlen [ knutanders ]
          Knut Anders Hatlen made changes -
          Link This issue relates to DERBY-4397 [ DERBY-4397 ]
          Knut Anders Hatlen made changes -
          Bug behavior facts Seen in production [ 10421 ]
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Affects Version/s 10.7.1.1 [ 12315564 ]
          Affects Version/s 10.6.2.1 [ 12315343 ]
          Affects Version/s 10.8.3.0 [ 12323456 ]
          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 -

          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)
          Knut Anders Hatlen created issue -

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development