db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: Incorrect ORDER BY caused by index?
Date Wed, 29 Oct 2008 12:45:38 GMT

Ok,

So the fact that even on a single table, it looks like your results are in
order of your identity index and it ignores the order by clause.

Your second index on the value doesn't appear to be used unless it's a
filter before the join and then the join uses the identity index.


> -----Original Message-----
> From: Tars Joris [mailto:tjoris@inventivegroup.com]
> Sent: Wednesday, October 29, 2008 4:02 AM
> To: Derby Discussion; msegel@segel.com
> Subject: RE: Incorrect ORDER BY caused by index?
> 
> 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