db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4331) Join returns results in wrong order
Date Wed, 05 Aug 2009 18:52:14 GMT

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


Mime
View raw message