Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 77210 invoked from network); 14 Dec 2007 21:27:07 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 14 Dec 2007 21:27:07 -0000 Received: (qmail 73593 invoked by uid 500); 14 Dec 2007 21:26:56 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 73558 invoked by uid 500); 14 Dec 2007 21:26:56 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 73548 invoked by uid 99); 14 Dec 2007 21:26:56 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Dec 2007 13:26:56 -0800 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO brutus.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Dec 2007 21:26:52 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 3E18071420E for ; Fri, 14 Dec 2007 13:26:43 -0800 (PST) Message-ID: <11969631.1197667603251.JavaMail.jira@brutus> Date: Fri, 14 Dec 2007 13:26:43 -0800 (PST) From: "Ajay Bhala (JIRA)" To: derby-dev@db.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' In-Reply-To: <9506790.1197667363355.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-3279?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ajay Bhala updated DERBY-3279: ------------------------------ Description: 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; was: 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. ------ 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; > 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.