[
https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12702147#action_12702147
]
A B commented on DERBY-3926:
----------------------------
I haven't looked at the details of your previous post, but regarding:
> So think the current fix may not be valid as it is causing sort where they do
> not need to be.
I agree. That's what I was hoping to convey in my comment on April 16th when I
wrote:
> *if* that's a correct statement of how the code is *supposed* to work, then
> it is actually
> quite useful and it does make sense.
I.e. we shouldn't remove it. As for:
> is it expected that the optimizer should recognize that a one row result set
> requires
> no ordering?
I don't know the details of how it works, but there is definitely logic in the
optimizer to try to recognize one row result sets and to make decisions based
on that information. The RowOrderingImpl.java class even includes a field
called "alwaysOrderedOptimizables" for which the javadoc says:
/*
** This vector contains table numbers for tables that are always ordered.
** This happens for one-row tables.
*/
Not sure how that plays into the above queries, but I thought I'd mention it...
> Another case I have seen is an order by of A.key, B.key being satisfied by
> join on
> A.key, B.key when it is known A.key is a single value
Yes, I think that's what the "columnsAlwaysOrdered" field in RowOrderingImpl
seems to be for. Any columns which are compared to constants (and probably
parameter markers as well?) are considered "always ordered" and hence should
not require sorting in and of themselves. Ex. In the query for this issue the
columnsAlwaysOrdered field includes columns m0.name, m1.name, and m1.value
because all of those columns are compared to literals in the WHERE clause. It
seems quite possible that the optimizer will apply these rules transitively to
detect other "always ordered" columns as well...
> 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
>
>
> 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.
