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 Wed, 08 Oct 2008 18:41:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3872?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12638026#action_12638026
] 

Mamta A. Satoor commented on DERBY-3872:
----------------------------------------

Just wanted to summarize in brief my findings on this jira entry.

The main thing to notice about the query below is that derby engine identifies it to be the
kind which can fall into sort avoidance code. 
connect 'jdbc:derby:c:/dellater/db1;create=true';
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") ); 

This query is internally dealt in two parts, First one is the
select q1."DEPTNO" from DEPTTAB q1, EMPTAB q2 -- DERBY-PROPERTIES joinStrategy = HASH 
where ( q2."DEPT_DEPTNO" = q1."DEPTNO") 
GROUP BY q1."DEPTNO" 
and the second part is
HAVING 1 = 
  ( select q3."SALARY" from EMPTAB q3 where ( q3."EMPID" = q1."DEPTNO") ); 

Since the query qualifies for sort avoidance, Derby scans through the rows in the DEPTTAB
and EMPTAB to do our own sorting and this leaves us with current row for DEPTTAB and EMPTAB
to be nulls. Once we find the row for the first part of the query, Derby enforces the HAVING
clause of the query as a restriction on the row returned from the first query. During the
application of the restriction, q1."DEPTNO" in HAVING clause has Derby look at the current
row for DEPTTAB but since it is null, we end up getting null pointer exception. If I comment
out the sort avoidance detection in the compile phase of the query, we do not run into NPE.

Hope this summary is useful in understanding why we are running into NPE. I have to say that
I do not have lot of ideas at this point as to how to solve the NPE. I will keep poking into
the code but if anyone has any pointers/ideas, I can definitely use them.

> 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