[jira] Updated: (DERBY-3279) 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.

["A B (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

A B updated DERBY-3279:
-----------------------

    Attachment: releaseNote.html

I don't know if fixes for wrong results *regressions* really need a release note or not--esp. if it 's just an ordering issue--but I'm attaching one here anyway.  I am *not* checking the "Existing Application Impact" box as I don't know what the rules are for regressions.  If it is agreed that release notes are good for regression fixes, as well, then someone can check the appropriate box to have the release note picked up by the generator tool.  Note: the html file will have to be "scrubbed" in order to play nicely with the release note tool.

> 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.3.2.2, 10.4.0.0
>
>         Attachments: cheese2.sql, d3279_10_3_merge.patch, d3279_ix2brnode_v1.patch, d3279_v1.patch, releaseNote.html
>
>
> 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.