[
https://issues.apache.org/jira/browse/DERBY-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563692#action_12563692
]
A B commented on DERBY-3279:
----------------------------
Thanks for running the tests, Ajay.
The removal of CHEESE_COST from the index means that the index now has a subset
of the columns from the base table. That in turn means that we have to
generate an IndexToBaseRowNode above the base table (which we won't do if the
index and the base table have the same columns). In my first patch I
overlooked this fact and thus did not propagate the call to
"adjustForSortElimination()" down to the base table beneath IndexToBaseRowNode.
To fix this I just had to add an appropriate call to IndexToBaseRowNode:
+ /**
+ * @see ResultSetNode#adjustForSortElimination
+ */
+ void adjustForSortElimination(RequiredRowOrdering rowOrdering)
+ throws StandardException
+ {
+ source.adjustForSortElimination(rowOrdering);
+ }
and the queries in cheese2.sql now sort correctly. I want to look a bit more
to see if there are any other cases where intermediary nodes are added above
FromBaseTable and thus need a similar change, and then I will post a follow-up
patch with the corresponding changes.
Thanks again for running more tests and reporting back!
> Derby 10.3.X ignores ORDER BY DESC when target column has an index and is
> used in an OR clause or an IN list.
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3279
> URL: https://issues.apache.org/jira/browse/DERBY-3279
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1
> Environment: Rational Application Developer 7.0.0.2 (Eclipse 3.2.2),
> J2RE 1.5.0 IBM J9 2.3 Windows XP
> Reporter: Ajay Bhala
> Assignee: A B
> Fix For: 10.4.0.0
>
> Attachments: cheese2.sql, d3279_v1.patch
>
>
> Running the following produces the error seen in Derby 10.3.X but not in
> 10.2.X nor in 10.1.X.
> Don't know if this related to DERBY-3231.
> First query is incorrectly sorted whereas the second one is okay when there
> is an index on the table.
> If the table is not indexed, the sort works correctly in DESC order.
> ------
> create table CHEESE (
> CHEESE_CODE VARCHAR(5),
> CHEESE_NAME VARCHAR(20),
> CHEESE_COST DECIMAL(7,4)
> );
> create index cheese_index on CHEESE (CHEESE_CODE DESC, CHEESE_NAME DESC,
> CHEESE_COST DESC);
> INSERT INTO CHEESE (
> CHEESE_CODE,
> CHEESE_NAME,
> CHEESE_COST)
> VALUES ('00000', 'GOUDA', 001.1234),
> ('00000', 'EDAM', 002.1111),
> ('54321', 'EDAM', 008.5646),
> ('12345', 'GORGONZOLA', 888.2309),
> ('AAAAA', 'EDAM', 999.8888),
> ('54321', 'MUENSTER', 077.9545);
> SELECT * FROM CHEESE
> WHERE (CHEESE_CODE='00000' OR CHEESE_CODE='54321') AND CHEESE_NAME='EDAM'
> ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC;
> SELECT * FROM CHEESE
> WHERE (CHEESE_CODE='AAAAA' OR CHEESE_CODE='54321') AND CHEESE_NAME='EDAM'
> ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
