db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3904) NPE on left join with aggregate
Date Sun, 19 Oct 2008 18:36:44 GMT

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

Bryan Pendleton commented on DERBY-3904:
----------------------------------------

GroupByNode.considerPostOptimizeOptimizations() is examining the query
to see if it can perform a very special optimization involving a MIN or MAX query
on an indexed column, in which case we may be able to go directly to 
the row with the MIN or MAX value by way of the index. The routine wants to
check that:

        /* Consider the optimization for min with asc index on that column or
         * max with desc index on that column:
         *  o  No group by
         *  o  One of:
         *      o  min/max(ColumnReference) is only aggregate && source is
         *         ordered on the ColumnReference
         *      o  min/max(ConstantNode)
         * The optimization of the other way around (min with desc index or
         * max with asc index) has the same restrictions with the additional
         * temporary restriction of no qualifications at all (because
         * we don't have true backward scans).
         */

Since T1.D1 is the PRIMARY KEY of the table, the result of the join is in fact
ordered on T1.D1.

So we examine the access path to learn more about the key columns in
the index that will be used.

There is a comment at this point:

 /* Check if we have an access path, this will be
  * null in a join case (See Beetle 4423)
  */

However, even though we are in a join case, the access path is NOT null;
rather it is a valid AccessPathImpl with a NULL conglomerate descriptor,
and a non-null join strategy.

So then we hit the code:

  AccessPath accessPath= getTrulyTheBestAccessPath();
  if (accessPath == null)
      return;
  IndexDescriptor id = accessPath.
                      getConglomerateDescriptor().
                      getIndexDescriptor();
  int[] keyColumns = id.baseColumnPositions();

and the "if (accessPath == null)" test fails, since the accessPath is not null,
but the dereference to fetch the IndexDescriptor gets a NPE on the
return from getConglomerateDescriptor().

It seems that the code expected that it would not run that line for
an access path with a join strategy, but the check for a null
access path isn't working to catch that case.

I'm wondering if I could improve that "if" test to be:

  if (accessPath == null || accessPath.getJoinStrategy() != null)

but I need to study AccessPathImpl some more and see under what
circumstances it has a non-null join strategy.


> NPE on left join with aggregate
> -------------------------------
>
>                 Key: DERBY-3904
>                 URL: https://issues.apache.org/jira/browse/DERBY-3904
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 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
>            Reporter: Rick Hillegas
>            Assignee: Bryan Pendleton
>
> 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;

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


Mime
View raw message