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:[EMAIL PROTECTED] > Sent: Wednesday, October 29, 2008 4:02 AM > To: Derby Discussion; [EMAIL PROTECTED] > 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. > -- >
