[DERBY-3926] Incorrect ORDER BY caused by index

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.0
• Fix Version/s: 10.1.3.3, 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.2.0, 10.6.1.0
• Component/s: SQL
• Environment: Checked into 10.3. This will still go to 10.2 and 10.1
• Urgency: Normal
• Issue & fix info: High Value Fix
• Bug behavior facts: Wrong query result
• Assignee: Mamta A. Satoor
• Reporter: Tars Joris
• Created: 29/Oct/08 08:56
• Updated: 16/May/13 06:53
• Resolved: 12/Aug/09 16:23

Description

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

Issue Links

• depends upon: DERBY-3997 ORDER BY causes column to be returned
• is related to: DERBY-4105 NPE when the attached reproducible script is run using ij.
• relates to: DERBY-4240 An index cause SQL ORDER BY can't return correct result
• relates to: DERBY-4331 Join returns results in wrong order
• relates to: DERBY-6148 Wrong sort elimination when using permuted join order