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 sorts VARCHAR column incorrectly when table is indexed and VARCHAR column contains a value of '00000'
Date Wed, 23 Jan 2008 22:05:35 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_v1.patch

Attaching a first attempt at resolving this issue.

The problem as described above only occurs in situations where preprocessing/optimization
eliminates a sort.  There is already a method called "adjustForSortElimination()" defined
in ResultSetNode, so this patch creates an alternate signature that takes an OrderByList as
an argument.  Then, in the case where sort elimination occurs, we will search a table's predicates
to see if any of them is a multi-probing predicate.  If so, we check to see if the ORDER BY
that was eliminated required a DESCENDING sort on the multi-probe column, and we adjust for
that (if needed) by sorting the IN list values into descending order at execution time.  See
code comments for details.

I ran derbyall and suites.All with this patch applied and saw no failures.  Review comments
are appreciated.

> Derby 10.3.X sorts VARCHAR column incorrectly when table is indexed and VARCHAR column
contains a value of '00000'
> ------------------------------------------------------------------------------------------------------------------
>
>                 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
>         Attachments: 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