[ https://issues.apache.org/jira/browse/DERBY-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563676#action_12563676 ]
Ajay Bhala commented on DERBY-3279: ----------------------------------- AB, thank you for the patch file. I patched the source from 10.3.2.1 with the file and re-ran my queried above. All seems well. However when I ran the queries in the attached file, I saw some incorrect sorting. The queries are similar to the original posting save for the fact that CHEESE_COST is neither in the index nor in the ORDER BY clause. > 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.