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