[
https://issues.apache.org/jira/browse/DERBY-3253?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
A B updated DERBY-3253:
-----------------------
Attachment: d3253_v1.patch
A surprisingly simple repro of this NPE can be created as follows:
create table t1 (i int, vc varchar(10));
insert into t1 values (1, 'one'), (2, 'two'), (3, 'three'), (1, 'un');
select * from t1, (select * from t1) x
where t1.i = x.i and x.vc in ('un', 'trois');
The key here is that the IN list's left operand points to a column from the
subselect, X.VC. As part of DERBY-47, the IN list will be changed into a
BinaryRelationalOperatorNode of the form "X.VC = ?", and that node, which we
call a "probe predicate", will serve to represent the IN list operator
throughout the various phases of optimization.
That said, as part of preprocessing Derby will look at the query and realize
that the sub-select can be flattened. When flattening the subquery, any
references to the subquery's RCL will be remapped to point to the underlying
expression. That means the left operand of the probe predicate "X.VC = ?" will
be changed to point directly to column "VC" of table T1. The code where this
happens is in the "flatten" method of FromSubquery:
/* Remap all ColumnReferences from the outer query to this node.
* (We replace those ColumnReferences with clones of the matching
* expression in the SELECT's RCL.
*/
rcl.remapColumnReferencesToExpressions();
outerPList.remapColumnReferencesToExpressions();
For the example query above, outerPList holds the two predicates "T1.I = X.I"
and "X.VC = ?", so we will attempt to remap the column references in those two
predicates. That brings us to the remapColumnReferencesToExpressions() method
of BinaryOperatorNode, where we have:
public ValueNode remapColumnReferencesToExpressions()
throws StandardException
{
leftOperand = leftOperand.remapColumnReferencesToExpressions();
rightOperand = rightOperand.remapColumnReferencesToExpressions();
return this;
}
Notice how the leftOperand can change here--and in the above query, it *will*
change to point directly to T1 instead of indirectly to the subquery. So now
the probe predicate's left operand is different from the left operand of the
original InListOperatorNode that the probe predicate replaced. That in it
itself is fine, but it causes problems later.
Namely, when it comes time to generate the final tree for the query, we realize
that the probe predicate is not "useful" for probing because it references
"VC", which is the second column in table T1. Since probe predicates are only
useful if they reference the first column in the table, per
"orderUsefulPredicates(...)" of PredicateList.java:
else if (pred.isInListProbePredicate()
&& (indexPosition > 0))
{
/* If the predicate is an IN-list probe predicate
* then we only consider it to be useful if the
* referenced column is the *first* one in the
* index (i.e. if (indexPosition == 0)). Otherwise
* the predicate would be treated as a qualifier
* for store, which could lead to incorrect
* results.
*/
....
the probe predicate is not useful. That in turn means that when it comes time
to generate the IN list operator, we'll "revert" back to the original
InListOperatorNode--i.e. we will generate the InListOperatorNode *instead of*
generating the probe predicate. This is found in the generateExpression()
method of BinaryOperatorNode:
if (ilon != null)
{
ilon.generateExpression(acb, mb);
return;
}
But there's a problem here: as mentioned above, ilon (the InListOperatorNode)
still has a left operand that points to a column from the *subquery*. Since we
flattened the subquery out, that left operand is no longer valid--and that
ultimately causes an execution time NPE because we try to apply the IN list
restriction to a column from a subquery that does not exist.
I tried a one-line fix to this code that seems to have resolved the issue:
if (ilon != null)
{
ilon.setLeftOperand(this.leftOperand); // Added this line
ilon.generateExpression(acb, mb);
return;
}
(with appropriate code comments, of course).
This has the effect of making sure that when we "revert" back to the original
InListOperatorNode generation, we'll still generate the correct
leftOperand--i.e. the left operand as it exists in the "probe predicate" upon
completion of optimization.
I'm attaching this small fix as d3253_v1.patch. I have yet to run the
regression tests (they are running now), but I thought I'd post my findings for
early review in the interim...
> 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.
