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