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 Mon, 08 Jun 2009 16:26:07 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12717311#action_12717311
] 

Mamta A. Satoor commented on DERBY-3926:
----------------------------------------

Thanks, Mike, for ruuning the tests. I will plan on committing the patch tomorrow. 

One piece of possible improvement that can be made to the patch in future is to hide all the
information about the column ordering in the RowOrdering object. Currently, we check if the
current Optimizable is a one-row resultset or not and if not, then the check to make sure
that all the previous optimizables should be one-row resultset happens in OrderByList.sortRequired(this
check is done if we have found earlier that there is no equijoin between the current optimizable's
order by column with columns already ordered from the previous optimizables. This check is
made much earlier in the optimization phase and the result of that check is encapsulated in
RowOrdering object and so no code restructuring is needed for this equijoin part of the logic.
The possible code improvement is for part of the logic where the current optimizable is multi-row,
there is no equijoin on this current optimizable's order by columns so say that ordering already
exists and the previous optimizables are not all one-row resultset. It may be possible for
this piece of logic to be encapsulated somehow in RowOrdering object). I am not sure if this
encapsulation is feasible or not but just wanted to note it in the jira.

This observation was also made by Mike and Army as comments to this jira entry,
********************************************************
A B added a comment - 20/May/09 10:17 AM 
mike> I thought it would be cleaner if all the information that was necessary 
mike> [...] was actually already located in the current RowOrdering object. 
mike> [...] The work here is that some more information may need to be tracked 
mike> in the RowOrdering. 

For what little it's worth, I agree, I think this is a nice idea that would in fact be a 
bit cleaner. I think the current patch is acceptable, as well, but if I had the luxury 
of choosing, I'd probably go with Mike's approach, if possible... 
********************************************

> 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, DERBY3926_notforcheckin_patch1_051109_diff.txt,
DERBY3926_notforcheckin_patch1_051109_stat.txt, DERBY3926_notforcheckin_patch2_051109_diff.txt,
DERBY3926_patch3_051509_diff.txt, DERBY3926_patch3_051509_stat.txt, DERBY3926_patch4_051519_diff.txt,
DERBY3926_patch4_051519_stat.txt, DERBY3926_patch5_052709_diff.txt, DERBY3926_patch5_052709_stat.txt,
DERBY3926_patch6_060309_diff.txt, DERBY3926_patch6_060309_stat.txt, 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.


Mime
View raw message