db-derby-dev mailing list archives

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

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

Kathey Marsden commented on DERBY-3904:
---------------------------------------

Beetle 4423 had summary:

NullPointerException returned by max() aggregate function called on a column with an index
in a join

Description:
If you call the max() aggregate function on a join statement 
where the column has an index as shown below:
select max(city_id)
 from cities c1, countries c2
 where 
  c1.city_id = 999

Java.lang.NullPointerException is thrown.

Comment:
 The problem is that in 
attempting to determine whether we can optimize the execution 
of the query by looking at the index for the max or min we 
don't check that the result set is coming directly from a base 
table (i.e. has an index)or has been derived in some way in 
this case from a join

The fix added the if condition to return if the accessPath was null


> 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