db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lily Wei (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4331) Join returns results in wrong order
Date Tue, 04 Aug 2009 03:20:15 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Lily Wei updated DERBY-4331:
----------------------------

    Attachment: orderby4331.zip
                notorderby4331.zip

Taking out Author table from previous query still not ordering in trunk but works 10.5 (783167)
ij> 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
     , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
WHERE
 R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
 F.REPOSITORY = R.ID AND
 CS.REPOSITORY = R.ID AND
 CS.ID = FC.CHANGESET AND
 F.ID = FC.FILE AND
 EXISTS (
   SELECT 1
    FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
    WHERE
           F2.ID = FC.FILE)
 ORDER BY CS.ID DESC;
ID
-----------
1
2
3
2
2
3

If take out of 'CS.ID = FC.CHANGESET' from where cause constraint, the query will perform
order by on both trunk and 10.5(783167)
ij> 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
     , CHANGESETS CS --DERBY-PROPERTIES constraint=CHANGESETS_PRIMARY_ID
WHERE
 R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND
 F.REPOSITORY = R.ID AND
 CS.REPOSITORY = R.ID AND
 F.ID = FC.FILE AND
 EXISTS (
   SELECT 1
    FROM FILES F2 --DERBY-PROPERTIES constraint=FILES_REPOSITORY_PATH
    WHERE
           F2.ID = FC.FILE)
 ORDER BY CS.ID DESC;
ID
-----------
3
3
3
3
3
3
2
2
2
2
2
2
1
1
1
1
1
1

18 rows selected

I cannot really tell what is the difference between the two path. However, the value -1 for
Tree height on the first query seems odd to me. 
The derby.log for the first query is on notorderby4331.zip and the second query is on orderby4331.zip.
(The below capture from derby.log on notorderby4331.zip)
                                                User supplied optimizer overrides on REPOSITORIES
are { index=SQL090803170751640 }
                                                Index Scan ResultSet for REPOSITORIES using
constraint REPOSITORIES_PATH at read committed isolation level using share row locking chosen
by the optimizer
                                                Number of opens = 1
                                                Rows seen = 1
                                                Rows filtered = 0
                                                Fetch Size = 1
                                                        constructor time (milliseconds) =
0
                                                        open time (milliseconds) = 0
                                                        next time (milliseconds) = 0
                                                        close time (milliseconds) = 0
                                                        next time in milliseconds/row = 0

                                                scan information:
                                                        Bit set of columns fetched=All
                                                        Number of columns fetched=2
                                                        Number of deleted rows visited=0
                                                        Number of pages visited=1
                                                        Number of rows qualified=1
                                                        Number of rows visited=1
                                                        Scan type=btree
                                                        Tree height=-1               <<<====
-1 is an odd value
                                                        start position:
        >= on first 1 column(s).

(The below capture from derby.log on orderby4331.zip)
                                                        User supplied optimizer overrides
on REPOSITORIES are { index=SQL090803170751640 }
                                                        Index Scan ResultSet for REPOSITORIES
using constraint REPOSITORIES_PATH at read committed isolation level using share row locking
chosen by the optimizer
                                                        Number of opens = 1
                                                        Rows seen = 1
                                                        Rows filtered = 0
                                                        Fetch Size = 1
                                                                constructor time (milliseconds)
= 0
                                                                open time (milliseconds) =
0
                                                                next time (milliseconds) =
0
                                                                close time (milliseconds)
= 0
                                                                next time in milliseconds/row
= 0

                                                        scan information:
                                                                Bit set of columns fetched=All
                                                                Number of columns fetched=2
                                                                Number of deleted rows visited=0
                                                                Number of pages visited=1
                                                                Number of rows qualified=1
                                                                Number of rows visited=1
                                                                Scan type=btree
                                                                Tree height=1   <<<====
not -1 value
                                                                start position:
        >= on first 1 column(s).

Hope this info is helpful. Thanks, Lily

> 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: 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