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