db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3253) 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.
Date Mon, 17 Dec 2007 03:06:43 GMT

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


Mime
View raw message