db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4331) Join returns results in wrong order
Date Tue, 04 Aug 2009 06:39:14 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738862#action_12738862
] 

Mamta A. Satoor commented on DERBY-4331:
----------------------------------------

The query plan for the query below is as follows
SELECT CS.ID 
FROM --DERBY-PROPERTIES joinOrder=FIXED 
    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 
 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; 


Statement Name:
        null
Statement Text:
        SELECT CS.ID
FROM --DERBY-PROPERTIES joinOrder=FIXED
    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
 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
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 6
Rows filtered = 0
restriction = false
projection = true
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        restriction time (milliseconds) = 0
        projection time (milliseconds) = 0
        optimizer estimated row count:            2.67
        optimizer estimated cost:         2330.17

Source result set:
        User supplied optimizer overrides for join are { joinOrder=FIXED }
        Nested Loop Join ResultSet:
        Number of opens = 1
        Rows seen from the left = 6
        Rows seen from the right = 6
        Rows filtered = 0
        Rows returned = 6
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                optimizer estimated row count:            2.67
                optimizer estimated cost:         2330.17

        Left result set:
                User supplied optimizer overrides for join are { joinOrder=FIXED}
                Nested Loop Exists Join ResultSet:
                Number of opens = 1
                Rows seen from the left = 6
                Rows seen from the right = 6
                Rows filtered = 0
                Rows returned = 6
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        optimizer estimated row count:            6.00
                        optimizer estimated cost:          357.23

                Left result set:
                        User supplied optimizer overrides for join are { joinOrder=FIXED }
                        Nested Loop Join ResultSet:
                        Number of opens = 1
                        Rows seen from the left = 4
                        Rows seen from the right = 6
                        Rows filtered = 0
                        Rows returned = 6
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                optimizer estimated row count:            6.00
                                optimizer estimated cost:          347.80

                        Left result set:
                                Index Row to Base Row ResultSet for FILES:
                                Number of opens = 1
                                Rows seen = 4
                                Columns accessed from heap = {0}
                                        constructor time (milliseconds) = 0
                                        open time (milliseconds) = 0
                                        next time (milliseconds) = 0
                                        close time (milliseconds) = 0
                                        optimizer estimated row count:  9.00
                                        optimizer estimated cost:          328.82

                                        User supplied optimizer overrides on FILES are { index=SQL090803230333481
}
                                        Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH
at read committed isolation level using share row locking chosen by the optimizer
                                        Number of opens = 1
                                        Rows seen = 4
                                        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={2}
                                                Number of columns fetched=1
                                                Number of deleted rows visited=0

                                                Number of pages visited=1
                                                Number of rows qualified=4
                                                Number of rows visited=4
                                                Scan type=btree
                                                Tree height=-1
                                                start position:
        None
                                                stop position:
        None
                                                qualifiers:
None
                                                optimizer estimated row count:          9.00
                                                optimizer estimated cost:   328.82


                        Right result set:
                                User supplied optimizer overrides on FILECHANGES are { index=SQL090803230334101
}
                                Index Scan ResultSet for FILECHANGES using constraint FILECHANGES_FILE_CHANGESET
at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                Number of opens = 4
                                Rows seen = 6
                                Rows filtered = 0
                                Fetch Size = 16
                                        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={0, 1}
                                        Number of columns fetched=2
                                        Number of deleted rows visited=0
                                        Number of pages visited=4
                                        Number of rows qualified=6
                                        Number of rows visited=9
                                        Scan type=btree
                                        Tree height=1
                                        start position:
        >= on first 1 column(s).
        Ordered null semantics on the following columns:0
                                        stop position:
        > on first 1 column(s).
        Ordered null semantics on the following columns:0
                                        qualifiers:None
                                        optimizer estimated row count:  6.00
                                        optimizer estimated cost:           18.97


                Right result set:
                        User supplied optimizer overrides on CHANGESETS are { index=SQL090803230333820
}
                        Index Scan ResultSet for CHANGESETS using constraint CHANGESETS_PRIMARY_ID
at read committed isolation level using share row locking chosen by the optimizer
                        Number of opens = 6
                        Rows seen = 6
                        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={0}
                                Number of columns fetched=1
                                Number of deleted rows visited=0
                                Number of pages visited=6
                                Number of rows qualified=6
                                Number of rows visited=6
                                Scan type=btree
                                Tree height=1
                                start position:
        >= on first 1 column(s).
        Ordered null semantics on the following columns:0
                                stop position:
        > on first 1 column(s).
        Ordered null semantics on the following columns:0
                                qualifiers:None
                                optimizer estimated row count:            6.00
                                optimizer estimated cost:            9.43


        Right result set:
                Project-Restrict ResultSet (10):
                Number of opens = 6
                Rows seen = 24
                Rows filtered = 18
                restriction = true
                projection = true
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        restriction time (milliseconds) = 0
                        projection time (milliseconds) = 0
                        optimizer estimated row count:            2.67
                        optimizer estimated cost:         1972.94

                Source result set:
                        Index Row to Base Row ResultSet for FILES:
                        Number of opens = 6
                        Rows seen = 24
                        Columns accessed from heap = {0}
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                optimizer estimated row count:            2.67
                                optimizer estimated cost:         1972.94

                                User supplied optimizer overrides on FILES are { index=SQL090803230333481
}
                                Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH
at read committed isolation level using share row locking chosen by the optimizer
                                Number of opens = 6
                                Rows seen = 24
                                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={2}
                                        Number of columns fetched=1
                                        Number of deleted rows visited=0
                                        Number of pages visited=6
                                        Number of rows qualified=24
                                        Number of rows visited=24
                                        Scan type=btree
                                        Tree height=1
                                        start position:        None
                                        stop position:        None
                                        qualifiers:None
                                        optimizer estimated row count:  2.67
                                        optimizer estimated cost:         1972.94


> 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