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 Thu, 02 Apr 2009 16:56:12 GMT

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

View raw message