Hi,

> > > Can someone explain why FB performs the sub-select for each row in the
> > main
> > > select even if the sub-select is not correlated and will always
> return the
> > > same resultset ?
> >
> > Internally the IN is converted to the same as a EXISTS function :
> >
> > FieldX IN (SELECT FieldY FROM TableA)
> >
> > becomes :
> >
> > EXISTS(SELECT FieldY FROM TableA WHERE FieldY = FieldX)
> >
> > Changing this has been planned for a future version.
>
> Arno, Yaffil team tried to change behaviour of not correlated
> sub-queries and IIRC after loooong struggle against induced bugs
> achieved some success.

I think this will need a good investigation in how to solve this.
I think the best way is making a memory list from the sub-query and the
above context can then use a index.

> Since Yaffil code is now available to you...

Well, then you know more then me. I don't have any access (that i'm aware
of) to the Yaffil code.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info