"Alexandre Benson Smith" <iblist@...> wrote:

>
> One thing that most newcomers to FB strikes is
>
> select
> *
> from
> A
> where
> AI.B_ID in (select B.B_ID from B where B.B = 'X')
>
> This query are treated as correlated, but in fact aren't there is some
> possibility to solve the both problems (infinite loop and this) at once ?

They are different. IN _is_ a correlated subquery, because it's being
converted to this:

exists (select 1 from B where B.B = 'X' and AI.B_ID = B.B_ID)

(not exactly EXISTS, but something similar)

This allows to use an index for the subquery link.

> The query could be rewriten as:
> select
> A.*
> from
> A
> join B on (B.B_ID = A.B_ID)
> where
> B.B = 'X'

Only if B_ID is a primary key. Otherwise, this is completely wrong.

No doubt, both parser and optimizer should be able to perform the required
transformations. But this is completely unrelated issue, sorry.


Dmitry