Hi Svein,

> --- In firebird-support@yahoogroups.com, "Martijn Tonies" wrote:
> > > 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
>
> Arrgh, Opera is a decent browser, but doesn't debug my comments very
> well! Of course I used B.FK, both for this one and the other query.

:-)

> > 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.
>
> Fb 1.0.2: It accepts it without aliasing because it is a field only
> present in TableA (hence no ambiguity). The field in TableB also only
> exists in TableB, but since two instances of that table is used within
> the query, I think the query is ambiguous and a prepare ought to fail

The ambiguity is clared by aliassing one of the tables B. Cause the
second instance should then be addressed with B.<column> always.
That said, "FieldInTableB" should be taken from the first occurence
of TableB. (not commenting on Firebirds behaviour)

Why? Because the SQL standard says that IF you use an alias,
you should use it always. Therefor, this should fail as well:

select employee.empid
from employee e
where e.emid < 10

(or whatever)

> (B always refer to the second instance of TableB, but referencing
> TableB directly may use either instance). Though Firebird 1.0.2
> happily prepares even when using dialect 3.
>
> > > 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.
>
> Good, we agree on that. But is it an error that Firebird 1.5 dialect 3
> reports, or does it just give wrong results as was the case with
> Jürgen who started this thread? Just like I expect you to prefer Nidar
> to discover they put salt into their Bamsemums rather than leaving it

Bamsemums... goooooooood.

> to the customers to taste which contain sugar and which doesn't, I
> prefer Firebird to tell me about my mistakes rather than answer my
> faulty input with faulty recordsets.

With regards,

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