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] Issue Comment Edited: (DERBY-4331) Join returns results in wrong order
Date Mon, 03 Aug 2009 21:20:15 GMT

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

Mamta A. Satoor edited comment on DERBY-4331 at 8/3/09 2:19 PM:
----------------------------------------------------------------

Wanted to share that if the same query is run with the optimizer ovreride of join order fixed, the query does not give incorrect results. The query plan at this time is as follows
Statement Name:
        null
Statement Text:
        SELECT CS.ID
FROM  --DERBY-PROPERTIES joinOrder=FIXED
    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
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:
Sort ResultSet:
Number of opens = 1
Rows input = 6
Rows returned = 6
Eliminate duplicates = false
In sorted order = false
Sort information:
        Number of rows input=6
        Number of rows output=6
        Sort type=internal
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        optimizer estimated row count:            0.08
        optimizer estimated cost:          553.68

Source result set:
        Project-Restrict ResultSet (20):
        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:            0.08
                optimizer estimated cost:          553.68

        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:            0.08
                        optimizer estimated cost:          553.68

                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 = 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:            0.36
                                optimizer estimated cost:          546.94

                        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:  0.60
                                        optimizer estimated cost:          475.57

                                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:          0.60
                                                optimizer estimated cost:   454.07

                                        Left result set:
                                                User supplied optimizer overrides for join are { joinOrder=FIXED }
                                                Hash Join ResultSet:
                                                Number of opens = 1
                                                Rows seen from the left = 3
                                                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 rowcount:            0.60
                                                        optimizer estimated cost:          372.68

                                                Left result set:
                                                        Table Scan ResultSet for CHANGESETS at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                                        Number of opens = 1
                                                        Rows seen = 3
                                                        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, 3}
                                                                Number of columns fetched=3
                                                                Number of pagesvisited=1
                                                                Number of rows qualified=3
                                                                Number of rows visited=3
                                                                Scan type=heap
                                                                start position:
null                                                            stop position:
null                                                            qualifiers:
None
                                                                optimizer estimated row count:            8.00
                                                                optimizer estimated cost:          351.84

                                                Right result set:
                                                        Hash Scan ResultSet for FILECHANGES using constraint FILECHANGES_FILE_CHANGESET at read committed isolation level using instantaneous share row locking:
                                                        Number of opens = 3
                                                        Hash table size = 3
                                                        Hash key is column number 1
                                                        Rows seen = 6
                                                        Rows filtered = 0
                                                                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 pagesvisited=1
                                                                Number of rows qualified=6
                                                                Number of rows visited=6
                                                                Scan type=btree
                                                                Tree height=1
                                                                start position:

        None
                                                                stop position:
        None
                                                                scan qualifiers:

None
                                                                next qualifiers:

Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false

                                                                optimizer estimated row count:            0.60
                                                                optimizer estimated cost:           20.84


                                        Right result set:
                                                Project-Restrict ResultSet (10):

                                                Number of opens = 6
                                                Rows seen = 6
                                                Rows filtered = 0
                                                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 rowcount:            0.60
                                                        optimizer estimated cost:           81.39

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

                                                                Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_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=All
                                                                        Number of columns fetched=2
                                                                        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:            0.60
                                                                        optimizer estimated cost:           81.39



                                Right result set:
                                        Project-Restrict ResultSet (13):
                                        Number of opens = 6
                                        Rows seen = 6
                                        Rows filtered = 0
                                        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:          0.60
                                                optimizer estimated cost:    21.50

                                        Source result set:
                                                Index Row to Base Row ResultSet for FILES:
                                                Number of opens = 6
                                                Rows seen = 6
                                                Columns accessed from heap = {2}

                                                        constructor time (milliseconds) = 0
                                                        open time (milliseconds) = 0
                                                        next time (milliseconds) = 0
                                                        close time (milliseconds) = 0
                                                        optimizer estimated rowcount:            0.60
                                                        optimizer estimated cost:           21.50

                                                        Index Scan ResultSet for FILES using constraint FILES_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=All
                                                                Number of columns fetched=2
                                                                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:            0.60
                                                                optimizer estimated cost:           21.50



                        Right result set:
                                Project-Restrict ResultSet (16):
                                Number of opens = 6
                                Rows seen = 6
                                Rows filtered = 0
                                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:  0.36
                                        optimizer estimated cost:           71.37

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

                                                Index Scan ResultSet for AUTHORS using constraint AUTHORS_REPOSITORY_NAME at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                                Number of opens = 6
                                                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, 2}
                                                        Number of columns fetched=2
                                                        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:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false

                                                        optimizer estimated rowcount:            0.36
                                                        optimizer estimated cost:           71.37



                Right result set:
                        Project-Restrict ResultSet (19):
                        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:            0.08
                                optimizer estimated cost:            6.73

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

                                        Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of opens = 6
                                        Rows seen = 24
                                        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=All
                                                Number of columns fetched=3
                                                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:
        >= on first 2 column(s).
        Ordered null semantics on the following columns:0 1
                                                stop position:
        >= on first 2 column(s).
        Ordered null semantics on the following columns:
0 1
                                                qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false

                                                optimizer estimated row count: 0.08
                                                optimizer estimated cost: 6.73


      was (Author: mamtas):
    Wanted to share that once the db is created, re-running the query does not give incorrect results. The query plan at this time is as follows
