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-3926) Incorrect ORDER BY caused by index
Date Wed, 29 Apr 2009 18:33:30 GMT

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

Mamta A. Satoor edited comment on DERBY-3926 at 4/29/09 11:32 AM:
------------------------------------------------------------------

I am attaching a newer version of script3.sql The new script is called script3WithUserFriendlyIndexNames.sql
The only changes made to script3.sql are to use user-friendly index names so that it is easier
to understand the query which is using index names through optimizer overrides to demonstrate
the buggy behavior. Hopefully it will be easier to read the query plan as well. The query
to see the problem is as follows

SELECT table1.id, table2.value, table3.value FROM --DERBY-PROPERTIES joinOrder=FIXED
table3 -- DERBY-PROPERTIES index=nonUniqueOnValue_Table3
, table2 -- DERBY-PROPERTIES index=nonUniqueOnValue_Table2
, table1
WHERE table1.id=table2.id AND table2.name='PageSequenceId' 
AND table1.id=table3.id 
AND table3.name='PostComponentId' 
AND table3.value='21857' ORDER BY table2.value;

The query plan for the query above looks as follows
Statement Name:
        null
Statement Text:
        SELECT table1.id, table2.value, table3.value FROM --DERBY-PROPERTIES joinOrder=FIXED
table3 -- DERBY-PROPERTIES index=nonUniqueOnValue_Table3
, table2 -- DERBY-PROPERTIES index=nonUniqueOnValue_Table2
, table1
WHERE table1.id=table2.id AND table2.name='PageSequenceId'
AND table1.id=table3.id
AND table3.name='PostComponentId'
AND table3.value='21857' ORDER BY table2.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:
Project-Restrict ResultSet (10):
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:         1185.66

Source 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 = 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:         1185.66

        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 = 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:         1185.35

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

                        Source result set:
                                Index Row to Base Row ResultSet for TABLE3:
                                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

                                        User supplied optimizer overrides on TABLE3 are {
index=NONUNIQUEONVALUE_TABLE3 }
                                        Index Scan ResultSet for TABLE3 using index NONUNIQUEONVALUE_TABLE3
at read committed isolation level using share row locking chosen by the optimizer
                                        Number of opens = 1
                                        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=2
                                                Number of deleted rows visited=0

                                                Number of pages visited=2
                                                Number of rows qualified=3
                                                Number of rows visited=4
                                                Scan type=btree
                                                Tree height=-1
                                                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:
                        Project-Restrict ResultSet (8):
                        Number of opens = 3
                        Rows seen = 8688
                        Rows filtered = 8685
                        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:         1178.38

                        Source result set:
                                Index Row to Base Row ResultSet for TABLE2:
                                Number of opens = 3
                                Rows seen = 8688
                                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:         1178.38

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

                                                Number of pages visited=12
                                                Number of rows qualified=8688
                                                Number of rows visited=8688
                                                Scan type=btree
                                                Tree height=2
                                                start position:        None
                                                stop position:        None
                                                qualifiers:None
                                                optimizer estimated row count:          0.20
                                                optimizer estimated cost:  1178.38



        Right result set:
                Index Scan ResultSet for TABLE1 using constraint SQL090429102526750 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:
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:            0.31


      was (Author: mamtas):
    I am attaching a newer version of script3.sql The new script is called script3WithUserFriendlyIndexNames.sql
The only changes made to script3.sql are to use user-friendly index names so that it is easier
to understand the query which is using index names through optimizer overrides to demonstrate
the buggy behavior. Hopefully it will be easier to read the query plan as well. The query
to see the problem is as follows

SELECT table1.id, table2.value, table3.value FROM --DERBY-PROPERTIES joinOrder=FIXED
table3 -- DERBY-PROPERTIES index=nonUniqueOnValue_Table3
, table2 -- DERBY-PROPERTIES index=nonUniqueOnValue_Table2
, table1
WHERE table1.id=table2.id AND table2.name='PageSequenceId' 
AND table1.id=table3.id 
AND table3.name='PostComponentId' 
AND table3.value='21857' ORDER BY table2.value;

  
> 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.1.3.3, 10.2.3.0, 10.3.3.1, 10.4.2.0
>            Reporter: Tars Joris
>            Assignee: Mamta A. Satoor
>         Attachments: derby-reproduce.zip, script3.sql, script3WithUserFriendlyIndexNames.sql,
test-script.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