db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [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.
Date Wed, 30 Jan 2008 21:39:34 GMT

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

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

    Attachment: d3279_ix2brnode_v1.patch

Attaching a follow-up patch, d3279_ix2brnode_v1.patch, that fixes the case for IndexToBaseRowNodes
which appear above the base table.  I didn't see any other cases where similar changes were
necessary (or rather, all other cases were handled by the _v1 patch).

This patch also adds some more test cases to InListMultiProbeTest, esp. to 1) test the case
where the index has fewer columns than the base table, and 2) test queries that have multiple
IN lists in them.  I decided to add the latter set of tests since Bryan asked about a similar
query in an earlier comment, and since cheese2.sql also includes a couple of queries with
multiple IN lists in the where clause (indirectly).

I ran derbyall and suites.All with ibm142 and saw no failures.

> 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_ix2brnode_v1.patch, 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.


Mime
View raw message