At 10:24 PM 21/09/2005 -0600, you wrote:

>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 (1,'test',NULL)
>
>Now I want to select the test_id, name, and name of the parent so I did
>this:
>
> select test.test_id,test.name,parent.name 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 I
>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 but
>I'm selecting the test.test_id and test.name so I should get the right
>values but I don't!
>
>If I change the join to use an alias for both tables then it will work
>so something like this:
>
> select t.test_id,t.name,p.name 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 don't
>see why it should be needed! I'm using Firebird CS 1.5.2 4731 on SuSE
>Linux 9.2.

Oh, but it is required, both by Firebird and by the standard. Many DBMS's
(including InterBase) are sloppy about it and are happy to let you write
ambiguous queries and get (often undetectable) wrong results. Firebird 1.5
doesn't try to guess anything. Wherever you have correlated elements
(whether by joins or by subqueries) you'll get spanked if you omit
identifiers. With self-referencing structures, there never was any
choice: it's always been "alias or die".

Learn to love it.

./hb