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