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

NPE when multiple values are contained in an IN statement within a CASE statement used in a GROUP BY

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.12.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Java 8, Mint Linux, 24G, Used within an in-memory table
    • Urgency:
      Urgent
    • Issue & fix info:
      Newcomer
    • Bug behavior facts:
      Crash, Seen in production, Wrong query result

      Description

      An IN statement within a CASE statement within a GROUP BY, that contains more than one element will cause a NPE.

      Reproduction Steps:

      ======== DATA ============

      CREATE TABLE Test (
      Region VARCHAR(20),
      Cost INTEGER
      );

      INSERT INTO Test VALUES ('Australia', 45);
      INSERT INTO Test VALUES ('Asia', 22);
      INSERT INTO Test VALUES ('North America', 33);
      INSERT INTO Test VALUES ('South America', 55);
      INSERT INTO Test VALUES ('Europe', 44);

      ======= SQL WORKS ===========

      SELECT DISTINCT
      CASE
      WHEN 1 = 0 THEN "REGION"
      WHEN "REGION" IN ('Asia') THEN 'A'
      ELSE "REGION"
      END,
      SUM("COST")
      FROM TEST
      GROUP BY
      CASE
      WHEN 1 = 0 THEN "REGION"
      WHEN "REGION" IN ('Asia') THEN 'A'
      ELSE "REGION"
      END

      ============ FAILS ===================

      SELECT DISTINCT
      CASE
      WHEN 1 = 0 THEN "REGION"
      WHEN "REGION" IN ('Asia', 'Australia') THEN 'A'
      ELSE "REGION"
      END,
      SUM("COST")
      FROM TEST
      GROUP BY
      CASE
      WHEN 1 = 0 THEN "REGION"
      WHEN "REGION" IN ('Asia','Australia') THEN 'A'
      ELSE "REGION"
      END

      ================== NPE ===================

      java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(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 sun.reflect.GeneratedMethodAccessor47.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:622)
      at com.onseven.dbvis.b.B.B.ā(Z:2256)
      at com.onseven.dbvis.b.B.F$A.call(Z:2838)
      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
      at java.util.concurrent.FutureTask.run(FutureTask.java:166)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
      at java.lang.Thread.run(Thread.java:701)
      Caused by: ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
      ... 22 more
      Caused by: java.lang.NullPointerException
      at org.apache.derby.exe.acd8cd40bax0152xa074xeeddx0000012ba0d05.e1(Unknown Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(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.executeStmt(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
      ... 13 more

        Attachments

        1. ac_86.decomp
          7 kB
          Bryan Pendleton
        2. derby.log
          20 kB
          Bryan Pendleton
        3. tests.diff
          3 kB
          Bryan Pendleton

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                sproose Peter Damen
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: