Derby
  1. Derby
  2. DERBY-2218

Null Pointer Exception when an untyped NULL subquery ("values null") appears outside of the FROM list in a SELECT query.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.1.3.2, 10.2.2.1, 10.3.1.4
    • Fix Version/s: 10.2.2.1, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None

      Description

      If a SELECT query contains a subquery which includes an untyped NULL value at any place other than in the FROM list, Derby will throw an NPE at bind time.

      ij> create table t1 (i int);
      0 rows inserted/updated/deleted

      – If the untyped NULL is in the FROM list, a reasonable error is thrown.

      ij> select * from (values null) x;
      ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

      ij> select * from (select * from t1, (values null) x )y;
      ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

      – But if it appears anywhere else, the result is an NPE:

      – IN-list
      ij> select * from t1 where i in (1, 2, (values null));
      ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

      – where clause
      select * from t1 where (values null);

      – order by clause
      select * from t1 order by (values null);

      – result column
      select (values null) from t1;

      – group by clause (only works in 10.2 and later)
      select * from t1 group by (values null);

      – having clause
      select * from t1 group by i having (values null);

      Stack trace (from 10.2.2) is:

      java.lang.NullPointerException
      at org.apache.derby.impl.sql.compile.SubqueryNode.setDataTypeServices(SubqueryNode.java:2289)
      at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(SubqueryNode.java:529)
      at org.apache.derby.impl.sql.compile.ValueNodeList.bindExpression(ValueNodeList.java:130)
      at org.apache.derby.impl.sql.compile.BinaryListOperatorNode.bindExpression(BinaryListOperatorNode.java:161)
      at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:540)
      at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:249)
      at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:162)
      at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:253)
      at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:345)
      at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:119)

      1. derby2218-trunk-stat01.txt
        0.3 kB
        Yip Ng
      2. derby2218-trunk-diff01.txt
        2 kB
        Yip Ng
      3. derby2218-trunk-stat02.txt
        0.3 kB
        Yip Ng
      4. derby2218-trunk-diff02.txt
        4 kB
        Yip Ng
      5. derby2218-trunk-stat03.txt
        0.3 kB
        Yip Ng
      6. derby2218-trunk-diff03.txt
        3 kB
        Yip Ng
      7. derby2218-10.2-stat01.txt
        0.5 kB
        Yip Ng
      8. derby2218-10.2-diff01.txt
        6 kB
        Yip Ng

        Activity

        A B created issue -
        Yip Ng made changes -
        Field Original Value New Value
        Affects Version/s 10.1.3.2 [ 12311972 ]
        Yip Ng made changes -
        Assignee Yip Ng [ yipng ]
        Yip Ng made changes -
        Attachment derby2218-trunk-stat01.txt [ 12348426 ]
        Attachment derby2218-trunk-diff01.txt [ 12348427 ]
        Yip Ng made changes -
        Derby Info [Patch Available]
        Yip Ng made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Yip Ng made changes -
        Derby Info [Patch Available]
        Yip Ng made changes -
        Attachment derby2218-trunk-stat02.txt [ 12348683 ]
        Attachment derby2218-trunk-diff02.txt [ 12348684 ]
        Yip Ng made changes -
        Derby Info [Patch Available]
        A B made changes -
        Summary Null Pointer Exception when an IN list contains an untyped NULL subquery ("values null"). Null Pointer Exception when an untyped NULL subquery ("values null") appears outside of the FROM list in a SELECT query.
        Description If a query specifies an IN list that contains a subquery which returns an untyped NULL value, Derby will throw an NPE at bind time.

        ij> create table t1 (i int);
        0 rows inserted/updated/deleted
        ij> select * from t1 where i in (1, 2, (values null));
        ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

        I verified the error against the latest 10.2 and 10.3 trunks; it could very well exist in earlier versions, too, but I haven't checked.

        Stack trace (from 10.2.2) is:

        java.lang.NullPointerException
        at org.apache.derby.impl.sql.compile.SubqueryNode.setDataTypeServices(SubqueryNode.java:2289)
        at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(SubqueryNode.java:529)
        at org.apache.derby.impl.sql.compile.ValueNodeList.bindExpression(ValueNodeList.java:130)
        at org.apache.derby.impl.sql.compile.BinaryListOperatorNode.bindExpression(BinaryListOperatorNode.java:161)
        at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:540)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:249)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:162)
        at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:253)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:345)
        at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:119)
        If a SELECT query contains a subquery which includes an untyped NULL value at any place other than in the FROM list, Derby will throw an NPE at bind time.

        ij> create table t1 (i int);
        0 rows inserted/updated/deleted

        -- If the untyped NULL is in the FROM list, a reasonable error is thrown.

        ij> select * from (values null) x;
        ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

        ij> select * from (select * from t1, (values null) x )y;
        ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

        -- But if it appears anywhere else, the result is an NPE:

        -- IN-list
        ij> select * from t1 where i in (1, 2, (values null));
        ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

        -- where clause
        select * from t1 where (values null);

        -- order by clause
        select * from t1 order by (values null);

        -- result column
        select (values null) from t1;

        -- group by clause (only works in 10.2 and later)
        select * from t1 group by (values null);

        -- having clause
        select * from t1 group by i having (values null);

        Stack trace (from 10.2.2) is:

        java.lang.NullPointerException
        at org.apache.derby.impl.sql.compile.SubqueryNode.setDataTypeServices(SubqueryNode.java:2289)
        at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(SubqueryNode.java:529)
        at org.apache.derby.impl.sql.compile.ValueNodeList.bindExpression(ValueNodeList.java:130)
        at org.apache.derby.impl.sql.compile.BinaryListOperatorNode.bindExpression(BinaryListOperatorNode.java:161)
        at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:540)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:249)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:162)
        at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java:253)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:345)
        at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:119)
        A B made changes -
        Derby Info [Patch Available]
        Yip Ng made changes -
        Attachment derby2218-trunk-diff03.txt [ 12349304 ]
        Attachment derby2218-trunk-stat03.txt [ 12349303 ]
        Yip Ng made changes -
        Derby Info [Patch Available]
        A B made changes -
        Resolution Fixed [ 1 ]
        Status In Progress [ 3 ] Resolved [ 5 ]
        Derby Info [Patch Available]
        A B made changes -
        Comment [ Thank you for the follow-up patch, Yip. I ran lang/valuesclause and lang/subquery as a sanity check and then committed ...diff03.txt with svn # 498689:
        ]
        A B made changes -
        Status Resolved [ 5 ] Reopened [ 4 ]
        Resolution Fixed [ 1 ]
        A B made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Fix Version/s 10.3.0.0 [ 12310800 ]
        Resolution Fixed [ 1 ]
        Yip Ng made changes -
        Attachment derby2218-10.2-diff01.txt [ 12351744 ]
        Attachment derby2218-10.2-stat01.txt [ 12351743 ]
        Yip Ng made changes -
        Derby Info [Patch Available]
        A B made changes -
        Fix Version/s 10.2.2.1 [ 12312251 ]
        Derby Info [Patch Available]
        Fix Version/s 10.2.3.0 [ 12312215 ]
        A B made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Dag H. Wanvik made changes -
        Affects Version/s 10.2.3.0 [ 12312215 ]
        Fix Version/s 10.2.3.0 [ 12312215 ]
        Gavin made changes -
        Workflow jira [ 12393686 ] Default workflow, editable Closed status [ 12800709 ]

          People

          • Assignee:
            Yip Ng
            Reporter:
            A B
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development