[
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.
