Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1757236116.1249337294829.JavaMail.jira@brutus>
Date: Mon, 3 Aug 2009 15:08:14 -0700 (PDT)
From: "Mike Matrigali (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4331) Join returns results in wrong order
In-Reply-To: <1441768431.1249168454800.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-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-4331:
----------------------------------


i have been looking at the query plan and have more questions than answers.  

One strange thing is that the bad query plan does not use the descending
index at all.  With DERBY-3926 the usual problem was that an index that
would naturally satisfy a sort avoidance plan was used but in some part of the 
join order which actually didn't result in the row ordering being correct.  

The plan looks like it does some sort of query transformation, maybe flatten but not sure if that is right term.  The EXISTS subquery I think is making it hard to 
control the plan with hints, but am not sure.  If anyone can figure out a rewrite that reproduces the bug without the subquery that would help also.

I tried the following query thinking just the existence of the subquery might be
enough, but it got correct ordered results:
SELECT CS.ID
FROM
    CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A
WHERE
    R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
    F.REPOSITORY  = R.ID                                            AND
    A.REPOSITORY  = R.ID                                            AND
    CS.REPOSITORY = R.ID                                            AND
    CS.ID         = FC.CHANGESET                                    AND
    F.ID          = FC.FILE                                         AND
    A.ID          = CS.AUTHOR                                       AND
    EXISTS (
        SELECT 1
        FROM FILES F2
        WHERE
            F2.REPOSITORY = 1)
ORDER BY CS.ID DESC;

> Join returns results in wrong order
> -----------------------------------
>
>                 Key: DERBY-4331
>                 URL: https://issues.apache.org/jira/browse/DERBY-4331
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.2.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>         Attachments: repro.sql, repro2.sql, repro2_qryplan.txt
>
>
> In Derby 10.5.2.0, the query below started returning results in wrong order (note the ORDER BY clause). Derby 10.5.1.1 gives the expected ordering.
> ij> SELECT CS.ID
> FROM
>     CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A
> WHERE
>     R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND F.REPOSITORY = R.ID AND A.REPOSITORY = R.ID AND
>     CS.REPOSITORY = R.ID AND CS.ID = FC.CHANGESET AND F.ID = FC.FILE AND
>     A.ID = CS.AUTHOR AND
>     EXISTS (
>         SELECT 1
>         FROM FILES F2
>         WHERE
>             F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND
>             F2.PATH LIKE '/%' ESCAPE '#')
> ORDER BY CS.ID DESC;
> ID         
> -----------
> 1          
> 2          
> 3          
> 2          
> 2          
> 3          
> 6 rows selected

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