Derby
  1. Derby
  2. DERBY-4342

SQLSTATE 38000 (NullPointerException) at inner self join and value(x1, x2...)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 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
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      WINXP
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      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'.

      1. derby-4342-1a.diff
        2 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          I was able to reproduce this on trunk. The interesting part of the stack trace is:
          :
          Caused by: java.lang.NullPointerException
          at org.apache.derby.impl.sql.execute.BaseActivation.getColumnFromRow(BaseActivation.java:1451)
          at org.apache.derby.exe.acf81e0010x0122xf19ex21cdx0000001421800.g0(Unknown Source)
          at org.apache.derby.exe.acf81e0010x0122xf19ex21cdx0000001421800.e1(Unknown Source)
          at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:141)
          at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:268)
          at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(JoinResultSet.java:148)
          at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:248)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
          ... 10 more

          Show
          Dag H. Wanvik added a comment - I was able to reproduce this on trunk. The interesting part of the stack trace is: : Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.execute.BaseActivation.getColumnFromRow(BaseActivation.java:1451) at org.apache.derby.exe.acf81e0010x0122xf19ex21cdx0000001421800.g0(Unknown Source) at org.apache.derby.exe.acf81e0010x0122xf19ex21cdx0000001421800.e1(Unknown Source) at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:141) at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:268) at org.apache.derby.impl.sql.execute.JoinResultSet.openCore(JoinResultSet.java:148) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:248) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235) ... 10 more
          Hide
          Dag H. Wanvik added a comment -

          Marking "repro attached", normal urgency, unmarking "newcomer" (this field means the devs consider it easy to fix).

          Show
          Dag H. Wanvik added a comment - Marking "repro attached", normal urgency, unmarking "newcomer" (this field means the devs consider it easy to fix).
          Hide
          Dag H. Wanvik added a comment -

          Normal join and left outer join raises no error.

          This query variant:

          select * from xyz t1 right outer join xyz t2 on t1.c1 = t2.c1 where value(t1.c1, t1.c2) = 1;

          gives an ASSERT on sane build:

          Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber expected to be >= 0 for T1.C1
          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
          at org.apache.derby.impl.sql.compile.ColumnReference.generateExpression(ColumnReference.java:941)
          at org.apache.derby.impl.sql.compile.CoalesceFunctionNode.generateExpression(CoalesceFunctionNode.java:262)
          at org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(BinaryOperatorNode.java:600)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1521)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)
          at org.apache.derby.impl.sql.compile.JoinNode.getJoinArguments(JoinNode.java:1577)
          at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1555)
          at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1492)
          at org.apache.derby.impl.sql.compile.HalfOuterJoinNode.generate(HalfOuterJoinNode.java:691)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1390)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1390)
          at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341)
          at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)
          at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:606)
          at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347)

          Show
          Dag H. Wanvik added a comment - Normal join and left outer join raises no error. This query variant: select * from xyz t1 right outer join xyz t2 on t1.c1 = t2.c1 where value(t1.c1, t1.c2) = 1; gives an ASSERT on sane build: Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED sourceResultSetNumber expected to be >= 0 for T1.C1 at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162) at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147) at org.apache.derby.impl.sql.compile.ColumnReference.generateExpression(ColumnReference.java:941) at org.apache.derby.impl.sql.compile.CoalesceFunctionNode.generateExpression(CoalesceFunctionNode.java:262) at org.apache.derby.impl.sql.compile.BinaryOperatorNode.generateExpression(BinaryOperatorNode.java:600) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1521) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341) at org.apache.derby.impl.sql.compile.JoinNode.getJoinArguments(JoinNode.java:1577) at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1555) at org.apache.derby.impl.sql.compile.JoinNode.generateCore(JoinNode.java:1492) at org.apache.derby.impl.sql.compile.HalfOuterJoinNode.generate(HalfOuterJoinNode.java:691) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1390) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1390) at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1341) at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109) at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:606) at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347)
          Hide
          Dag H. Wanvik added a comment -

          This one gives the NPE also:
          select * from xyz t1 join xyz t2 on t1.c1 = t2.c1 where value(t1.c1, t1.c2) = 1;

          Show
          Dag H. Wanvik added a comment - This one gives the NPE also: select * from xyz t1 join xyz t2 on t1.c1 = t2.c1 where value(t1.c1, t1.c2) = 1;
          Hide
          Knut Anders Hatlen added a comment -

          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.)

          Show
          Knut Anders Hatlen added a comment - 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.)
          Hide
          Knut Anders Hatlen added a comment -

          As far as I can see, all node types that have arguments or operands override ValueNode.remapColumnReferencesToExpressions() so that it is called recursively on all arguments/operands. I haven't been able to find anything indicating that the method was left out of CFN intentionally. The javadoc comment in ValueNode says that it should remap "all ColumnReferences in this tree", so calling it recursively on the arguments on CFN sounds like the right thing to do.

          I therefore believe that the suggested change in CoalesceFunctionNode is the correct fix for this issue, and the assert failure in the right outer join is probably a different bug. (Note also that a similar NPE with a right outer join, but without VALUE/COALESCE, has been logged as DERBY-4388.)

          The attached patch adds the missing method override to CoalesceFunctionNode, and it also adds a regression test case to CoalesceTest.

          Show
          Knut Anders Hatlen added a comment - As far as I can see, all node types that have arguments or operands override ValueNode.remapColumnReferencesToExpressions() so that it is called recursively on all arguments/operands. I haven't been able to find anything indicating that the method was left out of CFN intentionally. The javadoc comment in ValueNode says that it should remap "all ColumnReferences in this tree", so calling it recursively on the arguments on CFN sounds like the right thing to do. I therefore believe that the suggested change in CoalesceFunctionNode is the correct fix for this issue, and the assert failure in the right outer join is probably a different bug. (Note also that a similar NPE with a right outer join, but without VALUE/COALESCE, has been logged as DERBY-4388 .) The attached patch adds the missing method override to CoalesceFunctionNode, and it also adds a regression test case to CoalesceTest.
          Hide
          Knut Anders Hatlen added a comment -

          All the regression tests ran cleanly. The patch is ready for review.

          Show
          Knut Anders Hatlen added a comment - All the regression tests ran cleanly. The patch is ready for review.
          Hide
          Bryan Pendleton added a comment -

          The patch seems fine to me. I think this was just an oversight in CoalesceFunctionNode. Test case looks appropriate. +1 to commit.

          Show
          Bryan Pendleton added a comment - The patch seems fine to me. I think this was just an oversight in CoalesceFunctionNode. Test case looks appropriate. +1 to commit.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for reviewing the patch, Bryan. Committed revision 819006.

          I will file a separate issue for the assert failure in the right outer join and link it to this issue.

          Show
          Knut Anders Hatlen added a comment - Thanks for reviewing the patch, Bryan. Committed revision 819006. I will file a separate issue for the assert failure in the right outer join and link it to this issue.
          Hide
          Knut Anders Hatlen added a comment -

          For the record, there have also been problems with missing overrides in CoalesceFunctionNode before:

          • DERBY-883 added missing accept() method
          • DERBY-1574 added missing preprocess() and printSubNodes() methods
          Show
          Knut Anders Hatlen added a comment - For the record, there have also been problems with missing overrides in CoalesceFunctionNode before: DERBY-883 added missing accept() method DERBY-1574 added missing preprocess() and printSubNodes() methods
          Hide
          Knut Anders Hatlen added a comment -

          Merged fix to 10.5 (had to resolve a conflict with DERBY-4087 manually). Committed revision 819547.

          Show
          Knut Anders Hatlen added a comment - Merged fix to 10.5 (had to resolve a conflict with DERBY-4087 manually). Committed revision 819547.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Hubert Garm
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development