Dmitry Yemanov wrote:

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

I thought was converted _exactly_ to EXISTS.

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

Yep.. but it's the majority of the cases as I see the use of in (sub-select)

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

Ok. understood.

I get in this discussion because I thoght that the Arno's proposition to
execute first the sub-select to make a list of values should apply to
this case too...

thanks for your time !

>
>Dmitry
>
>
>

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br