Derby
  1. Derby
  2. DERBY-4365

NullPointerException when preparing LEFT OUTER JOIN between VALUES statements

    Details

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

      Description

      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'.

        Activity

        Hide
        Mamta A. Satoor added a comment -

        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.

        Show
        Mamta A. Satoor added a comment - 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.
        Hide
        Mamta A. Satoor added a comment -

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

        Show
        Mamta A. Satoor added a comment - I see that we are not doing bindUntypedNullsToResultColumns() for the resultsets involved in the join clause. I will look further into it.
        Hide
        Bryan Pendleton added a comment -

        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.

        Show
        Bryan Pendleton added a comment - 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.
        Hide
        Mamta A. Satoor added a comment -

        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;

        Show
        Mamta A. Satoor added a comment - 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;
        Hide
        Knut Anders Hatlen added a comment -

        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

        Show
        Knut Anders Hatlen added a comment - 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
        Hide
        Mamta A. Satoor added a comment -

        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

        Show
        Mamta A. Satoor added a comment - 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
        Hide
        Knut Anders Hatlen added a comment -

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

        Show
        Knut Anders Hatlen added a comment - Good catch, Mamta! The fix looks correct to me. +1 to commit.
        Hide
        Mamta A. Satoor added a comment -

        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

        Show
        Mamta A. Satoor added a comment - 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
        Hide
        Mamta A. Satoor added a comment -

        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.

        Show
        Mamta A. Satoor added a comment - 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.
        Hide
        Mamta A. Satoor added a comment -

        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.

        Show
        Mamta A. Satoor added a comment - 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.
        Hide
        Knut Anders Hatlen added a comment -

        Verified fix on trunk. Closing.

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

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development