[
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:30 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 there is a better 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 (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!
> 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.
