You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2014/03/06 11:46:43 UTC
[jira] [Commented] (DERBY-4403) Assert failure (sane) or
NullPointerException (insane) when attempting to GROUP BY expression
containing scalar subquery
[ https://issues.apache.org/jira/browse/DERBY-4403?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13922290#comment-13922290 ]
Knut Anders Hatlen commented on DERBY-4403:
-------------------------------------------
With production jars, I see a NullPointerException instead:
{noformat}
ij> create table t(i int, j int);
0 rows inserted/updated/deleted
ij> insert into t values (1,2);
1 row inserted/updated/deleted
ij> select sum(i) from t group by j + (select j from t where i=1);
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
{noformat}
The failing assert seems to predate DERBY-883, which added support for expressions in the GROUP BY list. I think it would make sense to disallow subqueries in the GROUP BY list and throw a syntax error. Expressions in GROUP BY is an extension to the SQL standard, and an equivalent, standard-compliant query could be expressed as a nested query, so I think it's not worth the trouble to enhance the non-standard syntax with subquery support.
The portable equivalent of the the failing query would be something like this:
{noformat}
select sum(x) from (select i, j + (select j from t where i=1) from t) s(x,y) group by y
{noformat}
Derby does seem to accept this variant.
> Assert failure (sane) or NullPointerException (insane) when attempting to GROUP BY expression containing scalar subquery
> ------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4403
> URL: https://issues.apache.org/jira/browse/DERBY-4403
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1
> Reporter: Dag H. Wanvik
> Labels: derby_triage10_5_2
>
> The following is not caught by the error checking (or maybe it is supposed to work?):
> select sum(i) from t group by j + (select j from t where i=1)
> java.sql.SQLException: Java exception: 'ASSERT FAILED dummySubqueryList.size() is expected to be 0: org.apache.derby.shared.common.sanity.AssertFailure'.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
> ... 16 more
> Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED dummySubqueryList.size() is expected to be 0
> at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:120)
> at org.apache.derby.impl.sql.compile.GroupByList.bindGroupByColumns(GroupByList.java:220)
> at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:629)
> at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:227)
> at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:140)
> at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:249)
> at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:319)
> at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
> at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:824)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606)
--
This message was sent by Atlassian JIRA
(v6.2#6252)