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 Fri, 14 Dec 2007 04:01:44 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_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.


Mime
View raw message