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-3926) Incorrect ORDER BY caused by index
Date Fri, 20 Mar 2009 19:04:50 GMT

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

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

The query plan when the correct results are returned is as follows
Statement Name:
        null
Statement Text:
        SELECT table1.id, m0.value, m1.value FROM table1, table2 m0, table2 m1 W
HERE table1.id=m0.id AND
m0.name='PageSequenceId' AND table1.id=m1.id AND m1.name='PostComponentId' AND m
1.value='21857' ORDER BY
m0.value
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 = 3
Rows returned = 3
Eliminate duplicates = false
In sorted order = false
Sort information:
        Number of rows input=3
        Number of rows output=3
        Sort type=internal
        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:            8.26

Source result set:
        Project-Restrict ResultSet (9):
        Number of opens = 1
        Rows seen = 3
        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.20
                optimizer estimated cost:            8.26

        Source result set:
                Nested Loop Exists Join ResultSet:
                Number of opens = 1
                Rows seen from the left = 3
                Rows seen from the right = 3
                Rows filtered = 0
                Rows returned = 3
                        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:            8.26

                Left result set:
                        Nested Loop Exists Join ResultSet:
                        Number of opens = 1
                        Rows seen from the left = 3
                        Rows seen from the right = 3
                        Rows filtered = 0
                        Rows returned = 3
                                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.29

                        Left result set:
                                Project-Restrict ResultSet (5):
                                Number of opens = 1
                                Rows seen = 3
                                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.20
                                        optimizer estimated cost:            6.9
7

                                Source result set:
                                        Index Row to Base Row ResultSet for TABL
E2:
                                        Number of opens = 1
                                        Rows seen = 3
                                        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.20
                                                optimizer estimated cost:
     6.97

                                                Index Scan ResultSet for TABLE2using index
KEY3 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 (millis
econds) = 0
                                                        open time (milliseconds)
 = 0
                                                        next time (milliseconds)
 = 0
                                                        close time (milliseconds
) = 0
                                                        next time in millisecond
s/row = 0

                                                scan information:
                                                        Bit set of columns fetch
ed=All
                                                        Number of columns fetche
d=2
                                                        Number of deleted rows v
isited=0
                                                        Number of pages visited=
2
                                                        Number of rows qualified
=3
                                                        Number of rows visited=4

                                                        Scan type=btree
                                                        Tree height=2
                                                        start position:
        >= on first 1 column(s).
        Ordered null semantics on the following columns:

                                                        stop position:
        > on first 1 column(s).
        Ordered null semantics on the following columns:

                                                        qualifiers:
None
                                                        optimizer estimated row
count:            0.20
                                                        optimizer estimated cost
:            6.97


                        Right result set:
                                Index Scan ResultSet for TABLE1 using constraint SQL090320113016460
at read committed isolation level using share row locking chosen by the optimizer
                                Number of opens = 3
                                Rows seen = 3
                                Rows filtered = 0
                                Fetch Size = 1
                                        constructor time (milliseconds) = 0
                                        open time (milliseconds) = 0
                                        next time (milliseconds) = 0
                                        close time (milliseconds) = 0
                                        next time in milliseconds/row = 0

                                scan information:
                                        Bit set of columns fetched={0}
                                        Number of columns fetched=1
                                        Number of deleted rows visited=0
                                        Number of pages visited=3
                                        Number of rows qualified=3
                                        Number of rows visited=3
                                        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.20
                                        optimizer estimated cost:            0.31


                Right result set:
                        Index Row to Base Row ResultSet for TABLE2:
                        Number of opens = 3
                        Rows seen = 3
                        Columns accessed from heap = {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:            0.97

                                Index Scan ResultSet for TABLE2 using constraint SQL090320113016670
at read committed isolation level using share row locking chosen by the optimizer
                                Number of opens = 3
                                Rows seen = 3
                                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=6
                                        Number of rows qualified=3
                                        Number of rows visited=3
                                        Scan type=btree
                                        Tree height=2
                                        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.20
                                        optimizer estimated cost:            0.97

> Incorrect ORDER BY caused by index
> ----------------------------------
>
>                 Key: DERBY-3926
>                 URL: https://issues.apache.org/jira/browse/DERBY-3926
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>            Reporter: Tars Joris
>         Attachments: derby-reproduce.zip
>
>
> I think I found a bug in Derby that is triggered by an index on a large column: VARCHAR(1024).
I know it  is generally not a good idea to have an index on such a large column.
> I have a table (table2) with a column "value", my query orders on this column but the
result is not sorted. It is sorted if I remove the index on that column.
> The output of the attached script is as follows (results should be ordered on the middle
column):
> ID                  |VALUE        |VALUE
> ----------------------------------------------
> 2147483653          |000002       |21857
> 2147483654          |000003       |21857
> 4294967297          |000001       |21857
> While I would expect:
> ID                  |VALUE        |VALUE
> ----------------------------------------------
> 4294967297          |000001       |21857
> 2147483653          |000002       |21857
> 2147483654          |000003       |21857
> This is the definition:
> CREATE TABLE table1 (id BIGINT NOT NULL, PRIMARY KEY(id));
> CREATE INDEX key1 ON table1(id);
> CREATE TABLE table2 (id BIGINT NOT NULL, name VARCHAR(40) NOT NULL, value VARCHAR(1024),
PRIMARY KEY(id, name));
> CREATE UNIQUE INDEX key2 ON table2(id, name);
> CREATE INDEX key3 ON table2(value);
> This is the query:
> SELECT table1.id, m0.value, m1.value
> FROM table1, table2 m0, table2 m1
> WHERE table1.id=m0.id
> AND m0.name='PageSequenceId'
> AND table1.id=m1.id
> AND m1.name='PostComponentId'
> AND m1.value='21857'
> ORDER BY m0.value;
> The bug can be reproduced by just executing the attached script with the ij-tool.
> Note that the result of the query becomes correct when enough data is changed. This prevented
me from creating a smaller example.
> See the attached file "derby-reproduce.zip" for sysinfo, derby.log and script.sql.
> Michael Segel pointed out:
> "It looks like its hitting the index ordering on id,name from table 2 and is ignoring
the order by clause."

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