Derby
  1. Derby
  2. DERBY-4420

NullPointerException with INSERT INTO ... from EXCEPT/INTERSECT

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0, 10.2.1.6, 10.3.1.4, 10.4.1.3, 10.5.1.1, 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

      The sequence of statements below give a NullPointerException. The statements are very similar to the ones in DERBY-4419, but this is a separate bug since the stack traces are different, and this bug can be seen all the way back to 10.1.1.0, whereas DERBY-4419 was a regression in 10.3. (On 10.0.2.1, a syntax error is raised instead of the NPE.)

      ij> create table t1(x int);
      0 rows inserted/updated/deleted
      ij> insert into t1 values 1,2;
      2 rows inserted/updated/deleted
      ij> create table t2(x int);
      0 rows inserted/updated/deleted
      ij> insert into t2 values 2,3;
      2 rows inserted/updated/deleted
      ij> create table t3(x int, y int generated always as identity);
      0 rows inserted/updated/deleted
      ij> insert into t3 select * from t1 except select * from t2;
      ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

      Same error if INTERSECT is used instead of EXCEPT.

      1. npe.sql
        0.3 kB
        Knut Anders Hatlen
      2. d4420-1a.diff
        17 kB
        Knut Anders Hatlen
      3. d4420-1a.stat
        0.2 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          Stack trace:

          java.lang.NullPointerException
          at org.apache.derby.impl.sql.compile.ResultSetNode.setTableConstructorTypes(ResultSetNode.java:329)
          at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(InsertNode.java:329)
          at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:324)
          at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:90)
          at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:828)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555)
          at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)

          Show
          Knut Anders Hatlen added a comment - Stack trace: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.ResultSetNode.setTableConstructorTypes(ResultSetNode.java:329) at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(InsertNode.java:329) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:324) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:90) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:828) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555) at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:329)
          Hide
          Knut Anders Hatlen added a comment -

          Attached is a script that reproduces the NPE.

          Show
          Knut Anders Hatlen added a comment - Attached is a script that reproduces the NPE.
          Hide
          Knut Anders Hatlen added a comment -

          Note that a NullPointerException with the exact same stack trace is also seen if T3 is defined as
          create table t3(x int, y int default 4);
          or
          create table t3(x int, y generated always as (2*x));

          Show
          Knut Anders Hatlen added a comment - Note that a NullPointerException with the exact same stack trace is also seen if T3 is defined as create table t3(x int, y int default 4); or create table t3(x int, y generated always as (2*x));
          Hide
          Knut Anders Hatlen added a comment -

          Actually, this has nothing to do with generated columns, default values or identity columns:

          ij> create table t1(x int);
          0 rows inserted/updated/deleted
          ij> create table t2(x int);
          0 rows inserted/updated/deleted
          ij> create table t3(x int);
          0 rows inserted/updated/deleted
          ij> insert into t3 select * from t1 except select * from t2;
          ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

          Show
          Knut Anders Hatlen added a comment - Actually, this has nothing to do with generated columns, default values or identity columns: ij> create table t1(x int); 0 rows inserted/updated/deleted ij> create table t2(x int); 0 rows inserted/updated/deleted ij> create table t3(x int); 0 rows inserted/updated/deleted ij> insert into t3 select * from t1 except select * from t2; ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
          Hide
          Knut Anders Hatlen added a comment -

          Removed "generated columns" from the bug summary, since the NPE can be reproduced without such columns.

          Show
          Knut Anders Hatlen added a comment - Removed "generated columns" from the bug summary, since the NPE can be reproduced without such columns.
          Hide
          Knut Anders Hatlen added a comment -

          The NPE is raised by this code in ResultSetNode.setTableConstructorTypes() because re is null:

          ResultColumn rc = (ResultColumn) resultColumns.elementAt(index);

          ValueNode re = rc.getExpression();

          if (re.requiresTypeFromContext())

          I looked at it in the debugger, and it turned out that rc was an instance of AllResultColumn, which represents the "" in the select list. So the problem seems to be related to the "" not having been expanded to the actual RC at that time.

          The insert statement works if the "*" in the first operand of the except operator is replaced with the actual column name:

          ij> insert into t3 select x from t1 except select * from t2;
          1 row inserted/updated/deleted

          Show
          Knut Anders Hatlen added a comment - The NPE is raised by this code in ResultSetNode.setTableConstructorTypes() because re is null: ResultColumn rc = (ResultColumn) resultColumns.elementAt(index); ValueNode re = rc.getExpression(); if (re.requiresTypeFromContext()) I looked at it in the debugger, and it turned out that rc was an instance of AllResultColumn, which represents the " " in the select list. So the problem seems to be related to the " " not having been expanded to the actual RC at that time. The insert statement works if the "*" in the first operand of the except operator is replaced with the actual column name: ij> insert into t3 select x from t1 except select * from t2; 1 row inserted/updated/deleted
          Hide
          Knut Anders Hatlen added a comment -

          It looks like setTableConstructorTypes() is only meant for table constructors (values clauses). UnionNode overrides it and makes it a no-op unless the union really is a values clause that has been rewritten to a union. IntersectOrExceptNode does not override it. I tried implementing an empty override in IntersectOrExceptNode, and that made the NPE go away. Probably, the code in ResultSetNode.setTableConstructorTypes() should only be executed if the node is a RowResultSetNode, so an alternative solution would be to move the method to RowResultSetNode and make the method in ResultSetNode a no-op.

          The change fixes the NPE, and the query with no generated columns, identity columns or default values works correctly. The queries that insert into a table with a column that's auto-generated somehow, this error is raised instead:

          ERROR 42X77: Column position '2' is out of range for the query expression.

          So either it's the wrong fix, or there's more than one bug.

          Show
          Knut Anders Hatlen added a comment - It looks like setTableConstructorTypes() is only meant for table constructors (values clauses). UnionNode overrides it and makes it a no-op unless the union really is a values clause that has been rewritten to a union. IntersectOrExceptNode does not override it. I tried implementing an empty override in IntersectOrExceptNode, and that made the NPE go away. Probably, the code in ResultSetNode.setTableConstructorTypes() should only be executed if the node is a RowResultSetNode, so an alternative solution would be to move the method to RowResultSetNode and make the method in ResultSetNode a no-op. The change fixes the NPE, and the query with no generated columns, identity columns or default values works correctly. The queries that insert into a table with a column that's auto-generated somehow, this error is raised instead: ERROR 42X77: Column position '2' is out of range for the query expression. So either it's the wrong fix, or there's more than one bug.
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch copies ResultSetNode.setTableConstructorTypes() to RowResultSetNode and makes the method in RSN a no-op. According to the comments in RSN, the code is only supposed to be executed if the node is a RowResultSetNode, so it sounds like a better place for the code. A test case is also added in InsertTest.

          Now the insert statements that take all the column values from the select statement work as expected. The ones that only insert into a subset of the columns and take the rest of the values from the column defaults, fail with the "column position out of range" message mentioned in the previous comment. I'm assuming this is a different bug, and I intend to file a separate issue if the proposed fix is committed.

          All the regression tests ran cleanly.

          Show
          Knut Anders Hatlen added a comment - The attached patch copies ResultSetNode.setTableConstructorTypes() to RowResultSetNode and makes the method in RSN a no-op. According to the comments in RSN, the code is only supposed to be executed if the node is a RowResultSetNode, so it sounds like a better place for the code. A test case is also added in InsertTest. Now the insert statements that take all the column values from the select statement work as expected. The ones that only insert into a subset of the columns and take the rest of the values from the column defaults, fail with the "column position out of range" message mentioned in the previous comment. I'm assuming this is a different bug, and I intend to file a separate issue if the proposed fix is committed. All the regression tests ran cleanly.
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 832379.

          I'll file a new issue for the column position out of range error.

          Show
          Knut Anders Hatlen added a comment - Committed revision 832379. I'll file a new issue for the column position out of range error.
          Hide
          Knut Anders Hatlen added a comment -

          Logged the remaining problems as DERBY-4433. Marking this issue as resolved.

          Show
          Knut Anders Hatlen added a comment - Logged the remaining problems as DERBY-4433 . Marking this issue as resolved.
          Hide
          Knut Anders Hatlen added a comment -

          Merged fix to 10.5 with revision 884616.

          Show
          Knut Anders Hatlen added a comment - Merged fix to 10.5 with revision 884616.
          Hide
          Knut Anders Hatlen added a comment -

          Verified on trunk. Closing.

          Show
          Knut Anders Hatlen added a comment - Verified on trunk. Closing.

            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