You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Daniel John Debrunner (JIRA)" <ji...@apache.org> on 2007/12/14 22:47:43 UTC
[jira] Issue Comment Edited: (DERBY-3279) Derby 10.3.X sorts
VARCHAR column incorrectly when table is indexed and VARCHAR column
contains a value of '00000'
[ https://issues.apache.org/jira/browse/DERBY-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12551947 ]
djd edited comment on DERBY-3279 at 12/14/07 1:46 PM:
------------------------------------------------------------------------
Here's what I get for trunk, both of them look incorrect since the ORDEY BY has DESC?
ij> 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;
CHEE&|CHEESE_NAME |CHEESE_C&
------------------------------------
00000|EDAM |2.1111
54321|EDAM |8.5646
2 rows selected
ij> 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;
CHEE&|CHEESE_NAME |CHEESE_C&
------------------------------------
54321|EDAM |8.5646
AAAAA|EDAM |999.8888
2 rows selected
was (Author: djd):
What output do you get and what do you expect, seems to order correctly for me, though I'm using trunk.
> 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
>
> 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.