--- In firebird-support@yahoogroups.com, "Josef Gschwendtner"
<Josef.Gschwendtner@...> wrote:

>
> Hi,
>
> I wonder if the following behavior is a known problem?
>
> We have a stored procedure (SP) with a query (Q1) joining two tables (T1
> and T2).
> At the time the SP was made (compiled) T1 did contain a field named
> 'xx'.
> Field xx is one of the selected fields of Q1.
>
> Example:
> select xx, xy from t1, t2 where t1.f1 = t2.f1 into :xx, :xy
>
> Now the problem arises:
> =======================
> We have been able to add a new field "xx" in table T2 without any
> problem (I think adding new field "xx" in T2 should fail with an
> appropriate message).
>
> That there is a problem with this database we found out doing a
> backup/restore of this database.
> The restore failed because of some weird problems.
> After droping all SP's and inserting them with a script we found out
> about this ambiguous fields.
>
> Is this a known problem? Are there any plans to improve this behavior?

---

Lots of issues, lets address them one at a time.

Firstly, IB6 used to accept ambiguous queries, the problem was that
the results were unpredictable and could potentially change between
different versions.

Lets take a look at your query:

select xx, xy from t1, t2 where t1.f1 = t2.f1 into :xx, :xy

There are two problems with this. The first and most serious problem
is that you do not qualify your selected fields by the table or alias
to use. Whilst it is acceptable under the SQL standard, it is a really
bad idea for the reason you discovered. The second problem is that you
should use the SQL 92 join syntax which is much easier to read.

select t1.xx, t2.xy
from table1 t1
join table2 t2 on (t1.f1 = t2.f1)

If you explicitly state which field you want, you will never run into
problems. If anything, Firebird 2 is more strict in preventing
ambiguous results.

Ideally, Firebird should realise that adding xx to t2 will cause a
problem, but if you write the queries properly you wont have an issue
either way.

Adam