[
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
derby-4698-1.stat
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 SelectNode.
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: 10.4.2.0, 10.5.3.0, 10.6.1.0
> Environment: Windows, Java 1.6
> Reporter: Matt Doran
> Attachments: derby-4698-1.diff, derby-4698-1.stat,
> derby-crash-10.4.2.0.log, derby-crash-10.5.3.0.log, derby-crash-10.6.1.0.log,
> derby.zip
>
>
> Running a simple SQL query containing a having clause causes a
> NullPointerException. I originally encountered this in 10.4.2.0, but have
> also reproduces this in 10.5.3.0 and the latest 10.6.1.0. 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
> 10.4.2.0 I found that without the "where" clause is didn't crash but returned
> no results when it should have).
> 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 10.6.1.0:
> 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
> Source)
> at
> org.apache.derby.impl.sql.compile.AggregateNode.getNewExpressionResultColumn(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.GroupByNode.addAggregateColumns(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.GroupByNode.addNewColumnsForAggregation(Unknown
> Source)
> 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
> 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.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.
