[
https://issues.apache.org/jira/browse/DERBY-3872?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12632395#action_12632395
]
Mamta A. Satoor commented on DERBY-3872:
----------------------------------------
I have some more finidings to share on Query1
select q1."DEPTNO" from DEPTTAB q1, EMPTAB q2 -- DERBY-PROPERTIES
joinStrategy = HASH
where ( q2."DEPT_DEPTNO" = q1."DEPTNO")
GROUP BY q1."DEPTNO" HAVING 1 =
( select q3."SALARY" from EMPTAB q3 where ( q3."EMPID" = q1."DEPTNO") ) ;
The query as written above results in NPE but if I change the order of tables
in the outer query's from list as shown below, there is no NPE
select q1."DEPTNO" from EMPTAB q2, DEPTTAB q1 -- DERBY-PROPERTIES
joinStrategy = HASH
where ( q2."DEPT_DEPTNO" = q1."DEPTNO")
GROUP BY q1."DEPTNO" HAVING 1 =
( select q3."SALARY" from EMPTAB q3 where ( q3."EMPID" = q1."DEPTNO") ) ;
It appears that changing the order of the tables makes Derby bypass the sort
avoidance code. To be more specific, impl.sql.compile.GroupByNode:init method
has following check
if (index == glSize) {
isInSortedOrder = childResult.isOrderedOn(crs, true, (Vector)null);
}
This piece of code assigns true to isInSortedOrder if DEPTTAB is first in the
from list of query. The code comment in JoinNode.isOrdered which is what gets
called by the code segment above has following interesting comment
/* RESOLVE - easiest thing for now is to only consider the leftmost child */
This is why if I change the order of the tables in the from list, DEPTTAB is no
more the leftmost child and hence isInSortedOrder ends up getting a value of
FALSE when DEPTTAB is not the first table in the from list. (This piece of code
so far is in Derby's query compile code.)
At query execution time, we check this flag is
impl.sql.execute.GroupedAggregateResultSet:openCore as shown below
/* If this is an in-order group by then we do not need the sorter.
* (We can do the aggregation ourselves.)
* We save a clone of the first row so that subsequent next()s
* do not overwrite the saved row.
*/
if (isInSortedOrder)
{
currSortedRow = getNextRowFromRS();
if (currSortedRow != null)
{
currSortedRow = (ExecIndexRow)currSortedRow.getClone();
initializeVectorAggregation(currSortedRow);
}
}
else
{
/*
** Load up the sorter
*/
scanController = loadSorter();
}
If isInSortOrder is found to be true, we go through the code path which causes
us to run into NPE. I need to do more debugging but I think what happens in
this code path is we set the current row associated with various internal
resutsets hanging off of the Activation object and most of these current row
end up being NULL and later on, the code is looking for specific column value
in the current row not expecting the current row to be NULL.
I will also try to compare the code flow in 10.2 codeline vs trunk because the
query in question works fine in 10.2 no matter how the tables are listed the
from list in the query.
> 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.
