db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tars Joris <tjo...@inventivegroup.com>
Subject Incorrect ORDER BY caused by index?
Date Tue, 28 Oct 2008 10:52:02 GMT
Hi All,

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, but nonetheless,
this looks like a bug. I use Derby version 10.4.2.0.

As suggested on the Bug Guidelines, I'll check here first if it is really a bug or if it's
a duplicate (I couldn't find any mention of this behavior).

I have a table 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 (ant-script
is included).
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.

Thanks in advance,

Tars.

--
Tars Joris
Project Development Manager
Inventive Designers nv
http://www.inventivedesigners.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--



Mime
View raw message