--- In IB-Architect@egroups.com, Nando Dessena <nandod@d...> wrote:

> 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.
>
> I have been told that other RDBMS' throw errors in this situation,

while

> IB simply executes the statement giving back seemingly unpredictable
> results.
>
> I was wondering if this behaviour is somehow defined by the SQL
> standard, or there are reasons for executing ambiguous statements
> without displaying errors.

The SQL standard says that your statement should return a syntax
error.

In SQL92, Subclause 7.9 <query specification>, Syntax Rule 6 says:

"Each <column reference> directly contained in each <value expression>
and each <column reference> contained in a <set function
specification> directly contained in each <value expression> shall
unambiguously reference a column of T."

(T, in this rule, refers to the result of the table expression of the
query (basically, the result of the FROM, WHERE, GROUP BY, and HAVING
clauses).)

In order for a product to conform to the SQL standard, it must raise
an exception condition ("syntax error or access rule violation") if
there are any ambiguous references in the query.

Dull, dull, dull.
db