[
https://issues.apache.org/jira/browse/DERBY-3253?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
A B updated DERBY-3253:
-----------------------
Attachment: d3253_v3.patch
d3253_v2_incomplete.patch
Hi Bryan,
Thank you for your continued feedback.
I'm attaching two files. The first is d3253_v2_incomplete.patch, which was my
first attempt at addressing the issue in the "other" way, i.e.:
> add logic in BinaryRelationalOperatorNode that sends all method calls
> relating to leftOperand on down to the InListOperatorNode, as well.
I tried the obvious places where this had to be done and the error reported for
this issue did indeed disappear. However, running more complicated queries
with that approach leads to *other* execution-time NullPointerExceptions, plus
a few changed query plans. When I applied the patch and ran derbyall there
were four test failures, all of which suffered from at least one execution-time
NPE:
derbyall/derbyall.fail:lang/inbetween.sql
derbyall/derbyall.fail:lang/predicatePushdown.sql
derbyall/derbyall.fail:lang/predicatesIntoViews.sql
derbyall/derbyall.fail:lang/subquery.sql
I looked briefly into this to try to figure out what the problem was, but I
admit that I gave up pretty quickly. In order to be sure that this approach
works, I think we'd have to 1) look through all of the code to find any place
that retrieved the left operand of a BinaryRelationalOperatorNode, 2) check to
see if that place made any changes to the left operand in any way (direct or
indirect), and 3) if changes were made, add logic to propagate those changes
down to left operand of the InListOperatorNode that is tucked away inside the
BinaryRelationalOperatorNode (if there is one). Does that sound correct to
you? Or am I making this too complicated?
The fact that just hitting what I thought to be the "basics" causes failures
leads me back to my original assessment of this approach:
> a) that would require more logic in more places in
> BinaryRelationalOperatorNode
> to propagate operations down to the InListOperatorNode, and b) it seems like
> the odds of missing some leftOperand operation could be non-neglible for very
> complicated queries.
You mentioned:
> it seems valid that the code has to be routinely passing method calls to both
> variants of the operator, since in a sense it *is* both variants of the
> operator,
> simultaneously, until at some crucial instant the optimizer determines that
> it can finalize the decision as to which variant to use.
Is this in some way saying that instead of "do the work once and apply the
result to the correct place", we'd be opting for "do the work twice and then
throw one of the results away"? Given the fact the doing the work a "second"
time affects more code and is more error-prone, I wonder if that is really the
best approach? Regardless of which "version" of the operator we use, there
should only be one correct form for the left operand when it comes time to
generate code--and that should be exactly same between the two "versions". So
if we have the operand in its correct form, it seems reasonable to just push it
to the right place for code generation...
I fully admit that I may be biased here and that my bias may be keeping from
seeing a silly mistake (or omission) in my attempt at this approach. So I've
attached my changes to this issue (d3253_v2_incomplete.patch); please feel free
to look at it and to clean it up if there's anything obvious I missed. My
apologies in advance if that proves to be the case.
I'm also attaching a second file, d3253_v3.patch, which is a slight variation
of the _v1 patch. With this approach we still use the
"setLeftOperand(this.leftOperand)" approach seen in _v1, but instead of doing
it once at code generation time, we do it as part of the
BinaryRelationalOperatorNode.getInListOp() method. That is to say, any time
any caller tries to access the InListOperatorNode that sits beneath a "probe
predicate", we ensure that the InListOperatorNode has a valid, up-to-date left
operand--which comes from the probe predicate itself--before returning it to
the caller. This solves the issue, and passes all regression tests.
I feel like _v3 is the best of the three approaches. It has more changes than
_v1 but unlike _v1, it ensures that *any* code which accesses the underlying
InListOperatorNode will see a left operand that has the correct information.
It also seems a tad more maintainable than _v2 since the latter requires that
any future code which directly or indirectly changes a
BinaryRelationalOperatorNode's left operand must also account for the
possibility that such a node is a probe predicate with an underlying
InListOperatorNode.
I'd like to proceed with the _v3 patch as my proposed solution. If it is still
generally felt that _v2 is the best overall approach, then perhaps I can go
ahead with the _v3 approach for now and anyone who is so inclined can implement
the _v2 approach as a longer term solution. Does that seem like an acceptable
proposal, or do you think that _v3 is in itself an incorrect or un-commitable
fix?
> NullPointer Exception (NPE) from query with IN predicate containing two
> values and joining a view with a large table. ERROR 38000: The exception
> 'java.lang.NullPointerException' was thrown while evaluating an expression.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3253
> URL: https://issues.apache.org/jira/browse/DERBY-3253
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.4.0.0
> Reporter: Stan Bradbury
> Attachments: 3253ReproDB.zip, d3253_v1.patch,
> d3253_v2_incomplete.patch, d3253_v3.patch
>
>
> With a single value in the IN clause the query does not fail.
> > Run the following query in the attached database (v 10.3 db).
> SELECT A.TIMESTAMP, B.F_NAMEADDR, B.TOTAL_F,
> B.TOTAL_FS, B.TOTAL_FT, B.TOTAL_FX
> FROM TIME A, THE_VIEW B
> WHERE B.T_ID = A.T_ID AND B.F_NAMEADDR IN
> ('one.two.three.oscar','one.two.three.kathy')
> ORDER BY A.TIMESTAMP ASC;
> > result
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while
> evaluating an expression.
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> Stack trace:
> Failed Statement is: SELECT A.TIMESTAMP, B.F_NAMEADDR, B.TOTAL_F,
> B.TOTAL_FS, B.TOTAL_FT, B.TOTAL_FX
> FROM TIME A, THE_VIEW B
> WHERE B.T_ID = A.T_ID AND B.F_NAMEADDR IN
> ('one.two.three.oscar','one.two.three.kathy')
> ORDER BY A.TIMESTAMP ASC
> ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while
> evaluating an expression.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at
> org.apache.derby.iapi.error.StandardException.unexpectedUserException(Unknown
> Source)
> at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.SortResultSet.getRowFromResultSet(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.SortResultSet.getNextRowFromRS(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.SortResultSet.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.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source)
> at org.apache.derby.tools.ij.main(Unknown Source)
> Caused by: java.lang.NullPointerException
> at
> org.apache.derby.exe.ac601a400fx0116xa813xc2f7x00000010a3602.e8(Unknown
> Source)
> ... 21 more
> ============= begin nested exception, level (1) ===========
> java.lang.NullPointerException
> at
> org.apache.derby.exe.ac601a400fx0116xa813xc2f7x00000010a3602.e8(Unknown
> Source)
> at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.SortResultSet.getRowFromResultSet(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.SortResultSet.getNextRowFromRS(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.SortResultSet.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.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source)
> at org.apache.derby.tools.ij.main(Unknown Source)
> ============= end nested exception, level (1) ===========
> Schema info:
> CREATE TABLE TIME ("T_ID" BIGINT NOT NULL, "TIMESTAMP" TIMESTAMP NOT NULL,
> "DAY" INTEGER NOT NULL, "WEEK" INTEGER NOT NULL, "MONTH" INTEGER NOT NULL,
> "YEAR_COL" INTEGER NOT NULL);
> CREATE TABLE F ("F_ID" BIGINT NOT NULL, "T_ID" BIGINT NOT NULL, "F_NAMEADDR"
> VARCHAR(250) NOT NULL, "TOTAL_F" BIGINT NOT NULL, "TOTAL_FS" BIGINT NOT NULL,
> "TOTAL_FT" BIGINT NOT NULL, "TOTAL_FX" BIGINT NOT NULL);
> CREATE VIEW the_view AS SELECT T.T_ID AS T_ID , T.F_NAMEADDR AS
> F_NAMEADDR,
> T.TOTAL_F AS TOTAL_F, T.TOTAL_FS AS TOTAL_FS, T.TOTAL_FT AS TOTAL_FT ,
> T.TOTAL_FX AS TOTAL_FX
> FROM F AS T
> WHERE T.T_ID = (SELECT MAX(T_ID) FROM F);
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
