db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4331) Join returns results in wrong order
Date Tue, 04 Aug 2009 00:01:15 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

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


This query also gets out of order rows in trunk but not in 10.5 before 3629 change.  It fixes
the indexes used also:
SELECT CS.ID
FROM  --DERBY-PROPERTIES joinOrder=FIXED
      REPOSITORIES R  --DERBY-PROPERTIES constraint=REPOSITORIES_PATH
    , FILES        F  --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
    , FILECHANGES  FC --DERBY-PROPERTIES constraint=FILECHANGES_FILE_CHANGESET
    , AUTHORS      A  --DERBY-PROPERTIES constraint=AUTHORS_REPOSITORY_NAME
    , CHANGESETS   CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
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 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
        WHERE
            F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND
            F2.PATH LIKE '/%' ESCAPE '#')
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.


Mime
View raw message