[
https://issues.apache.org/jira/browse/DERBY-3033?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12529267
]
Bryan Pendleton commented on DERBY-3033:
----------------------------------------
I'm thinking about the interaction between NOT EXISTS query flattening and
equijoin transitive closure,
and I'm getting myself all confused. Here's what I've been thinking:
1) The join predicate which causes startKeyGetter method E7 to be
generated is an equijoin between DISPATCHER_LOCKS.STUDY_ID
and DICOM_SEND_REQUESTS.STUDY_ID.
2) This predicate was NOT expressed in the original query as
an explicit condition in the WHERE clause; rather, it was
generated by Derby during PredicateList.joinClauseTransitiveClosure()
3) Since the result set for DISPATCHER_LOCKS is RS #5, it is
completely valid for the E7 method to be fetching the start key value
from result set #5.
4) But there is no row in result set number 5, because the query wants
to know when the DISPATCHER_LOCKS row does *NOT* exist!
5) That is, we validly flattened the NOT EXISTS subquery into the
parent's two-way query between STUDIES and DICOM_SEND_REQUESTS,
and we remembered enough information about the relationship between
STUDIES and the correlated NOT EXISTS subquery against
DISPATCHER_LOCKS, so that we know that the NestedLoopJoin
from STUDIES to DISPATCHER_LOCKS should return rows from STUDIES
where there is *NOT* a matching row in DISPATCHER_LOCKS, but
then we tried to take the value of the (non-existing) row from
DISPATCHER_LOCKS and use it to do an index-probe into DICOM_SEND_REQUESTS.
So now I'm thinking that when we flattened the NOT EXISTS query
into its parent, we needed to do so in a way that reflected that the
correlated join predicate was NOT suitable for use in transitive closure.
I think it's definitely time for somebody to start checking my work here,
because I'm venturing into areas that are quite unfamiliar to me. Can
somebody who is comfortable with the concepts of
- query flattening
- correlated NOT EXISTS subqueries, and
- transitive closure of equijoins
have a look at this query and offer an opinion about whether it was valid
for Derby to flatten and then transitively close the join in this fashion?
> select query results in nullpointer exception in skipScan()
> -----------------------------------------------------------
>
> Key: DERBY-3033
> URL: https://issues.apache.org/jira/browse/DERBY-3033
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Environment: Windows XP, Java 5.0, JDBC, Derby 10.2.2.0
> Reporter: Haolan Qin
> Assignee: Bryan Pendleton
> Attachments: bug4736.sql, d3033-sane-ij-session-10.3.1.5.txt,
> generatedActivation.txt, query_plan.new, query_plan.old, queryPlanRS.txt,
> test.rar, test.zip, viewer_10_1.zip
>
>
> The following error was repeatedly thrown when we tried to run a select query
> via JDBC. Strangely, the exact same select query did not trigger any error
> when run from the command line console. After we added an index, the error
> went away completely.
> java.lang.NullPointerException
> at org.apache.derby.impl.sql.execute.NoPutResultSetImpl.skipScan(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.TableScanResultSet.openCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet.openCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openRight(Unknown Source)
> at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(Unknown Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.SortResultSet.openCore(Unknown Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.drda.DRDAStatement.execute(Unknown Source)
> at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
> at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
