Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3904

NPE on left join with aggregate

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0
    • Fix Version/s: 10.4.2.1, 10.5.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      High Value Fix

      Description

      Stanislav Bryzgalov reported that the following script raises an NPE on the last query (a left join involving an aggregate). I have verified this in my environment:

      drop table t1;
      drop table t2;

      – create two simple tables
      CREATE TABLE T1( D1 DATE NOT NULL PRIMARY KEY, N1 VARCHAR( 10 ) );
      CREATE TABLE T2( D2 DATE NOT NULL PRIMARY KEY, N2 VARCHAR( 10 ) );

      – insert some data, two recs in T1 and one in T2
      INSERT INTO T1 VALUES( DATE( '2008-10-01' ), 'something' ), ( DATE( '2008-10-02' ), 'something' );
      INSERT INTO T2 VALUES( DATE( '2008-10-01' ), 'something' );

      – this runs fine, gives one record '2008-10-02'
      SELECT T1.D1
      FROM T1
      LEFT JOIN T2
      ON T1.D1 = T2.D2
      WHERE T2.D2 IS NULL;

      – this runs fine too, gives one record '2008-10-02'
      SELECT MAX( T1.D1 ) as D
      FROM T1
      WHERE T1.D1 NOT IN ( SELECT T2.D2 FROM T2 );

      – this one breaks!!!
      – SQL State = XJ001 SQL Code = -1 SQL Message = DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC: java.lang.NullPointerException
      SELECT MAX( T1.D1 ) AS D
      FROM T1
      LEFT JOIN T2
      ON T1.D1 = T2.D2
      WHERE T2.D2 IS NULL;

        Attachments

        1. derby3904.diff
          6 kB
          Bryan Pendleton

          Activity

            People

            • Assignee:
              bryanpendleton Bryan Pendleton
              Reporter:
              rhillegas Richard N. Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: