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