Statement Name:
        null
Statement Text:
        SELECT CS.ID
FROM  --DERBY-PROPERTIES joinOrder=FIXED
    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
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:
Sort ResultSet:
Number of opens = 1
Rows input = 6
Rows returned = 6
Eliminate duplicates = false
In sorted order = false
Sort information:
        Number of rows input=6
        Number of rows output=6
        Sort type=internal
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        optimizer estimated row count:            0.08
        optimizer estimated cost:          553.68

Source result set:
        Project-Restrict ResultSet (20):
        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:            0.08
                optimizer estimated cost:          553.68

        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:            0.08
                        optimizer estimated cost:          553.68

                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 = 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:            0.36
                                optimizer estimated cost:          546.94

                        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:  0.60
                                        optimizer estimated cost:          475.57

                                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:          0.60
                                                optimizer estimated cost:   454.07

                                        Left result set:
                                                User supplied optimizer overrides for join are { joinOrder=FIXED }
                                                Hash Join ResultSet:
                                                Number of opens = 1
                                                Rows seen from the left = 3
                                                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 rowcount:            0.60
                                                        optimizer estimated cost:          372.68

                                                Left result set:
                                                        Table Scan ResultSet for CHANGESETS at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                                        Number of opens = 1
                                                        Rows seen = 3
                                                        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, 3}
                                                                Number of columns fetched=3
                                                                Number of pagesvisited=1
                                                                Number of rows qualified=3
                                                                Number of rows visited=3
                                                                Scan type=heap
                                                                start position:
null                                                            stop position:
null                                                            qualifiers:
None
                                                                optimizer estimated row count:            8.00
                                                                optimizer estimated cost:          351.84

                                                Right result set:
                                                        Hash Scan ResultSet for FILECHANGES using constraint FILECHANGES_FILE_CHANGESET at read committed isolation level using instantaneous share row locking:
                                                        Number of opens = 3
                                                        Hash table size = 3
                                                        Hash key is column number 1
                                                        Rows seen = 6
                                                        Rows filtered = 0
                                                                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 pagesvisited=1
                                                                Number of rows qualified=6
                                                                Number of rows visited=6
                                                                Scan type=btree
                                                                Tree height=1
                                                                start position:

        None
                                                                stop position:
        None
                                                                scan qualifiers:

None
                                                                next qualifiers:

Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false

                                                                optimizer estimated row count:            0.60
                                                                optimizer estimated cost:           20.84


                                        Right result set:
                                                Project-Restrict ResultSet (10):

                                                Number of opens = 6
                                                Rows seen = 6
                                                Rows filtered = 0
                                                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 rowcount:            0.60
                                                        optimizer estimated cost:           81.39

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

                                                                Index Scan ResultSet for REPOSITORIES using constraint REPOSITORIES_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=All
                                                                        Number of columns fetched=2
                                                                        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:            0.60
                                                                        optimizer estimated cost:           81.39



                                Right result set:
                                        Project-Restrict ResultSet (13):
                                        Number of opens = 6
                                        Rows seen = 6
                                        Rows filtered = 0
                                        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:          0.60
                                                optimizer estimated cost:    21.50

                                        Source result set:
                                                Index Row to Base Row ResultSet for FILES:
                                                Number of opens = 6
                                                Rows seen = 6
                                                Columns accessed from heap = {2}

                                                        constructor time (milliseconds) = 0
                                                        open time (milliseconds) = 0
                                                        next time (milliseconds) = 0
                                                        close time (milliseconds) = 0
                                                        optimizer estimated rowcount:            0.60
                                                        optimizer estimated cost:           21.50

                                                        Index Scan ResultSet for FILES using constraint FILES_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=All
                                                                Number of columns fetched=2
                                                                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:            0.60
                                                                optimizer estimated cost:           21.50



                        Right result set:
                                Project-Restrict ResultSet (16):
                                Number of opens = 6
                                Rows seen = 6
                                Rows filtered = 0
                                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:  0.36
                                        optimizer estimated cost:           71.37

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

                                                Index Scan ResultSet for AUTHORS using constraint AUTHORS_REPOSITORY_NAME at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                                Number of opens = 6
                                                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, 2}
                                                        Number of columns fetched=2
                                                        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:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false

                                                        optimizer estimated rowcount:            0.36
                                                        optimizer estimated cost:           71.37



                Right result set:
                        Project-Restrict ResultSet (19):
                        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:            0.08
                                optimizer estimated cost:            6.73

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

                                        Index Scan ResultSet for FILES using constraint FILES_REPOSITORY_PATH at read committed isolation level using instantaneous share row locking chosen by the optimizer
                                        Number of opens = 6
                                        Rows seen = 24
                                        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=All
                                                Number of columns fetched=3
                                                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:
        >= on first 2 column(s).
        Ordered null semantics on the following columns:0 1
                                                stop position:
        >= on first 2 column(s).
        Ordered null semantics on the following columns:
0 1
                                                qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false

                                                optimizer estimated row count: 0.08
                                                optimizer estimated cost: 6.73

  
> 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.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.2.0, 10.6.0.0
>            Reporter: Knut Anders Hatlen
>         Attachments: 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