db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tars Joris <tjo...@inventivegroup.com>
Subject RE: Incorrect ORDER BY caused by index?
Date Wed, 29 Oct 2008 09:02:27 GMT
Hi Michael,

Thanks for your reply. See my responses inline.

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

You're right, this was done to verify that at least the condition m1.value='21857' was satisfied.

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

I had to rewrite it before it would run:
SELECT A.id, B.value
FROM   table1 A, table2 B, table2 C
WHERE  A.id=B.id
AND    A.id=C.id
AND    B.name='PageSequenceId'
AND    C.name='PostComponentId'
AND    C.value='21857'
ORDER BY B.value;

The result was still wrong:
ID                  |VALUE
---------------------------
2147483653          |000002
2147483654          |000003
4294967297          |000001

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

You're right. It doesn't make sense in this simplified example. But in fact table1 contains
several more columns, of which one more is selected (which isn't present in table2).

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

It is still wrong (the output is the same as above).

I guess the safest workaround would be to drop the index on the value-column, with a possible
performance impact.

Kind regards,

Tars.

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



Mime
View raw message