db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3872) NullPoinerException thrown when INTEGER function used as a predicate in a WHERE clause of a SELECT .. GROUP BY .. HAVING statement
Date Thu, 18 Sep 2008 21:22:44 GMT

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


Mime
View raw message