RE: [Firebird-devel] wrong column in query result

[Claudio V. C. <cv...@us...>]

Achim Kalwa wrote:
>
> The results for column "ID" is taken from table COLOR instead of
> PARTS.
>
> Firebird 1.5 RC1, Database is dialect 1
> Any hints?

Achim:
Is there anything new?
InterBase(R) has always given "random" results with ambiguous field names.
Most people aren't aware they may be looking at the wrong report or spoiling
their stored data. To be accurate, there's no randomness: the effect of
pushing/popping pointers from stacks while DSQL analyzes a statement makes
the table choosen to be totally implementation dependent when a field exists
in more than one used table. Don't rely on that!
Originally, the engine banned those constructions always. However, later it
was discovered that legacy ODBC drivers(used with IB4 and IB5) couldn't work
because they made internal metadata queries with ambiguous fields, so Ann
had to let dialect 1 turn a blind eye to ill-formed queries. Dialect 3
shouldn't accept your query. There are basically two disastrous cases:

1.- select id from t1 left join t2 on t1.id=t2.id;
What "id" do you want? Since it's an outer join, the output may be very
different: in one case, you may get more nulls than in the other.

2.- select repeated from t1 left join t2 on t1.id=t2.id;
Here, "repeated" is not used in the join condition, but it's common to both
tables. Since it's not a join field, it may contain different information in
each table.

C.
--
Claudio Valderrama C.
Consultant, SW developer.
www.cvalde.com - www.firebirdSql.org