At 10:31 06/04/2004 +1000, you wrote:

>At 01:48 PM 5/04/2004 -0300, you wrote:
> >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)
>
>That bug got fixed when the Win32 kit was replaced. So...

Hi Helen,

I know it... I am just justifying why I use the "wrong" build FB... :) this
bug was not annoying me...

>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
>
>Not with a cross-join. Select * on a joined set is a cross-join.
>You'd have got the warning if you had either
>Select t1.* or select t2.*

Helen,

I have two queries, I know one of then was bogus beacuse I forgot to put
the alias... What surprises me, and the reason to post this message, is...
I think FB should except on this type of query:

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


The problem that I say is ot with the "*" but in the WHERE clause... in the
WHERE I forgot to put the table alias... so FB just assume the "Number" i
have put on the where is the "Number" of Table2 when I want the "Number" of
Table1. Of course teh server has no mean to know if I want the "Number"
column from table1 or table2, so I expected an exception here.

> >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...
>
>That query gives me an exception with Fb 1.0.3 using the Fb 1.0.3
>client. Are you using the correct client?

Yes and No :)

on My machine I have FB 1.5 Client.

I tried it on Fb 1.5 Server on Windows and Linux, since I don't get any
error I tried to connect to a FB 1.03 Server with my machine that has
client 1.5. The results was the same.

> >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 ????
>
>It does.

When I use ISQL I got a WARNING

On IBConsole and IBOConsole I got no errors/warnings (are those cliente
tools ignoring warnings ????)

> >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...
>
>It seems to be another indication that you are using the wrong client
>library...while isql is finding the right client library.

I installed FB using "compatibility mode" (legacy mode ? don't remember the
correct termilogy) to create a gds32.dll on windows\system (system32 in my
case)

The file version from gds32.dll is 6.3.0.4290, Product Version 1.5.0.4290
\program files\firebird\bin\fbclient.dll version is 1.5.0.4290 Product
Version is 1.5.0.4290

So I think I have the correct client version installed, at least for my
first test that was on a FB 1.5 Server.

The second test was on a FB 1.0.3 server, the results are the same...

>btw, re-read your original message to see why I'm confused about whether
>this posting is discussing Fb 1.5 or Fb 1.0.3 behaviour.

I am sure I didn't made myself clear (again). I use FB 1.5. When I meet
this problem (forgot to put the alias on the query) I remembered that I
read somewhere (FB 1.0 release notes ?) that FB behaviour is diferent from
IB 6.0 with respect to ambiguous fields in a query. Since I got no error
with FB 1.5 I tried on FB 1.0.3.

What I think is:

The Client Tool (IBConsole, IBOConsole, Marathon) ignores the warning
(don't know if client side undertand diferent severities from
warning/error) generated by the server.

but when I execute a query with ambiguous field name, and the server
assumes one of those ambiguous fields to return me.

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


Table1 and Table2 has "Number" fields, this query returns the Table2.Number
field.

>/heLen

Thank you for your time... And I hope I make myself clear this time... :)

Forgot to metion Dialect 1 database.....


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]