Hi,

Adding my 0.02...

> > At 11:21 AM 5/11/2003 +0000, you wrote:
> > >I think your sql is ambiguous and am surprised that Firebird
> > >doesn't reject it due to syntax.
> >
> > I agree with your diagnosis but, AFAIK, the ambiguous syntax will
> > only be rejected on a dialect 3 database. You can still write bad
> > queries in dialect 1.
> > :-))
> > h.
>
> Hi Helen!
>
> Admittedly, I forgot that there were people whom upgrade to Firebird
> 1.5 even though they still use dialect 1 (quite contrary to us who
> upgraded to dialect 3 immediately, but still are on 1.0.2). Though for
> 1.0.2, this problem exists for dialect 3 as well, i.e.
>
> SELECT A.FieldInTableA, TableB.FieldInTableB, A.FieldInBothTables
> from TableA A
> join TableB on TableA.PK = TableB.PK
> join TableB B on TableA.PK = TableB.FK

This should fail - the last line should read:

join TableB B on TableA.PK = B.FK

Once a table is aliassed, you should use the alias everywhere.

> prepares just fine. Even
>
> SELECT FieldInTableA, FieldInTableB, A.FieldInBothTables
> from TableA A
> join TableB on TableA.PK = TableB.PK
> join TableB B on TableA.PK = TableB.FK
>
> prepares OK. The only way to force the ambiguous field error, is to
> remove the table qualifier for FieldInBothTables.

This should fail as well. The first line should have alias "A" put
in front of the column name. And, of course, the last line is the
same :-)

> So, the question is then whether this has been fixed after 1.0.2. This
> thread suggest that this may not be the case (the original question
> never mentioned dialect 1). Anyone able to answer?

I'd say there are different problems - this, I believe fails:

select fieldinbothtables
from tablea join tableb on (...)

The other ones (like aliassing and not using it) is a second error.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com