[jira] Created: (DERBY-4439) NullPointerException at bind time when selecting from VALUES NULL

NullPointerException at bind time when selecting from VALUES NULL
-----------------------------------------------------------------

                 Key: DERBY-4439
                 URL: https://issues.apache.org/jira/browse/DERBY-4439
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.5.3.0, 10.6.0.0
            Reporter: Knut Anders Hatlen
            Priority: Minor


I see a NullPointerException when I try the following query:

ij> select x*2 from (values null) v(x);
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

It should fail gracefully instead, like this similar query:

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

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

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

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

java.lang.NullPointerException
        at org.apache.derby.impl.sql.compile.BinaryOperatorNode.genSQLJavaSQLTree(BinaryOperatorNode.java:412)
        at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(BinaryOperatorNode.java:329)
        at org.apache.derby.impl.sql.compile.BinaryArithmeticOperatorNode.bindExpression(BinaryArithmeticOperatorNode.java:129)
        at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(ResultColumn.java:605)
        at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:693)
        at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:552)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:227)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:140)
        at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:249)
        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)

> NullPointerException at bind time when selecting from VALUES NULL
> -----------------------------------------------------------------
>
>                 Key: DERBY-4439
>                 URL: https://issues.apache.org/jira/browse/DERBY-4439
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>
> I see a NullPointerException when I try the following query:
> ij> select x*2 from (values null) v(x);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> It should fail gracefully instead, like this similar query:
> ij> select x from (values null) v(x);
> ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

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

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

Mamta A. Satoor commented on DERBY-4439:
----------------------------------------

Looks similar to DERBY-4365. I am looking at this one.

> NullPointerException at bind time when selecting from VALUES NULL
> -----------------------------------------------------------------
>
>                 Key: DERBY-4439
>                 URL: https://issues.apache.org/jira/browse/DERBY-4439
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>
> I see a NullPointerException when I try the following query:
> ij> select x*2 from (values null) v(x);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> It should fail gracefully instead, like this similar query:
> ij> select x from (values null) v(x);
> ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

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

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

Mamta A. Satoor commented on DERBY-4439:
----------------------------------------

I looked through the code and experimented with some changes. Basically, during the bind phase, CursorNode does binding in following order
                        super.bind(dataDictionary);

                        // bind the query expression
                        resultSet.bindResultColumns(fromList);

                        // this rejects any untyped nulls in the select list
                        // pass in null to indicate that we don't have any
                        // types for this node
                        resultSet.bindUntypedNullsToResultColumns(null);
In other words, untyped nulls are bound at the end.

In case of our query, where we are using a binary operator node, while we are going through the resultSet.bindResultColumns(fromList) code in CursorNode, we call BinaryOperatorNode.bindExpression() and towards the end of this method, there is following call
                return genSQLJavaSQLTree();
This piece of code assumes that everything is bound by now but since call to untyped nulls has not been made, we run into NPE.

I tried putting in call to binding untyped nulls in FromSubquery.bindExpression and that fixed the query in question but that is not the right thing to do exactly. For following query, with my changes, we end up throwing exception that Null is only allowed in a VALUES clause within an INSERT statement.
create table u (c1 integer);
insert into u select * from (values null) as X;
We need to be able to differentiate the fact that we are dealing with a CursorNode at the top and hence only for that case, we should try to bind untyped nulls. I will work on this more. If anyone has any suggestions on a fix, please share them here

> NullPointerException at bind time when selecting from VALUES NULL
> -----------------------------------------------------------------
>
>                 Key: DERBY-4439
>                 URL: https://issues.apache.org/jira/browse/DERBY-4439
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>
> I see a NullPointerException when I try the following query:
> ij> select x*2 from (values null) v(x);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> It should fail gracefully instead, like this similar query:
> ij> select x from (values null) v(x);
> ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

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

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

Mamta A. Satoor commented on DERBY-4439:
----------------------------------------

Another thing that can be pursued is to see if genSQLJavaSQLTree() can be called after all the binding is finished(including the untyped nulls).

> NullPointerException at bind time when selecting from VALUES NULL
> -----------------------------------------------------------------
>
>                 Key: DERBY-4439
>                 URL: https://issues.apache.org/jira/browse/DERBY-4439
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>
> I see a NullPointerException when I try the following query:
> ij> select x*2 from (values null) v(x);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> It should fail gracefully instead, like this similar query:
> ij> select x from (values null) v(x);
> ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

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

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

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

I'm not sure if "insert into u select * from (values null) as X" is supposed to be accepted.

NULL is an <implicitly typed value specification> (see SQL:2003, part 2, 6.5 <contextually typed value specification>), which can be found in a <contextually typed value specification>, in a <cast specification>, or in a <default clause>.

In "insert into u select * from (values null) as X", the "values null" part is a <table value constructor>, which as far as I can see does not accept a <contextually typed value specification>. In "insert into u values null", on the other hand, it's a <contextually typed table value constructor>, which does accept contextually typed values.

> NullPointerException at bind time when selecting from VALUES NULL
> -----------------------------------------------------------------
>
>                 Key: DERBY-4439
>                 URL: https://issues.apache.org/jira/browse/DERBY-4439
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>
> I see a NullPointerException when I try the following query:
> ij> select x*2 from (values null) v(x);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> It should fail gracefully instead, like this similar query:
> ij> select x from (values null) v(x);
> ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

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

     [ https://issues.apache.org/jira/browse/DERBY-4439?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor reassigned DERBY-4439:
--------------------------------------

    Assignee: Mamta A. Satoor

> NullPointerException at bind time when selecting from VALUES NULL
> -----------------------------------------------------------------
>
>                 Key: DERBY-4439
>                 URL: https://issues.apache.org/jira/browse/DERBY-4439
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>
> I see a NullPointerException when I try the following query:
> ij> select x*2 from (values null) v(x);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> It should fail gracefully instead, like this similar query:
> ij> select x from (values null) v(x);
> ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

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

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

Dag H. Wanvik commented on DERBY-4439:
--------------------------------------

That's how I read it too. I think we should tighten this behavior and issue a release note.

> NullPointerException at bind time when selecting from VALUES NULL
> -----------------------------------------------------------------
>
>                 Key: DERBY-4439
>                 URL: https://issues.apache.org/jira/browse/DERBY-4439
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>            Priority: Minor
>
> I see a NullPointerException when I try the following query:
> ij> select x*2 from (values null) v(x);
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
> It should fail gracefully instead, like this similar query:
> ij> select x from (values null) v(x);
> ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

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