Derby
  1. Derby
  2. DERBY-4388

NullPointerException in RIGHT JOIN with NOT BETWEEN

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.3.0, 10.6.1.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Repro attached

      Description

      ij> create table t1(x int);
      0 rows inserted/updated/deleted
      ij> create table t2(x int);
      0 rows inserted/updated/deleted
      ij> insert into t2 values (1);
      1 row inserted/updated/deleted
      ij> select * from t1 right join t2 on t1.x = t2.x where t1.x not between 0 and 4;
      X |X
      -----------------------
      ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
      ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

      1. d4388-1a.diff
        3 kB
        Knut Anders Hatlen
      2. d4388-1a.stat
        0.2 kB
        Knut Anders Hatlen
      3. d4388-1b.diff
        3 kB
        Knut Anders Hatlen
      4. derby-4388-10.5.diff
        3 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Committed to 10.5 with revision 825450.

          Show
          Knut Anders Hatlen added a comment - Committed to 10.5 with revision 825450.
          Hide
          Knut Anders Hatlen added a comment -

          The regression test could not be merged to 10.5 because InbetweenTest does not exist on the 10.5 branch. derby-4388-10.5.diff contains the fix and adds a regression test case in the lang/inbetween.sql test, which is present in 10.5. The test case raises NPE without the fix.

          Show
          Knut Anders Hatlen added a comment - The regression test could not be merged to 10.5 because InbetweenTest does not exist on the 10.5 branch. derby-4388-10.5.diff contains the fix and adds a regression test case in the lang/inbetween.sql test, which is present in 10.5. The test case raises NPE without the fix.
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 822289.

          I'll leave the issue open until the fix has been merged to 10.5.

          Show
          Knut Anders Hatlen added a comment - Committed revision 822289. I'll leave the issue open until the fix has been merged to 10.5.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for looking at the patch, Dag. I think you're right about the first clone not being necessary. I've prepared a new patch (1b) which only clones the left operand in the second comparison. I'm rerunning the regression tests and will commit if they pass.

          Show
          Knut Anders Hatlen added a comment - Thanks for looking at the patch, Dag. I think you're right about the first clone not being necessary. I've prepared a new patch (1b) which only clones the left operand in the second comparison. I'm rerunning the regression tests and will commit if they pass.
          Hide
          Dag H. Wanvik added a comment -

          Thanks for this patch, Knut. I read the logic of InListOperatorNode.eliminateNots and that lead me to believe that only the
          second clone is necessary (the point being that the two "new" comparison operations not share the same column reference. I tried this
          by substituting leftOperand for leftClone in the first comparison (leftO < rightOList.elementAt(0)), the test ran ok.
          but it may be safer to clone both. Just thought I should mention it.
          Without the patch, the test fails, withit it works, so an good improvement, I think.
          +1

          Show
          Dag H. Wanvik added a comment - Thanks for this patch, Knut. I read the logic of InListOperatorNode.eliminateNots and that lead me to believe that only the second clone is necessary (the point being that the two "new" comparison operations not share the same column reference. I tried this by substituting leftOperand for leftClone in the first comparison (leftO < rightOList.elementAt(0)), the test ran ok. but it may be safer to clone both. Just thought I should mention it. Without the patch, the test fails, withit it works, so an good improvement, I think. +1
          Hide
          Knut Anders Hatlen added a comment -

          All the regression tests ran cleanly.

          Show
          Knut Anders Hatlen added a comment - All the regression tests ran cleanly.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching a patch which changes BetweenOperatorNode.eliminateNots() to follow the same pattern as InListOperatorNode.eliminateNots() and adds a test case to InbetweenTest. No tests except InbetweenTest have been run yet, but I will start a full regression test run and report back.

          Show
          Knut Anders Hatlen added a comment - Attaching a patch which changes BetweenOperatorNode.eliminateNots() to follow the same pattern as InListOperatorNode.eliminateNots() and adds a test case to InbetweenTest. No tests except InbetweenTest have been run yet, but I will start a full regression test run and report back.
          Hide
          Knut Anders Hatlen added a comment -

          BetweenOperatorNode.eliminateNots() rewrites T1.X NOT BETWEEN 0 AND 4 to T1.X < 0 OR T1.X > 4. I compared the optimized tree for a NOT BETWEEN query with a query manually rewritten to the equivalent less-than/greater-than form and saw that the structure of the column references was different.

          Looking at how other nodes implement eliminateNots(), I found that InListOperatorNode is similar to BetweenOperatorNode, except that it always clones the left operand if it is a ColumnReference. A comment told that the cloning was necessary because the reference could be remapped during optimization, and then the different nodes would need a separate instance. Changing BetweenOperatorNode to clone ColumnReferences appears to fix the NPE.

          Show
          Knut Anders Hatlen added a comment - BetweenOperatorNode.eliminateNots() rewrites T1.X NOT BETWEEN 0 AND 4 to T1.X < 0 OR T1.X > 4. I compared the optimized tree for a NOT BETWEEN query with a query manually rewritten to the equivalent less-than/greater-than form and saw that the structure of the column references was different. Looking at how other nodes implement eliminateNots(), I found that InListOperatorNode is similar to BetweenOperatorNode, except that it always clones the left operand if it is a ColumnReference. A comment told that the cloning was necessary because the reference could be remapped during optimization, and then the different nodes would need a separate instance. Changing BetweenOperatorNode to clone ColumnReferences appears to fix the NPE.
          Hide
          Knut Anders Hatlen added a comment -

          The query also fails if a left join is used:

          ij> select * from t2 left join t1 on t1.x=t2.x where t1.x not between 0 and 4;
          X |X
          -----------------------
          ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
          ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

          Show
          Knut Anders Hatlen added a comment - The query also fails if a left join is used: ij> select * from t2 left join t1 on t1.x=t2.x where t1.x not between 0 and 4; X |X ----------------------- ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression. ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
          Hide
          Knut Anders Hatlen added a comment -

          The stack trace is similar to DERBY-4342, but I'm logging it as a separate issue since this query doesn't use VALUE or COALESCE. Also, DERBY-4342 throws NPE during Statement.execute(), whereas the NPE in this issue is thrown during ResultSet.next().

          Here's the relevant part of the stack trace:

          java.lang.NullPointerException
          at org.apache.derby.impl.sql.execute.BaseActivation.getColumnFromRow(BaseActivation.java:1451)
          at org.apache.derby.exe.ac0b5b0099x0123xf14ex9cd1x000003c495d81.e4(Unknown Source)
          at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:147)
          at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:268)
          at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:471)
          at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:427)
          at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:371)

          Show
          Knut Anders Hatlen added a comment - The stack trace is similar to DERBY-4342 , but I'm logging it as a separate issue since this query doesn't use VALUE or COALESCE. Also, DERBY-4342 throws NPE during Statement.execute(), whereas the NPE in this issue is thrown during ResultSet.next(). Here's the relevant part of the stack trace: java.lang.NullPointerException at org.apache.derby.impl.sql.execute.BaseActivation.getColumnFromRow(BaseActivation.java:1451) at org.apache.derby.exe.ac0b5b0099x0123xf14ex9cd1x000003c495d81.e4(Unknown Source) at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:147) at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:268) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:471) at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:427) at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:371)

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development