Hi Svein,

You said: "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 think this could be an interesting question to answer...

I thought that even without an order specified, the engine is supposed
to use the natural one. As Oracle has its ROWID, I think FB has its
RDB$DBKEY (or something like that) that is generated after the
insertions and stablished some order. So, if in regular selects I use to
obtain the same result set (in natural order) why not in subselects?
Aren't the rows returned in a top-down sequence, according to the
information in the system tables?

Why I suppose this a FB bug? Simple: the SQL statement is completely
clear, readable and understandable for everyone, but the engine hasn't
assumed that. In all those years, the languages tend to reach our
natural human language (or not?) and, I was misunderstood by FB :-( ...

You can suggest another construction for that query, as Lele did, but
that weird result will still remain.

This thread is still open for me. Thanks for your suggestions and
comments.

Fernando Deola
EFEX Systems Ltda.
Blumenau - SC - Brasil

-----Mensagem original-----
De: Svein Erling Tysvaer
[mailto:svein.erling.tysvaer@...]
Enviada em: sexta-feira, 20 de setembro de 2002 09:03
Para: ib-support@yahoogroups.com
Assunto: RE: [ib-support] Re: Found possible bug on FB 1.0 build 821

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.

To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/