[
https://issues.apache.org/jira/browse/DERBY-3872?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12638190#action_12638190
]
A B commented on DERBY-3872:
----------------------------
> 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.
