db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4331) Join returns results in wrong order
Date Mon, 03 Aug 2009 22:04:14 GMT

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

Mamta A. Satoor commented on DERBY-4331:
----------------------------------------

The following updated query is still pretty involved but it consistently reproduces the wrong
results and will go through very limited iteration of optimizer code because of all the optimizer
overrides. Ideal will be if the following query can be made simpler with fewer number of tables.
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
        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