> -----Original Message-----
> From: Nando Dessena [mailto:nandod@...]
> Sent: Jueves 11 de Enero de 2001 9:41
>
> Hello,
> I noticed that IB happily executes an ambiguous query of the form:
>
> select ...
> from orders o
> left join customers c1 on (o.customer_id1 = c1.id)
> left join customers c2 on (o.customer_id2 = c2.id)
> where somefield = 'somevalue'
>
> Assuming "somefield" exists in both the customers and the orders table,
> the query is ambiguous unless the reference in the where clause is
> qualified.

You will want to have a peek at another bug in the same field: ambiguity.
Other engines reject it:
http://sourceforge.net/bugs/?func=detailbug&bug_id=123133&group_id=9028
and in the meantime, you could document your observation, logging a bug
against Firebird (don't know if you want to append to the same bug, but log
it, please).

> I have been told that other RDBMS' throw errors in this situation, while
> IB simply executes the statement giving back seemingly unpredictable
> results.

Probably, since I've found that some ambiguities that IB let to go are
trapped by MsSql, for example. I remember I handled a case of a person that
complained for the incorrect results. (Maybe I should log it or append to
your bug report when you do it at Firebird.) The problem was a JOIN/ON
clause with a WHERE clause to filter even more records, but the WHERE was
put just on the field that served to do the JOIN and was called the same of
both tables. IB didn't complain. It simply assumed one of the tables should
be used and voila. With [inner] join, there's no problem, but it was a LEFT
JOIN, so the difference was dramatic. Once I told the person to qualify the
field with the desired table name, the problem disappeared. But it's a nasty
trap IMHO.

> I was wondering if this behaviour is somehow defined by the SQL
> standard, or there are reasons for executing ambiguous statements
> without displaying errors.
> Otherwise, we could think about adding such a "feature".

I think that you already got the answer from Diane: it's not standard
behavior.

C.