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:08:44 GMT

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

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

This is an interesting query. The first thing I noticed is that the stack trace occurs
in GROUP BY processing, but the query does not specify a GROUP BY. 

I suppose that the presence of the MAX() aggregate in the select list is
introducing a GROUP BY processing of the data behind the scenes.

My guess is that this implicit GROUP BY doesn't have some of the data structures
set up the way it expects. I'll have a more thorough look at the code.


> 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