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 Wed, 05 Aug 2009 20:04:14 GMT

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

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

    Attachment: wisconsin.diff

here is the diff file from the wisconsin diffs.  I am working through it, but it is taking
time.  A lot of the diffs are that the new plan picks a different order which is also
a valid sort avoidance plan, they are the of the form where we have TABLE1 with
INDEX1(id) and TABLE2 with INDEX2(id) and query of form:
select xxx
where
TABLE1.id = TABLE2.id
order by TABLE1.id

prior to backing out equijoin stuff we would often pick INDEX2 as left most using the equijoin
logic, but now without it we instead find plan using INDEX1 as left most.  No
loss in performance as both provide a valid sort avoidance plan - just a lot of diffs.

> 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
>            Assignee: Mike Matrigali
>         Attachments: createDBsimpler.txt, createDBsimplerVer2.txt, derby4331_do_not_commit.diff,
notorderby4331.zip, orderby4331.zip, repro.sql, repro2.sql, repro2_qryplan.txt, wisconsin.diff
>
>
> 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