From derby-dev-return-68582-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Mon May 04 20:24:52 2009 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 50935 invoked from network); 4 May 2009 20:24:52 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 4 May 2009 20:24:52 -0000 Received: (qmail 49866 invoked by uid 500); 4 May 2009 20:24:52 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 49836 invoked by uid 500); 4 May 2009 20:24:52 -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 49828 invoked by uid 99); 4 May 2009 20:24:52 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 04 May 2009 20:24:52 +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; Mon, 04 May 2009 20:24:51 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 03E99234C003 for ; Mon, 4 May 2009 13:24:31 -0700 (PDT) Message-ID: <1147508183.1241468670986.JavaMail.jira@brutus> Date: Mon, 4 May 2009 13:24:30 -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: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-3926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12705726#action_12705726 ] Mamta A. Satoor commented on DERBY-3926: ---------------------------------------- Mike, thanks for your time on this jira. Yes, your understanding of my description is correct. I think the key here is that the outer table(table3) is returning more than one row and each one of those row is requiring us to look at the middle table (table2) which results into 3 scans on table2. So even though, table2.value has index on it, it is not helping in this case because of 3 different scans on table2. If it were just one scan on table2, then the index table2.value would have returned the rows to us in proper order. The 3 different scans would require a sorting on them to return the rows in sorted order. > 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 > Assignee: Mamta A. Satoor > Attachments: derby-reproduce.zip, script3.sql, script3WithUserFriendlyIndexNames.sql, test-script.zip > > > I think I found a bug in Derby that is triggered by an index on a large column: VARCHAR(1024). I know it is generally not a good idea to have an index on such a large column. > I have a table (table2) with a column "value", my query orders on this column but the result is not sorted. It is sorted if I remove the index on that column. > The output of the attached script is as follows (results should be ordered 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=m0.id > AND m0.name='PageSequenceId' > AND table1.id=m1.id > AND m1.name='PostComponentId' > AND m1.value='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 changed. This prevented me from creating a smaller example. > See the attached file "derby-reproduce.zip" for sysinfo, derby.log and script.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." -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.