[jira] Created: (DERBY-4365) NullPointerException when preparing LEFT OUTER JOIN between VALUES statements

NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
-----------------------------------------------------------------------------

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


ij version 10.5
ij> connect 'jdbc:derby:db;create=true';
ij> select a.* from (values ('a'),('b'),(null)) a
    left outer join (values ('c'),('d')) b on 1=1;
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773394#action_12773394 ]

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

I think the culprit here is the null value in values clause. A bit smaller repro is
select a.* from (values (null)) a
    left outer join (values ('a')) b on 1=1;


I tried without the left outer join
select a.* from (values (null)) a;
The above query actually gave an error
ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.

Maybe same error has to be thrown for the left outer join case since according to the above error message, Null should not be allowed for our query since it is not an INSERT statement.

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

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

    Assignee: Mamta A. Satoor

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773408#action_12773408 ]

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

I see that we are not doing bindUntypedNullsToResultColumns() for the resultsets involved in the join clause. I will look further into it.

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773669#action_12773669 ]

Bryan Pendleton commented on DERBY-4365:
----------------------------------------

According to my (ancient) copy of Date & Darwen's _A Guide to the SQL Standard_:

  DEFAULT and NULL are permitted only if the row constructor is being used to
  specify the source, or part of the source, for an INSERT statement.

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773725#action_12773725 ]

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

Thanks, Bryan, for verifying what the SQL spec says. So, it seems that we should throw an exception for the following query
select a.* from (values ('a'),('b'),(null)) a
    left outer join (values ('c'),('d')) b on 1=1;

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12773825#action_12773825 ]

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

That's my understanding too. It should be OK to specify NULL if it's wrapped in a CAST, though. So this query should still be accepted:

ij> select a.* from (values ('a'),('b'),(cast(null as char(1)))) a left outer join (values ('c'),('d')) b on 1=1;
1  
----
a  
a  
b  
b  
NULL
NULL

6 rows selected

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4365:
-----------------------------------

    Attachment: DERBY4365_NULLinValues_stat_patch1.txt
                DERBY4365_NULLinValues_diff_patch1.txt

I have a patch which I am ready to commit if noone has any objection to it. Basically, for some reason, for JOIN, we were doing a no-op in the method bindUntypedNullsToResultColumns(ResultColumnList) rather than checking for untyped nulls for the left and right resultsets. I have made changes so that check is made. I have added couple more tests (including the one provided by Knut, where a CAST of NULL values should work fine). I will plan on committing the patch tomorrow if there is no feedback. The derbyall and junit suites ran fine with the exception of known intermittent (DERBY-3757) 'ASSERT FAILED transaction table has null entry when running new StressMultiTest

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>         Attachments: DERBY4365_NULLinValues_diff_patch1.txt, DERBY4365_NULLinValues_stat_patch1.txt
>
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12774250#action_12774250 ]

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

Good catch, Mamta! The fix looks correct to me. +1 to commit.

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>         Attachments: DERBY4365_NULLinValues_diff_patch1.txt, DERBY4365_NULLinValues_stat_patch1.txt
>
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4365:
-----------------------------------

    Fix Version/s: 10.6.0.0

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>             Fix For: 10.6.0.0
>
>         Attachments: DERBY4365_NULLinValues_diff_patch1.txt, DERBY4365_NULLinValues_stat_patch1.txt
>
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12774306#action_12774306 ]

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

Knut, thanks for reviewing the patch. I have committed into trunk using revision 833430. The commit comments were as follows

DERBY-4365

For JOIN, we were doing a no-op in the method bindUntypedNullsToResultColumns(ResultColumnList) rather than checking for untyped nulls for the left and right resultsets. The changes through this commit now checks for untyped nulls for joins. Added couple more tests (including where a CAST of NULL values should work fine). The derbyall and junit suites ran fine with the exception of known intermittent (DERBY-3757) 'ASSERT FAILED transaction table has null entry when running new StressMultiTest

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>         Attachments: DERBY4365_NULLinValues_diff_patch1.txt, DERBY4365_NULLinValues_stat_patch1.txt
>
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor resolved DERBY-4365.
------------------------------------

       Resolution: Fixed
    Fix Version/s: 10.5.3.1

Backported the change into 10.5 codeline. I ran derbyall succesfully. Junit suite (specifically upgrade test) has been running into connectivity issues which might be local to my machine configuration. I will keep an eye open to see if this commit causes any failure on 10.5 codeline but the change has been running fine on trunk for sometime now.

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>             Fix For: 10.5.3.1, 10.6.0.0
>
>         Attachments: DERBY4365_NULLinValues_diff_patch1.txt, DERBY4365_NULLinValues_stat_patch1.txt
>
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

--
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-4365?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12777779#action_12777779 ]

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

I rerean the junit tests on 10.5 with my changes and this time there were no connection related failures for upgrade tests. Everything ran fine.

> NullPointerException when preparing LEFT OUTER JOIN between VALUES statements
> -----------------------------------------------------------------------------
>
>                 Key: DERBY-4365
>                 URL: https://issues.apache.org/jira/browse/DERBY-4365
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>             Fix For: 10.5.3.1, 10.6.0.0
>
>         Attachments: DERBY4365_NULLinValues_diff_patch1.txt, DERBY4365_NULLinValues_stat_patch1.txt
>
>
> ij version 10.5
> ij> connect 'jdbc:derby:db;create=true';
> ij> select a.* from (values ('a'),('b'),(null)) a
>     left outer join (values ('c'),('d')) b on 1=1;
> ERROR XJ001: Java exception: ': java.lang.NullPointerException'.

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