[
https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-3926:
-----------------------------------
Attachment: DERBY3926_patch5_052709_stat.txt
DERBY3926_patch5_052709_diff.txt
I am attaching a new patch, namely DERBY3926_patch5_052709_stat.txt. This patch
still does the same thing as the previous patch which is to make sure that if
order by column does not belong to the outermost optimizable, then check if the
order by column has a constant comparison predicate on it or it is coming from
a single-row table. If either of the 2 conditions is true then we do not need
to worry if the outer opitimizable to the optimizable for order by column are
one-row resultsets or not. One thing to note is that I have the additional
check to see if the order by column comes from a single row table. Earlier
patch only checked if the order by column has constant comparison predicate on
it. All this logic is now in RequiredRowOrdering:sortRequired(RowOrdering
rowOrdering, JBitSet tableMap, OptimizableList optimizableList) throws
StandardException;
This is more logical place for code to be in rather than putting in
OptimizerImpl which is where the previous patch put the code changes.
The one item remaining on this jira is one query from wisconsin that now
requires a sorting node rather than avoiding the sort. Mike and Bryan are right
about checking if the previous optimizables are multi-row optimizables then see
if the multiple scan into order by column's optimizable are all going to return
same single row resulset. If so, then there is no need to require sorting even
though outer optimizables are multu-row resultsets.
> 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,
> 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.
