--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 04:41 AM 22/08/2007, Ed Dressel wrote:
> >I have the following SQL statement:
> >
> > select * from ClientINfo
> > where Client_Id in (Select Max(Client_ID) from ClientInfo)
> >
> >where Client_ID is the primary key of ClientInfo.
> >
> >But the SQL above is very slow (almost 2 minutes).
>
> That's because the query is DAFT. The IN() predicate is is most
> certainly not interchangeable with "=", which is the mistake you are
> making here. MAX() returns one result per aggregate grouping, not a
> set. Use IN() for comparing the search argument with a SET of values
> and only then when there's no better way to do it.

To be fair, although in this instance it is a bit silly to use IN when
the subquery can only return a single value, and it is also silly to
refer to a subquery like this when it is the same table and there is
no correlation between the records, Firebird is not able to optimise
some fixed subqueries.

The current optimiser would never convert:

select ID
from Maintable
where SubqueryID in (select ID from SubqueryTable)

To (in PSQL):

for select ID from SubqueryTable
into :SubqueryID
do
begin
for select ID from MainTable where SubqueryID = :SubqueryID
into :ID do
begin
suspend;
end
end

Adam