Adam wrote:

> 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
>
> select a.id
> from tablea a
>
> or
>
> 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.

But its not ambiguous. There are two tables in the from part of the
select. One named test and the other parent (which is an alias to the
test table) and so you should have two columns of test_id but one for
the table test and the other for parent and I explicitly specify in the
select that I want the test.test_id and not parent.test_id so there is
no ambiguity that I can see. Added confirmation is that it works
properly in the other databases I've tried and that Firebird doesn't
trigger the abiguity error that it does now when it thinks its ambiguous.

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

It should not be needed and I suspect the SQL specs would agree with me
on this and that Firebird is getting it wrong. Or perhaps this is a
case where the SQL specs are themselves ambiguous but I still think that
Firebird is getting it wrong. Perhaps 2.0 would handle this properly?

--
Brad Pepers
brad@...