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 Mon, 03 Aug 2009 21:13:14 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 commented on DERBY-4331:
----------------------------------------

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