db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Simon Meng (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4240) An index cause SQL ORDER BY can't return correct result
Date Thu, 21 May 2009 03:32:45 GMT
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


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.


Mime
View raw message