[DERBY-4331] Join returns results in wrong order

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.2.0, 10.6.1.0
• Fix Version/s: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.3.0, 10.6.1.0
• Component/s: SQL
• Issue & fix info: High Value Fix, Repro attached
• Bug behavior facts: Regression, Wrong query result
• Assignee: Mike Matrigali
• Reporter: Knut Anders Hatlen
• Created: 01/Aug/09 23:12
• Updated: 18/May/13 09:16
• Resolved: 12/Aug/09 16:21

Description

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

Issue Links

• is related to: DERBY-3926 Incorrect ORDER BY caused by index
• is related to: DERBY-4339 improve sort avoidance algorithm to include equijoin relationships
• relates to: DERBY-6148 Wrong sort elimination when using permuted join order