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] Issue Comment Edited: (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 Fri, 14 Dec 2007 16:29:43 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3253?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12551848
] 

army edited comment on DERBY-3253 at 12/14/07 8:28 AM:
------------------------------------------------------

> Did I follow your explanation correctly? 

Sorry, I think I messed up the explanation slightly.  The order of events is:

  -- Preprocess InListOperatorNode, which has some left operand OP_0, and convert it
    into a "probe predicate", which is a BinaryRelationalOperatorNode.  That probe
    predicate does two things: 1) assumes its left operand from InListOperatorNode,
    i.e. the probe predicate's left operand is OP_0, as well; and 2) stores a pointer
    to the InListOperatorNode so that, if needed, the probe predicate can "revert" back
    to the InListOperatorNode at generation time.  Upon completion of the preprocess
    phase for InListOperatorNode, the new probe predicate and the original InListOperatorNode
    both have the same left operand (OP_0)

  -- Preprocess the subquery node, which involves flattening it.  As part of flattening
    the *probe predicate*'s left operand (*not* the InListOperatorNode's left operand) 
    changes to OP_1; this is because it (the probe predicate) now represents the
    IN operation for the query tree, not the InListOperatorNode. So the InListOperatorNode

    remains the same after flattening--i.e. its left operand is still OP_0.  That
    means the probe predicate and the InListOperatorNode have now different
    left operands.

  -- Optimization completes, we decide that the probe predicate is not useful.

  -- During code generation, we see that the probe predicate is not useful so we "revert"
    back to the original InListOperatorNode by calling "generate" on that node (which we
    stored inside the probe predicate (BinaryRelationalOperatorNode) at the beginning,
    per #2 of the first bullet above).  But since the InListOperatorNode still has the old
    operand OP_0, it generates the wrong column reference.

> If so, it seems like it might be (slightly) cleaner if the logic were
> centralized in the InListOperatorNode itself,

I don't think this is possible--or at least, not straightforward--because once the probe predicate
comes into the picture, it (the probe predicate) becomes the focus of all method calls related
to the IN list--so the only way to keep the InListOperatorNode up to date would be to add
logic in BinaryRelationalOperatorNode that sends all method calls relating to leftOperand
on down to the InListOperatorNode, as well.  Note that doing so would require that the logic
go into BinaryRelationalOperatorNode, not into InListOperatorNode.

That was the way I was leaning when I first started, but a) that would require more
logic in more places in BinaryRelationalOperatorNode to propagate operations
down to the InListOperatorNode, and b) it seemed like the odds of missing some
leftOperand operation could be non-neglible for very compilcated queries (just a
theory, I didn't actually investigate this further).  That said, it seemed like doing a
single "setLeftOperand()" call at generation time was the preferable mechanism.

I am of course open to change if you think this is not the best approach.

Thanks for the feedback!

      was (Author: army):
    > Did I follow your explanation correctly? 

Sorry, I think I messed up the explanation slightly.  The order of events is:

  -- Preprocess InListOperatorNode, which has some left operand OP_0, and convert it
    into a "probe predicate", which is a BinaryRelationalOperatorNode.  That probe
    predicate does two things: 1) assumes its left operand from InListOperatorNode,
    i.e. the probe predicate's left operand is OP_0, as well; and 2) stores a pointer
    to the InListOperatorNode so that, if needed, the probe predicate can "revert" back
    to the InListOperatorNode at generation time.  Upon completion of the preprocess
    phase for InListOperatorNode, the new probe predicate and the original InListOperatorNode
    both have the same left operand.

  -- Preprocess the subquery node, which involves flattening it.  As part of flattening
    the *probe predicate*'s left operand changes to OP_1 because it (the probe predicate)
    now represents the IN operation.  The original InListOperatorNode remains the same,
    though--i.e. it is still OP_0.  So now the probe predicate and the InListOperatorNode
    have different left operands.

  -- Optimization completes, we decide that the probe predicate is not useful.

  -- During code generation, we see that the probe predicate is not useful so we "revert"
    back to the original InListOperatorNode by calling "generate" on that node (which we
    stored inside the probe predicate (BinaryRelationalOperatorNode) at the beginning,
    per #2 of the first bullet above..  But since the InListOperatorNode still has the old
    operand OP_0, it generates the wrong column reference.

> If so, it seems like it might be (slightly) cleaner if the logic were
> centralized in the InListOperatorNode itself,

I don't think this is possible--or at least, not straightforward--because once the probe
predicate comes into the picture, it (the probe predicate) becomes the focus of all
method calls related to the IN list--so the only way to keep the InListOperatorNode up
to date would be to add logic in BinaryRelationalOperatorNode that sends all method
calls relating to leftOperand on down to the InListOperatorNode, as well.  Note that
doing so would require that the logic go into BinaryRelationalOperatorNode, not into
InListOperatorNode.

That was the way I was leaning when I first started, but a) that would require more
logic in more places in BinaryRelationalOperatorNode to propagate operations
down to the InListOperatorNode, and b) it seemed like the odds of missing some
leftOperand operation could be non-neglible for very compilcated queries (just a
theory, I didn't actually investigate this further).  That said, it seemed like doing a
single "setLeftOperand()" call at generation time was the preferable mechanism.

I am of course open to change if you think this is not the best approach.

Thanks for the feedback!
  
> 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
>
>
> 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