Hi guys !

This weekend I wrote by mistake a query without the alias on the fields.
Take a look

Firebird SS 1.5.0.4290 on WinXP (I know I should upgrade to the other
build... But AFAIK the problem is with ISQL and the needed to do <ENTER>
<ENTER> to execute the statments)

I tested on a remote server runnning FB 1.0.3 and got the same results...

create table Table1(id integer, Number integer, Name varchar(20));
create table Table2(id integer, Table1ID integer, Number varchar(20));

insert into Table1 values (1, 1, 'ABC');
insert into Table1 values (2, 2, 'DEF');
insert into Table1 values (3, 3, 'GHI');

insert into Table2 values (1, 1, '10');
insert into Table2 values (2, 2, '25');
insert into Table2 values (3, 3, '20');
insert into Table2 values (4, 3, 'Km 26');


this Query

select
*
from
Table1 T1 join
Table2 T2 on (T2.Table1ID = T1.ID)
where
Number between 2 and 3

gives me:

Conversion error from String "Km 26"

I know i forgot to put alias on the where clause, to get the correct
resultset I should make it:

select
*
from
Table1 T1 join
Table2 T2 on (T2.Table1ID = T1.ID)
where
T1.Number between 2 and 3

That's ok, but don't should FB tell me that I have an "Ambiguous Field" in
this query

If I write this:

Select
Number
from
Table1 T1 join
Table2 T2 on (T2.Table1ID = T1.ID)

I get

10
25
20
km 26

Wich is values from table Table2. If I recall correct on FB 1.0 was made a
correction to prevent users to write bad SQL that uses Fields from tables
with the same name without an alias... This correction broke some
applications (IBReplicator, IBConsole) that don't put alias on all
queries... Or I am missing things here...

I know how to correct the query, i just forgot to put alias... What I want
to know is if it's the correct behaviour.

In this case FB should give an error right ????

I just did another test...

In ISQL I got an warning:
SQL warning code = 204
-Ambiguous field name between table TABLE1 and table TABLE2
-NUMBER

In IBOConsole and IBConsole no warnings...

See you !


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004


[Non-text portions of this message have been removed]