Derby
  1. Derby
  2. DERBY-3253

NullPointer Exception (NPE) from query with IN predicate containing two values and joining a view with a large table. ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.3.1.4, 10.3.2.1, 10.4.1.3
    • Fix Version/s: 10.3.3.0, 10.4.1.3
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Regression

      Description

      With a single value in the IN clause the query does not fail.
      > Run the following query in the attached database (v 10.3 db).

      SELECT A.TIMESTAMP, B.F_NAMEADDR, B.TOTAL_F,
      B.TOTAL_FS, B.TOTAL_FT, B.TOTAL_FX
      FROM TIME A, THE_VIEW B
      WHERE B.T_ID = A.T_ID AND B.F_NAMEADDR IN ('one.two.three.oscar','one.two.three.kathy')
      ORDER BY A.TIMESTAMP ASC;

      > result

      ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
      ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

      Stack trace:
      Failed Statement is: SELECT A.TIMESTAMP, B.F_NAMEADDR, B.TOTAL_F,
      B.TOTAL_FS, B.TOTAL_FT, B.TOTAL_FX
      FROM TIME A, THE_VIEW B
      WHERE B.T_ID = A.T_ID AND B.F_NAMEADDR IN ('one.two.three.oscar','one.two.three.kathy')
      ORDER BY A.TIMESTAMP ASC
      ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.iapi.error.StandardException.unexpectedUserException(Unknown Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.getRowFromResultSet(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.getNextRowFromRS(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.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)
      at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(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.Main14.main(Unknown Source)
      at org.apache.derby.tools.ij.main(Unknown Source)
      Caused by: java.lang.NullPointerException
      at org.apache.derby.exe.ac601a400fx0116xa813xc2f7x00000010a3602.e8(Unknown Source)
      ... 21 more
      ============= begin nested exception, level (1) ===========
      java.lang.NullPointerException
      at org.apache.derby.exe.ac601a400fx0116xa813xc2f7x00000010a3602.e8(Unknown Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.getRowFromResultSet(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.getNextRowFromRS(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(Unknown Source)
      at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.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)
      at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
      at org.apache.derby.impl.tools.ij.utilMain.doCatch(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.Main14.main(Unknown Source)
      at org.apache.derby.tools.ij.main(Unknown Source)
      ============= end nested exception, level (1) ===========

      Schema info:

      CREATE TABLE TIME ("T_ID" BIGINT NOT NULL, "TIMESTAMP" TIMESTAMP NOT NULL, "DAY" INTEGER NOT NULL, "WEEK" INTEGER NOT NULL, "MONTH" INTEGER NOT NULL, "YEAR_COL" INTEGER NOT NULL);

      CREATE TABLE F ("F_ID" BIGINT NOT NULL, "T_ID" BIGINT NOT NULL, "F_NAMEADDR" VARCHAR(250) NOT NULL, "TOTAL_F" BIGINT NOT NULL, "TOTAL_FS" BIGINT NOT NULL, "TOTAL_FT" BIGINT NOT NULL, "TOTAL_FX" BIGINT NOT NULL);

      CREATE VIEW the_view AS SELECT T.T_ID AS T_ID , T.F_NAMEADDR AS F_NAMEADDR,
      T.TOTAL_F AS TOTAL_F, T.TOTAL_FS AS TOTAL_FS, T.TOTAL_FT AS TOTAL_FT , T.TOTAL_FX AS TOTAL_FX
      FROM F AS T
      WHERE T.T_ID = (SELECT MAX(T_ID) FROM F);

      1. 3253ReproDB.zip
        2.17 MB
        Stan Bradbury
      2. d3253_v1.patch
        5 kB
        A B
      3. d3253_v2_incomplete.patch
        10 kB
        A B
      4. d3253_v3.patch
        9 kB
        A B

        Issue Links

          Activity

          Gavin made changes -
          Workflow jira [ 12418587 ] Default workflow, editable Closed status [ 12801589 ]
          Dag H. Wanvik made changes -
          Bug behavior facts [Regression]
          Knut Anders Hatlen made changes -
          Link This issue is duplicated by DERBY-3464 [ DERBY-3464 ]
          Stan Bradbury made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          A B made changes -
          Fix Version/s 10.3.2.2 [ 12312885 ]
          Affects Version/s 10.3.2.1 [ 12312876 ]
          A B made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Fix Version/s 10.4.0.0 [ 12312540 ]
          Resolution Fixed [ 1 ]
          Bryan Pendleton made changes -
          Assignee A B [ army ]
          A B made changes -
          Attachment d3253_v3.patch [ 12371773 ]
          Attachment d3253_v2_incomplete.patch [ 12371772 ]
          A B made changes -
          Attachment d3253_v1.patch [ 12371649 ]
          Kathey Marsden made changes -
          Component/s SQL [ 11408 ]
          A B made changes -
          Link This issue is related to DERBY-47 [ DERBY-47 ]
          A B made changes -
          Derby Info [Regression]
          Stan Bradbury made changes -
          Field Original Value New Value
          Attachment 3253ReproDB.zip [ 12370993 ]
          Stan Bradbury created issue -

            People

            • Assignee:
              A B
              Reporter:
              Stan Bradbury
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development