enLibrary
Alias handling and ambiguous field detecting in Firebird 2.0
Dmitry Yemanov, 9-Nov-2007
Alias handling and ambiguous field detecting has been improved in Firebird 2.0.
Changes:
a) When an alias is present on a table, that alias must be used or no alias at
all, the tablename only is not valid anymore.
b) Fields without qualifier can now be used in a higher scope level. Own scope
level is checked first and ambiguous field checking is done on scope level.
Author:
Arno Brinkman
Examples:
a)
1.
When an alias is present it must be used or no alias at all must be used.
This query was allowed in FB1.5 and earlier versions:
SELECT
RDB$RELATIONS.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
but will now correctly report an error that the field
"RDB$RELATIONS.RDB$RELATION_NAME" couldn't be found.
Use this (preferred):
SELECT
R.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
or this statement:
SELECT
RDB$RELATION_NAME
FROM
RDB$RELATIONS R
2.
The statement below will now correctly use the FieldID from the subselect
and from the updating table:
UPDATE
TableA
SET
FieldA = (SELECT SUM(A.FieldB) FROM TableA A WHERE A.FieldID = TableA.FieldID)
In firebird it's possible to give an alias by an update statement, but by
many other database vendors this isn't supported. These SQL statements are
now better interchangeable between other SQL database products.
3.
This example didn't run correctly in FB1.5 and earlier:
SELECT
RDB$RELATIONS.RDB$RELATION_NAME,
R2.RDB$RELATION_NAME
FROM
RDB$RELATIONS
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = RDB$RELATIONS.RDB$RELATION_NAME)
If RDB$RELATIONS contains 90 records, it would return 90 * 90 = 8100 records,
but in FB2.0 it will correctly return 90 records.
b)
1.
This failed in FB1.5, but is possible in FB2.0:
SELECT
(SELECT RDB$RELATION_NAME FROM RDB$DATABASE)
FROM
RDB$RELATIONS
2.
Ambiguity checking in sub-selects. The query below did run on FB1.5
without reporting an ambiguity, but will report it in FB2.0:
SELECT
(SELECT
FIRST 1 RDB$RELATION_NAME
FROM
RDB$RELATIONS R1
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = R1.RDB$RELATION_NAME))
FROM
RDB$DATABASE