Library

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:

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

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.

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.

This failed in FB1.5, but is possible in FB2.0:

SELECT

(SELECT RDB$RELATION_NAME FROM RDB$DATABASE)

FROM

RDB$RELATIONS

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