Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <461132445.1247144714820.JavaMail.jira@brutus>
Date: Thu, 9 Jul 2009 06:05:14 -0700 (PDT)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Resolved: (DERBY-4240) An index cause SQL ORDER BY can't
 return correct result
In-Reply-To: <902931716.1242876765721.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-4240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor resolved DERBY-4240.
------------------------------------

    Resolution: Duplicate

duplicate of DERBY-3926.

> An index cause SQL ORDER BY can't return correct result
> -------------------------------------------------------
>
>                 Key: DERBY-4240
>                 URL: https://issues.apache.org/jira/browse/DERBY-4240
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.2.0
>            Reporter: Simon Meng
>            Assignee: Mamta A. Satoor
>
> Following snippet  is a SQL example program. It can reproduce a database issue. 
> DROP TABLE test1;
> DROP TABLE test2;
> CREATE TABLE test1 (id BIGINT NOT NULL, name VARCHAR(255), PRIMARY KEY (id));
> CREATE TABLE test2 (entity_id BIGINT, rel_id BIGINT);
> CREATE INDEX idx_test2 ON test2 (entity_id);
> INSERT INTO test1 (id, name) VALUES (102, 'Tom');
> INSERT INTO test1 (id, name) VALUES (1, null);
> INSERT INTO test1 (id, name) VALUES (103, 'Jerry');
> INSERT INTO test1 (id, name) VALUES (101, 'Pupy');
> INSERT INTO test2 (entity_id, rel_id) VALUES (1, 102);
> INSERT INTO test2 (entity_id, rel_id) VALUES (1, 101);
> INSERT INTO test2 (entity_id, rel_id) VALUES (1, 103);
> SELECT t1.id, t1.name FROM test2 t2 INNER JOIN test1 t1 ON t2.rel_id = t1.id WHERE t2.entity_id = 1 ORDER BY t1.id ASC;
> The expected result should be 
> ID                  NAME
> --------------------------
> 101                Pupy
> 102                Tom
> 103                Jerry
> When running the program, I got below result.
> ID                  NAME
> --------------------------
> 102                Tom
> 101                Pupy
> 103                Jerry
> The result is obviously wrong. Using ORDER BY ASC does not get expected result. I found ORDER BY DESC works fine.
> Note: there is an index (idx_test2). This index affects the SQL query. If the index is dropped, ORDER BY ASC can return correct result..

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.