db-derby-dev mailing list archives

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

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

Mike Matrigali updated DERBY-4331:
----------------------------------


here is the query plan from the bad sort results query, gotten by adding derby.language.logQueryPlan=true
to derby.properties.  Posting now, have not read through it yet - but first thing to notice
is that there
is not any sort node so as assumed the problem is not a sorter bug - but some sort of sort
avoidance
plan bug:
2009-08-03 18:09:06.296 GMT Thread[main,5,main] (XID = 259), (SESSIONID = 3), SELECT CS.ID^M
FROM^M
    CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A^M
WHERE^M
    R.PATH = '/var/tmp/source5923202038296723704opengrok/mercurial' AND ^M
    F.REPOSITORY  = R.ID                                            AND ^M
    A.REPOSITORY  = R.ID                                            AND^M
    CS.REPOSITORY = R.ID                                            AND ^M
    CS.ID         = FC.CHANGESET                                    AND ^M
    F.ID          = FC.FILE                                         AND^M
    A.ID          = CS.AUTHOR                                       AND^M
    EXISTS (^M
        SELECT 1^M
        FROM FILES F2^M
        WHERE^M
            F2.ID = FC.FILE AND F2.REPOSITORY = R.ID AND^M
            F2.PATH LIKE '/%' ESCAPE '#')^M
ORDER BY CS.ID DESC ******* Project-Restrict ResultSet (18):
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.01
    optimizer estimated cost:          157.72

Source result set:
    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.01
        optimizer estimated cost:          157.72

    Left result set:
        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.01
            optimizer estimated cost:          157.10

        Left result set:
            Nested Loop Join ResultSet:
            Number of opens = 1
            Rows seen from the left = 16
            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.02
                optimizer estimated cost:          155.23

            Left result set:
                Nested Loop Join ResultSet:
                Number of opens = 1
                Rows seen from the left = 4
                Rows seen from the right = 16
                Rows filtered = 0
                Rows returned = 16
                    constructor time (milliseconds) = 0
                    open time (milliseconds) = 0
                    next time (milliseconds) = 0
                    close time (milliseconds) = 0
                    optimizer estimated row count:            0.20
                    optimizer estimated cost:          154.53

                Left result set:
                    Nested Loop Join ResultSet:
                    Number of opens = 1
                    Rows seen from the left = 1
                    Rows seen from the right = 4
                    Rows filtered = 0
                    Rows returned = 4
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        optimizer estimated row count:            0.22
                        optimizer estimated cost:          146.81

                    Left result set:
                        Index Row to Base Row ResultSet for REPOSITORIES:
                        Number of opens = 1
                        Rows seen = 1
                        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:            1.00
                            optimizer estimated cost:          135.64

                            Index Scan ResultSet for REPOSITORIES using constraint SQL090803110642671
at read committed isolation level using share ro
w locking chosen by the optimizer
                            Number of opens = 1
                            Rows seen = 1
                            Rows filtered = 0
                            Fetch Size = 1
                                constructor time (milliseconds) = 0
                                open time (milliseconds) = 0
                                next time (milliseconds) = 0
                                close time (milliseconds) = 0
                                next time in milliseconds/row = 0

                            scan information:
                                Bit set of columns fetched=All
                                Number of columns fetched=2
                                Number of deleted rows visited=0
                                Number of pages visited=1
                                Number of rows qualified=1
                                Number of rows visited=1
                                Scan type=btree
                                Tree height=-1
                                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:            1.00
                                optimizer estimated cost:          135.64

                    Right result set:
                        Index Row to Base Row ResultSet for FILES:
                        Number of opens = 1
                        Rows seen = 4
                        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.22
                            optimizer estimated cost:           11.17

                            Index Scan ResultSet for FILES using constraint SQL090803110643012
at read committed isolation level using share row locki
ng 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=All
                                Number of columns fetched=3
                                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:
    >= 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:
None
                                optimizer estimated row count:            0.22
                                optimizer estimated cost:           11.17


                Right result set:
                    Index Row to Base Row ResultSet for FILES:
                    Number of opens = 4
                    Rows seen = 16
                    Columns accessed from heap = {0, 2}
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        optimizer estimated row count:            0.20
                        optimizer estimated cost:            7.72

                        Index Scan ResultSet for FILES using constraint SQL090803110643012
at read committed isolation level using share row locking c
hosen by the optimizer
                        Number of opens = 4
                        Rows seen = 16
                        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, 2}
                            Number of columns fetched=2
                            Number of deleted rows visited=0
                            Number of pages visited=4
                            Number of rows qualified=16
                            Number of rows visited=16
                            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 row count:            0.20
                            optimizer estimated cost:            7.72
            Right result set:
                Index Scan ResultSet for FILECHANGES using constraint SQL090803110644463 at
read committed isolation level using instantaneous share r
ow locking chosen by the optimizer
                Number of opens = 16
                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=16
                    Number of rows qualified=6
                    Number of rows visited=36
                    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 row count:            0.02
                    optimizer estimated cost:            0.70
        Right 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.01
                optimizer estimated cost:            1.87

                Index Scan ResultSet for AUTHORS using constraint SQL090803110643392 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, 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 row count:            0.01
                    optimizer estimated cost:            1.87

    Right result set:
        Project-Restrict ResultSet (17):
        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.01
            optimizer estimated cost:            0.62

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

                Index Scan ResultSet for CHANGESETS using constraint SQL090803110643850 at
read committed isolation level using share row locking chos
en 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.01
                    optimizer estimated cost:            0.62


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