Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1802452986.1224686264299.JavaMail.jira@brutus>
Date: Wed, 22 Oct 2008 07:37:44 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3904) NPE on left join with aggregate
In-Reply-To: <457106562.1223575004649.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

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

One way to verify whether or not the Min/Max optimization
feature is working properly is to use the RuntimeStatistics
or queryplan logging functionality to verify that

  select max(d1) from t1

constructs a "Last Key Index Scan ResultSet" in its chosen plan.

Here's an example of queryplan output which appears to
indicate that the optimization is working properly:

2008-10-22 14:28:58.696 GMT Thread[main,5,main] (XID = 214), (SESSIONID = 1), select max(d1) from t1
 ******* 
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
    constructor time (milliseconds) = 0
    open time (milliseconds) = 0
    next time (milliseconds) = 0
    close time (milliseconds) = 0
    restriction time (milliseconds) = 0
    projection time (milliseconds) = 0
    optimizer estimated row count:            1.00
    optimizer estimated cost:           20.17

Source result set:
    Scalar Aggregate ResultSet:
    Number of opens = 1
    Rows input = 1
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        optimizer estimated row count:            7.00
        optimizer estimated cost:           20.17

    Index Key Optimization = true
    Source result set:
        Project-Restrict ResultSet (3):
        Number of opens = 1
        Rows seen = 1
        Rows filtered = 0
        restriction = false
        projection = true
            constructor time (milliseconds) = 0
            open time (milliseconds) = 0
            next time (milliseconds) = 0
            close time (milliseconds) = 0
            restriction time (milliseconds) = 0
            projection time (milliseconds) = 0
            optimizer estimated row count:            7.00
            optimizer estimated cost:           20.17

        Source result set:
            Last Key Index Scan ResultSet for T1 using index SQL081019111017690at read committed isolation level using share row locking chosen by the optimizer
            Number of opens = 1
            Rows seen = 1
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                next time in milliseconds/row = 0

                optimizer estimated row count:            7.00
                optimizer estimated cost:           20.17


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