anthony.bydeveldt wrote:

> --- In firebird-support@yahoogroups.com, Lester Caine <lester@...> wrote:
>> anthony.bydeveldt wrote:
>>> Just 1 more question guys!
>>>
>>> Can anyone tell me which SQL standard the ambiguity rule relating to table aliases was introduced?
>>>
>>> I understand that the rule wasn't enforced until firebird v2, but I wanted to know which SQL standard that was introduced into.
>>>
>>> I am planning on getting the developer to change the software based on it not being SQL standard compliant, as we will be using this software into the foreseeable future and we don't want to get stuck on a 2004 version database server.
>> That problem has little to do with 'standards compliance' and more to do with
>> 'which xxx field is correct'. Prior to applying the rule, if there were two or
>> more fields with the same name and you did not correctly alias the one you
>> wanted to use, it was selected a little at random, so if they had different
>> data in you did not know what you would get. Now you get a complaint if
>> Firebird is not sure which one to use .... even if the field has to contain
>> the same data since you have used it as the link between two tables, but that
>> is just consistency since it removes the need to prove that the field is the same.
>>
> I was referring specifically to the table alias rule that once a table was given an alias, that alias has to be used instead of the table name ie:
>
> select * from table t
> where table.col1 = 5
>
> used to work in fb1.5, but is no longer allowed under fb2.0 and throw an exception and should be:
>
> select * from table t
> where t.col1 = 5
>
> Is this something that was always part of the standard, or was it added in a later revision of the SQL standard?

If you think about it a little more you will see it is the same problem ... at
least from my view point. Is 'table.col1 = 5' actually 't' or a different
instance of 'table'. Easy to answer in your example, but add a self join on
'table' .... makes sense to remove the ambiguity in the simple case, since the
query could be expanded later and the problem may not be so easy to spot?

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php