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