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 "A B (JIRA)" <ji...@apache.org> on 2008/10/09 08:02:46 UTC
[jira] Issue Comment Edited: (DERBY-3872) NullPoinerException
thrown when INTEGER function used as a predicate in a WHERE clause of a
SELECT .. GROUP BY .. HAVING statement
[ https://issues.apache.org/jira/browse/DERBY-3872?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12638190#action_12638190 ]
army edited comment on DERBY-3872 at 10/8/08 11:02 PM:
------------------------------------------------------
> Just wanted to summarize in brief my findings on this jira entry.
Thanks for all of the *great* information on this one, Mamta! I spent some time looking at this and your detailed comments were extremely useful in helping me get up to speed.
> if anyone has any pointers/ideas, I can definitely use them.
I did some tracing based on your earlier observation that "the ResultColumn associated with the HAVING clause is the same object that is associated with the join node". When comparing a successful query with an unsuccessful one, I noticed that in *both* cases the HAVING clause includes a pointer to a ResultColumn that is also referenced by the JoinNode. So that in itself is not necessarily a problem.
However, it turns out that for the successful query, the ResultColumn reference in question is *buried* beneath a chain of VirtualColumn-to-ResultColumn nodes that exist on the right side of the equality (q3."EMPID" = q1."DEPTNO") in the HAVING subquery. In the failing query, though, the ResultColumn reference is the immediate child of a ColumnReference that is the right side of the equality--there are no intervening VirtualColumn nodes nor any other ResultColumns.
I think what this means is that, in the failing query, when we set the result set number for the ResultColumns associated with the JoinNode, that result set number inadvertently gets picked up by the equality in the HAVING subquery, which ultimately leads to the NPE, as you discovered. For the successful query, though, the intervening VirtualColumnNodes and ResultColumns have their *own* (correct) result set numbers, and those effectively "hide" the result set number that was set from the JoinNode.
That said, I did some tracing to try to find out where the intervening VirtualColumnNodes come from in the successful query. It turns out that GroupByNode.addUnAggColumns() creates the nodes, and then it uses a Visitor implementation to "substitute" those nodes into the query tree at the right place(s). For a successful query the Visitor performs the substitution in the base table, as it should; but for the failing query, the substitution never happens. Upon further investigation I noticed that the failing query includes an IndexToBaseRowNode whose child is a FromBaseTable, while the successful query simply includes a FromBaseTable directly. With that difference in mind I set a breakpoint in the "addUnAggColumns()" method at the line:
% havingClause.accept(
% (SubstituteExpressionVisitor)havingRefsToSubstitute.get(r));
and traced from there. One thing I quickly noticed was that the IndexToBaseRowNode class does *not* define an "accept()" method, which means it defaults to the "accept()" method of ResultSetNode. That's a problem because ResultSetNode does not know about the FromBaseTable child that exists beneath IndexToBaseRowNode--so ResultSetNode.accept() correctly visits the IndexToBaseRowNode itself, but does *NOT* visit the underlying FromBaseTable. As luck would have it, the substitution that the code in addUnAggColumns() is trying to make is intended for the FromBaseTable (and esp. for the "restriction" predicate that is contained within it), but since that node is never visited, the substitution never happens.
As an experiment I added an "accept()" method to IndexToBaseRowNode.java that was almost identical to the method as it exists today in SingleChildResultSetNode--I just changed "childResult" to "source" and that was it. When I did that, I could see the substitution in addUnAggColumns() start to take effect for the failing query, which led to insertion of what appear to be the proper VirtualColumn nodes beneath the HAVING subquery's predicate, and in the end, the query ran to completion with no error.
I didn't do *any* other testing of any sort, nor have I looked at the possible side effects of adding an accept() method to IndexToBaseRowNode (I can't think of any offhand). But at first glance it seems like the "right" thing to do, and it made the repro for this issue run without error, so if you're looking for a "what next" step, you could perhaps investigate that angle a bit more...?
Thanks again for your work continued efforts on this issue!
was (Author: army):
> Just wanted to summarize in brief my findings on this jira entry.
Thanks for all of the *great* information on this one, Mamta! I spent some time looking at this and your detailed comments were extremely useful in helping me get up to speed.
> if anyone has any pointers/ideas, I can definitely use them.
I did some tracing based on your earlier observation that "the ResultColumn associated with the HAVING clause is the same object that is associated with the join node". When comparing a successful query with an unsuccessful one, I noticed that in *both* cases the HAVING clause includes a pointer to a ResultColumn that is also referenced by the JoinNode. So that in itself is not necessarily a problem.
However, it turns out that for the successful query, the ResultColumn reference in question is *buried* beneath a chain of VirtualColumn-to-ResultColumn nodes that exist on the right side of the equality (q3."EMPID" = q1."DEPTNO") in the HAVING subquery. In the failing query, though, the ResultColumn reference is the immediate child of a ColumnReference that is the right side of the equality--there are no intervening VirtualColumn nodes nor any other ResultColumns.
I think what this means is that, in the failing query, when we set the result set number for the ResultColumns associated with the JoinNode, that result set number inadvertently gets picked up by the equality in the HAVING subquery, which ultimately leads to the NPE, as you discovered. For the successful query, though, the intervening VirtualColumnNodes and ResultColumns have their *own* (correct) result set numbers, and those effectively "hide" the result set number that was set from the JoinNode.
That said, I did some tracing to try to find out where the intervening VirtualColumnNodes come from in the successful query. It turns out that GroupByNode.addUnAggColumns() creates the nodes, and then it uses a Visitor implementation to "substitute" those nodes into the query tree at the right place(s). For a successful query the Visitor performs the substitution in the base table, as it should; but for the failing query, the substitution never happens. Upon further investigation I noticed that the failing query includes an IndexToBaseRowNode whose child is a FromBaseTable, while the successful query simply includes a FromBaseTable directly. With that difference in mind I set a breakpoint in the "addUnAggColumns()" method at the line:
% havingClause.accept(
% (SubstituteExpressionVisitor)havingRefsToSubstitute.get(r));
and traced from there. One thing I quickly noticed was that the IndexToBaseRowNode class does *not* define a "visit()" method, which means it defaults to the "visit()" method of ResultSetNode. That's a problem because ResultSetNode does not know about the FromBaseTable child that exists beneath IndexToBaseRowNode--so ResultSetNode.visit() correctly visits the IndexToBaseRowNode itself, but does *NOT* visit the underlying FromBaseTable. As luck would have it, the substitution that the code in addUnAggColumns() is trying to make is intended for the FromBaseTable (and esp. for the "restriction" predicate that is contained within it), but since that node is never visited, the substitution never happens.
As an experiment I added a "visit()" method to IndexToBaseRowNode.java that was almost identical to the method as it exists today in SingleChildResultSetNode--I just changed "childResult" to "source" and that was it. When I did that, I could see the substitution in addUnAggColumns() start to take effect for the failing query, which led to insertion of what appear to be the proper VirtualColumn nodes beneath the HAVING subquery's predicate, and in the end, the query ran to completion with no error.
I didn't do *any* other testing of any sort, nor have I looked at the possible side effects of adding a visit() method to IndexToBaseRowNode (I can't think of any offhand). But at first glance it seems like the "right" thing to do, and it made the repro for this issue run without error, so if you're looking for a "what next" step, you could perhaps investigate that angle a bit more...?
Thanks again for your work continued efforts on this issue!
> NullPoinerException thrown when INTEGER function used as a predicate in a WHERE clause of a SELECT .. GROUP BY .. HAVING statement
> -----------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3872
> URL: https://issues.apache.org/jira/browse/DERBY-3872
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.3.0
> Reporter: Stan Bradbury
> Assignee: Mamta A. Satoor
> Attachments: NPE_Reproduction.sql, QueryWithoutTruePred.out, QueryWithTruePred.out
>
>
> Use attached SQL script to create two tables , execute the following SQL and throw the exception and stack trace below. NOTE: removing the 'always true' clause '.. ( integer (1.1) = 1) .." from the SQL and the query does not fail. Releated??
> select q1."DEPTNO" from DEPTTAB q1, EMPTAB q2 where ( integer (1.1) = 1) and ( q2."DEPT_DEPTNO" = q1."DEPTNO")
> GROUP BY q1."DEPTNO"
> HAVING max( q2."SALARY") >= ( select q3."SALARY" from EMPTAB q3 where ( q3."EMPID" = q1."DEPTNO") )
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
> . . .derby.iapi.error.StandardException.newException
> . . .derby.iapi.error.StandardException.unexpectedUserException
> . . .derby.impl.services.reflect.DirectCall.invoke
> . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore
> . . .derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow
> . . .derby.impl.jdbc.EmbedResultSet.movePosition
> . . .derby.impl.jdbc.EmbedResultSet.next
> . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults
> . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults
> . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults
> . . .derby.tools.JDBCDisplayUtil.DisplayResults
> . . .derby.impl.tools.ij.utilMain.displayResult
> . . .derby.impl.tools.ij.utilMain.doCatch
> . . .derby.impl.tools.ij.utilMain.runScriptGuts
> . . .derby.impl.tools.ij.utilMain.go
> . . .derby.impl.tools.ij.Main.go
> . . .derby.impl.tools.ij.Main.mainCore
> . . .derby.impl.tools.ij.Main14.main
> . . .derby.tools.ij.main
> Caused by: java.lang.NullPointerException
> . . .derby.iapi.types.NumberDataType.compare
> . . .derby.impl.store.access.btree.ControlRow.compareIndexRowFromPageToKey
> . . .derby.impl.store.access.btree.ControlRow.searchForEntry
> . . .derby.impl.store.access.btree.LeafControlRow.search
> . . .derby.impl.store.access.btree.BTreeScan.positionAtStartForForwardScan
> . . .derby.impl.store.access.btree.BTreeForwardScan.positionAtStartPosition
> . . .derby.impl.store.access.btree.BTreeForwardScan.fetchRows
> . . .derby.impl.store.access.btree.BTreeScan.fetchNext
> . . .derby.impl.sql.execute.TableScanResultSet.getNextRowCore
> . . .derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore
> . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore
> . . .derby.impl.sql.execute.OnceResultSet.getNextRowCore
> . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.g0
> . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.e6
> ... 17 more
> ============= begin nested exception, level (1) ===========
> java.lang.NullPointerException
> . . .derby.iapi.types.NumberDataType.compare
> . . .derby.impl.store.access.btree.ControlRow.compareIndexRowFromPageToKey
> . . .derby.impl.store.access.btree.ControlRow.searchForEntry
> . . .derby.impl.store.access.btree.LeafControlRow.search
> . . .derby.impl.store.access.btree.BTreeScan.positionAtStartForForwardScan
> . . .derby.impl.store.access.btree.BTreeForwardScan.positionAtStartPosition
> . . .derby.impl.store.access.btree.BTreeForwardScan.fetchRows
> . . .derby.impl.store.access.btree.BTreeScan.fetchNext
> . . .derby.impl.sql.execute.TableScanResultSet.getNextRowCore
> . . .derby.impl.sql.execute.IndexRowToBaseRowResultSet.getNextRowCore
> . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore
> . . .derby.impl.sql.execute.OnceResultSet.getNextRowCore
> . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.g0
> . . .derby.exe.ac601a400fx011cx480cx5eacx00000010d8100.e6
> . . .derby.impl.services.reflect.DirectCall.invoke
> . . .derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore
> . . .derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow
> . . .derby.impl.jdbc.EmbedResultSet.movePosition
> . . .derby.impl.jdbc.EmbedResultSet.next
> . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults
> . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults
> . . .derby.tools.JDBCDisplayUtil.indent_DisplayResults
> . . .derby.tools.JDBCDisplayUtil.DisplayResults
> . . .derby.impl.tools.ij.utilMain.displayResult
> . . .derby.impl.tools.ij.utilMain.doCatch
> . . .derby.impl.tools.ij.utilMain.runScriptGuts
> . . .derby.impl.tools.ij.utilMain.go
> . . .derby.impl.tools.ij.Main.go
> . . .derby.impl.tools.ij.Main.mainCore
> . . .derby.impl.tools.ij.Main14.main
> . . .derby.tools.ij.main
> ============= end nested exception, level (1) ===========
> Cleanup action completed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.