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 23:15: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: derby_4331_patch_2.diff

Still have not finished running tests (all of the junit tests passed, waiting on old
harness tests), but here is the current state of the patch.  It includes the new tests that
mamta contributed (I had to make a few minor changes to get the queries to run correctly).
 

It also includes an update to the wisconsin tests and master.  I worked through the
diffs and there were only 2 queries that previous to this patch that produced a 
sort avoidance plan and that now don't.  I have filed DERBY-4339 to note these 
2 queries:
select * from --DERBY-PROPERTIES joinOrder=FIXED
TENKTUP2, TENKTUP1
where TENKTUP1.unique1 = TENKTUP2.unique1
and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1'; 
select * from TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
order by TENKTUP1.unique1 = TENKTUP2.unique1 

All the other diffs were changes to the plans where the optimizer just choose 
different ways to provide a sort avoidance plan with the available indexes.

> 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_additionalTests_diff.txt,
derby4331_do_not_commit.diff, derby_4331_patch_2.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