I consider this a bug in the program, not in the engine. The subselect is
executed once for each potential match and without an order by there is no
guarantee that the same record is skipped for every execution. Suppose the
FONE table in the case of

>SELECT
> P.ID_PESSOA,
> F.NR_FONE
> FROM
> PESSOA P
> INNER JOIN FONE F
> ON (F.ID_PESSOA = P.ID_PESSOA AND
> F.NR_FONE NOT IN
> (SELECT
> SKIP 1 FF.NR_FONE
> FROM FONE FF
> WHERE
> FF.ID_PESSOA = P.ID_PESSOA))

contained

ID_PESSOA NR_FONE
1 35252
1 56533

Then when checking the first FONE entry the subselect might return the
records in the order 35252, 56533 when checking 56533 and the opposite
order 56533, 35252 when checking 35252 resulting in none of them being
returned. And in the opposite case both rows may be returned. Maybe
Firebird is consistent in the way it return records even without an ORDER
BY, but I doubt there is any rules saying that it has to be.

I would rather consider it a "bug" that Firebird allows subselects
following a IN clause, since it in most (all?) cases can be done more
efficiently either by doing it the way Lele suggests or by using EXISTS.
But I guess lots of applications depend upon this and maybe it is part of
some SQL standard.

Set
-using IN only with constants

At 10:15 20.09.2002 +0400, you wrote:

> > But it seems that the bug involving the FIRST clause in subqueries
> > is really confirmed. Unfortunatelly, it's not listed in the open
> > bugs nor in release notes.
>
>The reason could be the following. FIRST/SKIP makes sense only when the
>result set is ordered. Since the ORDER BY clause isn't allowed in select
>expressions (i.e. subqueries, views, etc), the engine may simply ignore
>FIRST/SKIP in such constructs. Just a guess. We should decide whether it's
>right or not before considering it an obvious bug.