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 Tue, 24 Mar 2009 19:16:52 GMT

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

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

I will spend little time on this jira. As Mike pointed out, it is a possibilit that in the
wrong result case, may be we are getting confused with m0 and m1 which basically are involved
in a self join. 

For a given query, if the optimizer chooses a query plan with an index key for say a where
clause and it finds that the same key can be used to satisfy the order by, then in that case,
optimizer will avoid doing the sorting required for order by because the rows are already
in order based on the index key used to satisfy where clause.

In this particular case, there is an index called key3 on table2.value which can be used to
satisfy the m1.value='21857'.  It is possible that may be there is some bug in optimizer which
thinks that the index used for m1.value can be used to avoid the sorting required for m0.value.
But the sort should not be avoided because the order by is on m0.value and not m1.value and
hence index used for m1.value can't be used to avoid the sort on m0.value. I will debug this
a little to see what is index key3 being used for ie m0 or m1. It is unclear from the query
plan whether the index key3 is being used on m0 or m1.

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; 


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