[DERBY-4240] An index cause SQL ORDER BY can't return correct result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.4.2.0
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Repro attached
• Bug behavior facts: Wrong query result
• Assignee: Mamta A. Satoor
• Reporter: Simon Meng
• Created: 21/May/09 03:32
• Updated: 09/Jul/09 13:05
• Resolved: 09/Jul/09 13:03

Description

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..

Issue Links

• is related to: DERBY-3926 Incorrect ORDER BY caused by index