db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [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
Date Thu, 09 Oct 2008 06:02:46 GMT

    [ 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.


Mime
View raw message