[
https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707021#action_12707021
]
Mamta A. Satoor commented on DERBY-3926:
----------------------------------------
It appears that we need to catch the case where an optimizable is not the
outermost node and optimizer is considering using an index on that optimizable
(the index is on the order by column) and there is no constant comparison
predicate on that column. This pretty much makes the index of no use but say
the optimizer has been forced to use that index through optimizer overrides (ie
what we have done in our test queries in this jira). If the outer tables in the
join order are all one-row resultset, then it is not an issues because we will
be doing only one scan on the optimizable in question and all the rows returned
for that optimizable will be sorted on the index being considered on
optimizable. The problem case is when there are outer optimizable involved and
the outer optimizables will qualify more than one row which will be returned
for them and for each one of those rows, we will be doing a scan on the
optimizable in question and hence the rows satisfied through multiple scans of
the optimizable in question will not be in any sorted order. To fix this, I am
planning on adding additional code in OptimizerImpl.costBasedCostOptimizable
after the following existing if statement at line 2239
if (joinPosition == 0 ||
optimizableList.getOptimizable(proposedJoinOrder[joinPosition -
1]).considerSortAvoidancePath())
Following is the psudeo code of what I am planning on adding
if (joinPosition != 0) //if we are the outermost optimizable, we are good to go.
{
if (optimizable.currentPlanUsingIndex() &&
optimizable.indexOnOrderByColumn() &&
optimizable.noConstantPredicateOnIndexColumn())
Sorting can't be avoided on this optimizable
else
{
Continue with the existing code which is
if
(requiredRowOrdering.sortRequired(currentRowOrdering,assignedTableMap) ==
RequiredRowOrdering.NOTHING_REQUIRED)
...................
}
}
I will try to implement this psedo code. Let me know if anyone has any comments
if this does not look like a good possible solution.
> 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: d3926_repro.sql, 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.
