Hi,

> I had problems with a query that gave false results, then I noticed
> that i hadn't qualified one of the fields in coalesce in select.
> Unfourtunately firebird didn't warn about it and just used one of the
> tables (listed at the end of from clause) where a field with this
> name could be found.
>
> The tables and query are too complex to show here, so I put a
> simplified version here:
>
> table1
> ------
> some_field
>
> table2
> ------
> other_field
>
>
> select coalesce (t2_alias1.field2 + other_field)
> from table1 t1
> left join table2 t2_alias1 on (...)
> left join table2 t2_alias2 on (...)
>
> The result showed that value from t2_alias2 appeared into coalesce.

You should also have the problem without COALESCE.
At least i can reproduce it very easy with :

select
other_field
from
table1 t1
left join table2 t2_alias1 on (...)
left join table2 t2_alias2 on (...)


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81