[
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_patch4_051519_stat.txt
DERBY3926_patch4_051519_diff.txt
Army, thanks for looking at the patch. I need to remove the commented out if
condition along with corresponding {} brackets. Also, I have added the comment
for currentRowOrdering.alwaysOrdered(previousOptimizable.getTableNumber()) Let
me know if I should reword it differently.
I have attached a patch with these changes and some more order by tests. That
is the only difference between this patch and the earlier patch.,
On another note, I was planning on moving all this new code inside the existing
method OrderByList.sortRequired which gets called by OptimizerImpl. This way,
all the decision regarding sort avoidance would have been made in the
appropriate named method sortRequired. But the new code added by me needs
access to current join order position, previous optimizables, predicate lists
etc which are not available to OrderByList.sortRequired method. Passing these
additional parameters to the method will require changes to other part of the
Derby code where we call this method. Based on that, I am thinking that I
should leave my new code where it is right now. If anyone feels differently
about the location of the new code, do let me know.
The only issue with the suggested patch is that a query like following is now
going to require a sort node on the top which we didn't require earlier. Other
than this one case, all the other test cases
have worked fine with my patch. I am inclied on going ahead and committing the
patch with this know one case. If I don't hear anything back on the patch, I
will plan on comitting it towards the end of the week.
select * from --DERBY-PROPERTIES joinOrder=FIXED
TENKTUP1 -- DERBY-PROPERTIES index=TK1UNIQUE1
, TENKTUP2 -- DERBY-PROPERTIES index=TK2UNIQUE1
where TENKTUP1.unique1 = TENKTUP2.unique1
order by TENKTUP1.unique1, TENKTUP2.unique1;
> 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,
> 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.
