[
https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali updated DERBY-4331:
----------------------------------
I am not sure if just running update statistics will fix this problem for any
set of data. For this
query it does look like changing the order by clause to include an extra column
will work around
this bug:
ORDER BY CS.ID, CS.REPOSITORY;
> 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: createDBsimpler.txt, createDBsimplerVer2.txt,
> notorderby4331.zip, orderby4331.zip, 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.
