[
https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12695057#action_12695057
]
Mamta A. Satoor commented on DERBY-3926:
----------------------------------------
Another interesting thing I noticed (in both working and non-working case) is
we do recognize the optimize phase that sorting is required for m0 for the
order by clause as shown belowHere, for the join order [2, 0, 1], we identify
that sorting is required for M0.
Thread [main] (Suspended)
OrderByList.sortRequired(RowOrdering, JBitSet) line: 549
Level2OptimizerImpl(OptimizerImpl).costBasedCostOptimizable(Optimizable,
TableDescriptor, ConglomerateDescriptor, OptimizablePredicateList,
CostEstimate) line: 2248
Level2OptimizerImpl(OptimizerImpl).costOptimizable(Optimizable,
TableDescriptor, ConglomerateDescriptor, OptimizablePredicateList,
CostEstimate) line: 1984
FromBaseTable.optimizeIt(Optimizer, OptimizablePredicateList,
CostEstimate, RowOrdering) line: 521
ProjectRestrictNode.optimizeIt(Optimizer, OptimizablePredicateList,
CostEstimate, RowOrdering) line: 316
Level2OptimizerImpl(OptimizerImpl).costPermutation() line: 1938
SelectNode.optimize(DataDictionary, PredicateList, double) line: 1767
CursorNode(DMLStatementNode).optimizeStatement() line: 305
CursorNode.optimizeStatement() line: 515
GenericStatement.prepMinion(LanguageConnectionContext, boolean,
Object[], SchemaDescriptor, boolean) line: 367
GenericStatement.prepare(LanguageConnectionContext, boolean) line: 88
GenericLanguageConnectionContext.prepareInternalStatement(SchemaDescriptor,
String, boolean, boolean) line: 802
EmbedStatement40(EmbedStatement).execute(String, boolean, boolean, int,
int[], String[]) line: 606
ij.executeImmediate(String) line: 329
utilMain.doCatch(String) line: 505
utilMain.runScriptGuts() line: 347
utilMain.go(LocalizedInput[], LocalizedOutput) line: 245
Main.go(LocalizedInput, LocalizedOutput) line: 210
Main.mainCore(String[], Main) line: 177
Main.main(String[]) line: 73
Main.main(String[]) line: 73
ij.main(String[]) line: 59
But it is obvious from the query plan for non-working that somehow we later
decide to do sort avoidance for m0. I will look more to see where the optimizer
changes it mind about the sort requirement for m0.
> 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.
