Subject Re: Odd problem
Author Adam
--- In, Brad Pepers <brad@l...>
> I've found an odd thing in Firebird. I have a table that like this:
> create table test (
> test_id integer not null,
> name varchar(30) not null,
> parent_id integer,
> primary key (test_id),
> foreign key (parent_id) references test (test_id)
> );
> I insert one record into the table:
> insert into test (test_id,name,parent_id) values
> Now I want to select the test_id, name, and name of the parent so I
> this:
> select test.test_id,, from test left join
> test parent on test.parent_id = parent.test_id;
> This doesn't work though! It does on Sybase and PostgreSQL and if
> select * instead of specifying the fields then I see it all but of
> course the parent aliased ones are null since the parent id is null
> I'm selecting the test.test_id and so I should get the
> values but I don't!
> If I change the join to use an alias for both tables then it will
> so something like this:
> select t.test_id,, from test t left join test p
> on t.parent_id = p.test_id
> But this is not required for the other databases I've tried and I
> see why it should be needed! I'm using Firebird CS 1.5.2 4731 on
> Linux 9.2.
> --
> Brad Pepers
> brad@l...


It doesn't work because it is ambiguous (in FB 1.5). FB 1.5 allows
the following statement

select id
from tablea a

I know that FB 2 works differently. In FB 2, if you define a table
alias, then you can not refer to it other than that alias, so the
above query would need to become

from tablea a


select id
from tablea

Your case looks like an extension to this. It is doing a join to
itself, and would therefore have two fields named test_id. In your
case, it chooses the "wrong" one.

The work around is simple, use aliases for both tables as you have
done in your second example.