[
https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali updated DERBY-4331:
----------------------------------
I am still investigating, but as this seems time critical am posting some
incomplete information.
I have been debugging and talking to mamta about this issue. I now do believe
part of
DERBY-3926 should be backed out as the logic is incomplete, I don't believe the
fix for this is
simple and it would be better to get all the code lines into a safer state and
put off the complete
fix for later. But I believe we only need to backout part of the fix.
The fix for DERBY-3926 addressed 2 issues:
1) The code incorrectly assumed if a query plan resulted in for example "always
sorted on a",
"always sorted on b" where "always sorted" meant something like a=5, that
then one could
assume the query was also just sorted on b. This was only true if a was a
single row result
set. I believe this code is working fine.
2) When the fix went in we noticed a few queries in wisconsin were not doing
sort avoidance any
more . See 23/Apr/09 01:13 PM DERBY-3926 comment by mamta. To this end
code was
added to do some tricky stuff with join equivalent analysis. This part of
the fix is localized to
a single file mamta and I have looked at it and it does not do the right
work. It does not handle
desc indexes or desc ordery by properly and can incorrectly assume that a
query ordered
on a, b, c satisfies and order by c. I have just tried just backing out
this change and
all the DERBY-4331 cases I tried worked, all the test cases specific for
DERBY-3926 worked,
and as expected I got wisconsin diffs that I am analysing. I have not had
time to run full tests.
I will post a patch with just this change if anyone else wants to experiment.
> 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.
