Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Reply-To: <msegel@segel.com>
From: <derby@segel.com>
Sender: "Michael Segel" <msegel@segel.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: Incorrect ORDER BY caused by index?
Date: Tue, 28 Oct 2008 19:29:19 -0500
Organization: MSCC
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <98F519CDC2FA6146AE00069E9A1D91FD4A46AAEEF8@erganix.dc.intranet>
thread-index: Ack46zXWsVpLLZ2oQwWYJsyEcIQ+cgAbhJBg
Message-Id: <20081029003103.833825E488@dbrack01.segel.com>

Sorry to top post, it's a bit quicker and I'm going to pull things from your
post to show you something. I'm also reformatting your code to make it
easier to read... 

Lets take a look at your tables...

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);

Note:
On your second table the primary key/index is a compound index of the id and
name columns.

Lets look at your select statement:

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;

Lets rewrite this... First you don't need the third column.


SELECT A.id, B.value 
FROM   table1 A, table2 B, table2 C
WHERE  table1.id=B.id 
AND    table1.id=C.id 
AND    B.name='PageSequenceId'
AND    C.name='PostComponentId' 
AND    C.value='21857' 
ORDER BY B.value;


If you run this code, do you get the same result?

It looks like its hitting the index ordering on id,name from table 2 and is
ignoring the order by clause.

If you remove the index on table2, then you're joining the table(s) on id
alone.

One thing about your table join. Joining with table1 doesn't make sense.
You don't need it.

You can reduce the query to the following:
SELECT B.id, B.value 
FROM   table2 B, table2 C
WHERE  B.id = C.id
AND    B.name='PageSequenceId'
AND    C.name='PostComponentId' 
AND    C.value='21857' 
ORDER BY B.value;

What results do you get running the rewrites? 


> -----Original Message-----
> From: Tars Joris [mailto:tjoris@inventivegroup.com]
> Sent: Tuesday, October 28, 2008 5:52 AM
> To: derby-user@db.apache.org
> Subject: Incorrect ORDER BY caused by index?
> 
> 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.
> --
>