[jira] Commented: (DERBY-4342) SQLSTATE 38000 (NullPointerException) at inner self join and value(x1, x2...)

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4342?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12759144#action_12759144 ] 

Knut Anders Hatlen commented on DERBY-4342:
-------------------------------------------

I've had a look at the INNER JOIN case and compared it with a similar, but working, query where VALUE was replaced by MOD. That is
    select * from xyz t1 inner join xyz t2 on t1.c1 = t2.c1 where value(t1.c1, t1.c2) = 1
vs
    select * from xyz t1 inner join xyz t2 on t1.c1 = t2.c1 where mod(t1.c1, t1.c2) = 1

The node trees for the queries had the exact same structure (except of course that the CoalesceFunctionNode was replaced by a BinaryArithmeticOperatorNode) after the parsing phase and the bind phase. After the optimize phase, there was one difference: The columns under the BinaryArithmeticOperatorNode (for the MOD query) referenced the base table whereas the columns under the CoalesceFunctionNode (for the VALUE query) referenced the result columns from the JoinNode.

At the time when these columns are accessed during execution, no rows have propagated from the base tables to the join results, so the VALUE query gets null instead of a row in getColumnFromRow() and fails with NPE.

The difference between CoalesceFunctionNode and BinaryArithmeticOperatorNode that makes the two queries access the columns differently, is remapColumnReferencesToExpressions(). CFN inherits the method from ValueNode, where it is a no-op. BAON inherits it from BinaryOperatorNode, where remapColumnReferencesToExpressions() is called recursively on the operands.

I tried to add this method to CFN

    public ValueNode remapColumnReferencesToExpressions()
            throws StandardException
    {
        for (int i = 0; i < argumentsList.size(); i++) {
            ValueNode vn = (ValueNode) argumentsList.elementAt(i);
            vn.remapColumnReferencesToExpressions();
        }
        return this;
    }

and then the INNER JOIN did not fail and it returned the expected result.

Unfortunately, the RIGHT OUTER JOIN still raised an assert failure, so either that's a separate problem, or adding the method was not the right fix. (I did try to compare the node trees for VALUE vs MOD in the outer join case too, and there the two queries produced completely different trees.)

> SQLSTATE 38000 (NullPointerException) at inner self join and value(x1, x2...)
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4342
>                 URL: https://issues.apache.org/jira/browse/DERBY-4342
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions: 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
>         Environment: WINXP
>            Reporter: Hubert Garm
>
> create table xyz (c1 int, c2 int);
> insert into xyz values(1,2);
> select * from xyz t1 inner join xyz t2 on t1.c1 = t2.c1 where value(t1.c1, t1.c2) = 1;
> causes the following error:
> FEHLER 38000: Bei der Auswertung eines Ausdrucks wurde die Ausnahme 'java.lang.NullPointerException' ausgeloest.
> FEHLER XJ001: Java-Ausnahme: ': java.lang.NullPointerException'.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.