[ https://issues.apache.org/jira/browse/DERBY-6863?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-6863: -------------------------------------- Bug behavior facts: Crash,Seen in production,Wrong query result (was: Crash,Deviation from standard,Seen in production,Wrong query result) Removed the "Deviation from standard" flag. The standard only allows column references in the GROUP BY clause, so the failing query is non-standard. Allowing expressions is an extension added in DERBY-883. With many fallouts similar to this bug, unfortunately... > NPE when multiple values are contained in an IN statement within a CASE > statement used in a GROUP BY > ---------------------------------------------------------------------------------------------------- > > Key: DERBY-6863 > URL: https://issues.apache.org/jira/browse/DERBY-6863 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.12.1.1 > Environment: Java 8, Mint Linux, 24G, Used within an in-memory table > Reporter: Peter Damen > Attachments: ac_86.decomp, derby.log > > > 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 -- This message was sent by Atlassian JIRA (v6.3.4#6332)