From derby-dev-return-68157-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Wed Apr 22 16:18:11 2009 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 3323 invoked from network); 22 Apr 2009 16:18:11 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 22 Apr 2009 16:18:11 -0000 Received: (qmail 8803 invoked by uid 500); 22 Apr 2009 16:18:10 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 8739 invoked by uid 500); 22 Apr 2009 16:18:10 -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 8731 invoked by uid 99); 22 Apr 2009 16:18:10 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 22 Apr 2009 16:18:10 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.140] (HELO brutus.apache.org) (140.211.11.140) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 22 Apr 2009 16:18:10 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id B3C2B234C003 for ; Wed, 22 Apr 2009 09:17:49 -0700 (PDT) Message-ID: <829994744.1240417069731.JavaMail.jira@brutus> Date: Wed, 22 Apr 2009 09:17:49 -0700 (PDT) From: "Mamta A. Satoor (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (DERBY-3926) Incorrect ORDER BY caused by index In-Reply-To: <587695671.1225270724527.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-3926?page=3Dcom.atlassian= .jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1270= 1587#action_12701587 ]=20 Mamta A. Satoor commented on DERBY-3926: ---------------------------------------- I have worked on writing a junit test which is currently going to fail beca= use we are returning the data in incorrect order. I thought it would be use= ful to have the test for people to quickly run the test if they wanted to. = This junit test will not be part of any suite currently since the bug is no= t fixed yet. Putting it in the suite is going to make it fail everytime bec= ause the test is asserting that the data be returned in the correct order. = I will check that test in soon. It was painful to convert the setup script = provided for this jira into a junit test. The script is huge. I was able to= use Army's test converter DERBY-2151(it was extremely helpful because it a= tleast converted half of the script into junit test. My understanding is th= at the converter works on the older canon based master file. I think it tak= es sql delimited by ; from the canon file and assumes that next line is the= output of that sql. So, the converter skipped every other sql from my setu= p script. I ended up hand putting every other line which was skipped by the= converter. ) > Incorrect ORDER BY caused by index > ---------------------------------- > > Key: DERBY-3926 > URL: https://issues.apache.org/jira/browse/DERBY-3926 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.1.3.3, 10.2.3.0, 10.3.3.1, 10.4.2.0 > Reporter: Tars Joris > Attachments: derby-reproduce.zip > > > I think I found a bug in Derby that is triggered by an index on a large c= olumn: VARCHAR(1024). I know it is generally not a good idea to have an in= dex on such a large column. > I have a table (table2) with a column "value", my query orders on this co= lumn but the result is not sorted. It is sorted if I remove the index on th= at column. > The output of the attached script is as follows (results should be ordere= d on the middle column): > ID |VALUE |VALUE > ---------------------------------------------- > 2147483653 |000002 |21857 > 2147483654 |000003 |21857 > 4294967297 |000001 |21857 > While I would expect: > ID |VALUE |VALUE > ---------------------------------------------- > 4294967297 |000001 |21857 > 2147483653 |000002 |21857 > 2147483654 |000003 |21857 > This is the definition: > CREATE TABLE table1 (id BIGINT NOT NULL, PRIMARY KEY(id)); > CREATE INDEX key1 ON table1(id); > CREATE TABLE table2 (id BIGINT NOT NULL, name VARCHAR(40) NOT NULL, value= VARCHAR(1024), PRIMARY KEY(id, name)); > CREATE UNIQUE INDEX key2 ON table2(id, name); > CREATE INDEX key3 ON table2(value); > This is the query: > SELECT table1.id, m0.value, m1.value > FROM table1, table2 m0, table2 m1 > WHERE table1.id=3Dm0.id > AND m0.name=3D'PageSequenceId' > AND table1.id=3Dm1.id > AND m1.name=3D'PostComponentId' > AND m1.value=3D'21857' > ORDER BY m0.value; > The bug can be reproduced by just executing the attached script with the = ij-tool. > Note that the result of the query becomes correct when enough data is cha= nged. This prevented me from creating a smaller example. > See the attached file "derby-reproduce.zip" for sysinfo, derby.log and sc= ript.sql. > Michael Segel pointed out: > "It looks like its hitting the index ordering on id,name from table 2 and= is ignoring the order by clause." --=20 This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.