At 02:40 AM 29/06/2007, you wrote:

>I have a db using firebird-1.0.0.796. I created a self union join and
>got strange result.
>
>create view bb (bb_pid, bb_2) as
> select a_pid, a2 from a
>union
> select a_pid, a3 from a

Drop the view and redefine it as

create view bb (bb_pid, bb_2) as
select a1.a_pid, a1.a2 from a a1
union ALL
select a2.a_pid, a2.a3 from a a2

>a2 and a3 are computed field. a_pid is the primary key.
>
>Table c
>c_pid c_bb_pid
>1 4
>2 4
>
>select c_pid, c_bb_pid fromc c, bb
>where c.c_bb_pid = bb.bb_pid ( RETURN 2 only)

Use proper join syntax (note the table qualifiers):

select
c.c_pid, c.c_bb_pid from c
join bb
on c.c_bb_pid = bb.bb_pid

>select c_pid, c_bb_pid fromc c, a
>where c.c_bb_pid = a.a_pid ( RETURN 1 & 2 )

select c.c_pid, c.c_bb_pid
from c
join a
on c.c_bb_pid = a.a_pid

>Is that I do something wrong ? Please advice.

See how it goes using the fully qualified syntax for all of these
examples (including the last one!)

In Fb 1.0.x the DSQL processor would allow these improperly qualified
table references and the possibility of unexpected results. With
later versions, the rules have become progressively tougher, making
it harder to have improper syntax accepted. Without these
safeguards, you need to be just that much more careful with the
language in order to get the results you expect.

That is a *very* old distro you are using there, a real museum piece
from the beginning of 2002: you might be just about the last person
left using it! <g> There were two more sub-releases of Firebird 1.0
after that and, since then, Firebird 1.5 and four sub-releases, and
Firebird 2.0 and one sub-release (so far). Even Fb 2.1 is about to
be beta-released and Fb 3.0 is in development. All of that
represents more than five successive years of bug-fixing and improvements....

./heLen