db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4698) Simple query with HAVING clause crashes with NullPointerException
Date Mon, 14 Jun 2010 21:07:14 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Dag H. Wanvik updated DERBY-4698:

    Attachment: derby-4698-1.diff

Uploading a preliminary patch which seems to fix this problem. 

My theory is the following: As part of DERBY-3880 a fix was inserted to handle the case of
column references in HAVING clauses being wrong after JOIN flattening. 

Normally, such column references are updated as part of the flattening operation in the preprocess
phase; i.e. in the first of the three main phases in the statement optimization: 

          {preprocess, optimize, modifyAccessPaths}

cf DMLStatementNode#optimizeStatement. The flattening is driven by SelectNode#preprocess in
the call to fromList.flattenFromTables.

The fix for DERBY-3880 involved remapping the now wrong column reference in the HAVING clause
(i.e. after join flattening) as part of the *modifyAccessPaths* phase, cf the fix in AggregateNode#getNewExpressionResultColumn,
ca line 566 on trunk.

At the time, Bryan asked if the fix wasn't somewhat odd, since the phase in which the column
reference remapping for the having clause was now done, was *different* from the other column
reference fixups and suggested that it might be more appropriate to remap column references
from the HAVING clause by passing in the havingClause in the flattenFromTables call made by

I agree with Bryan that this sounds like a sounder approach, and it is the approach taken
by this patch. It makes both queries shown in this JIRA issue work with the supplied database.

The problem encountered by the old fix in this case, is that the column reference's VCN's
sourceResultSet (due to the presence of indexes?) has a result column which have already been
converted to a CurrentRowLocationNode, cf the rewrite calls to addRCForRID that happen in
fromBaseTable#changeAccessPath. This seems a correct transformation, but makes the remapping.
So, essentially, the rewrite is attempted too late, at least for the problematic queries.

On 10.6, with debug code enabled, we don't see a NPE, but an assert that the desired column
(A.BALANCE) could not be found. On trunk, the assert is different due to the change introduced
by DERBY-4679: it complains that it does not expect to see a CurrentRowLocationNode, alas.
In both cases, the problem is the same, the CurrentRowLocationNode's presence is unexpected
and makes the remapping fail.

By treating column references in the havingClause uniformly with other column references at
flattening time in the preprocess phase, there is no need for the old fix code, so the patch
removes that.

GroupByTest which contains the test case for DERBY-3880 still passes with the patch, so it
would seem the patch is a valid alternate solution for DERBY-3880 also.

Will run full regressions and try to make a simple repro so we can add a new test case for
this issue.

> Simple query with HAVING clause crashes with NullPointerException
> -----------------------------------------------------------------
>                 Key: DERBY-4698
>                 URL: https://issues.apache.org/jira/browse/DERBY-4698
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:,,
>         Environment: Windows, Java 1.6
>            Reporter: Matt Doran
>         Attachments: derby-4698-1.diff, derby-4698-1.stat, derby-crash-,
derby-crash-, derby-crash-, derby.zip
> Running a simple SQL query containing a having clause causes a NullPointerException.
 I originally encountered this in, but have also reproduces this in and
the latest   I raised this on the mailing list too, and Knut said it also fails
on trunk - see http://thread.gmane.org/gmane.comp.apache.db.derby.user/12782
> The query (created throw Hibernate) that causes the crash is:
> SELECT user0_.user_id         AS col_0_0_,
>        SUM(account2_.balance) AS col_1_0_
> FROM   tbl_user user0_
>        INNER JOIN tbl_user_account accountlin1_
>          ON user0_.user_id = accountlin1_.user_id
>        INNER JOIN tbl_account account2_
>          ON accountlin1_.account_id = account2_.account_id
> WHERE  user0_.deleted = 'N'
>        AND ( account2_.account_type IN ( 'USER-01', 'USER' ) )
> GROUP  BY user0_.user_id
> HAVING SUM(account2_.balance) >= 100.0 
> However I simplified it to the following and still caused a crash (though in
I found that without the "where" clause is didn't crash but returned no results when it should
> SELECT u.user_id,
>        SUM(a.balance)
> FROM   tbl_user u
>        INNER JOIN tbl_user_account al
>          ON u.user_id = al.user_id
>        INNER JOIN tbl_account a
>          ON al.account_id = a.account_id
> GROUP BY u.user_id
> HAVING sum(a.balance) >= 10.0 
> The derby log showed the following stace trace for
> 2010-06-14 04:59:24.942 GMT Thread[main,5,main] (XID = 5824013), (SESSIONID = 1), (DATABASE
= C:\Development\pc-ng-branch\server\working\data\internal/derby), (DRDAID = null), Failed
Statement is: SELECT u.user_id user_id,
>        SUM(a.balance) acct_sum
> FROM   tbl_user u
>        INNER JOIN tbl_user_account al
>          ON u.user_id = al.user_id
>        INNER JOIN tbl_account a
>          ON al.account_id = a.account_id
> GROUP BY u.user_id
> HAVING sum(a.balance) >= 1.0 
> java.lang.NullPointerException
> 	at org.apache.derby.impl.sql.compile.ColumnReference.remapColumnReferencesToExpressions(Unknown
> 	at org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown
> 	at org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown
> 	at org.apache.derby.impl.sql.compile.GroupByNode.addAggregates(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.GroupByNode.init(Unknown Source)
> 	at org.apache.derby.iapi.sql.compile.NodeFactory.getNode(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.SelectNode.genProjectRestrict(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.SelectNode.modifyAccessPaths(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> 	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> 	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.Main.main(Unknown Source)
> 	at org.apache.derby.tools.ij.main(Unknown Source)
> Cleanup action completed

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message