SQL migration issues

DDL

Views made updatable via triggers no longer perform direct table operations

In former versions, a naturally updatable view with triggers passed the DML operation to the underlying table and executed the triggers as well. The result was that, if you followed the official documentation and used triggers to perform a table update (inserted to, updated or deleted from the underlying table), the operation was done twice: once executing the view's trigger code and again executing the table's trigger code. This situation caused performance problems or exceptions, particularly if blobs were involved.

Now, if you define triggers for a naturally updatable view, it becomes effectively like a non-updatable view that has triggers to make it updatable, in that a DML request has to be defined on the view to make the operation on the underlying table happen, viz.

if the view's triggers define a DML operation on the underlying table, the operation in question is executed once and the table triggers will operate on the outcome of the view's triggers,
if the view's triggers do not define any DML request on the underlying table then no DML operation will take place in that table.

Important: Some existing code may depend on the assumption that requesting a DML operation on an updatable view with triggers defined would cause the said operation to occur automatically, as it does for an updatable view with no triggers. For example, this "feature" might have been used as a quick way to write records to a log table en route to the "real" update. Now, it will be necessary to adjust your view trigger code in order to make the update happen at all.

New reserved words (keywords)

A number of new reserved keywords are introduced. The full list is available in a chapter of its own in the accompanying Release Notes and also in Firebird's CVS tree in /doc/sql.extentions/README.keywords. You must ensure that your DSQL statements and procedure/trigger sources do not contain those keywords as identifiers.

Note: In a Dialect 3 database, such identifiers can be redefined using the same words, as long as the identifiers are enclosed in double-quotes. In a Dialect 1 database there is no way to retain them: they must be redefined with new, legal words.

(v.2.1) Malformed UTF8 strings and text blobs are no longer allowed. This affects not just user data but also the metadata stored in the system tables. There is a metadata script to enable you to upgrade the stored sources of triggers, stored procedures, views, constraints, etc. Please consult the v.2.1 Release Notes for instructions.

Important: In order to have the metadata correctly stored in the database, i.e., in UTF8, it is essential to ensure that DDL statements are transliterated into the connection character set. Mixed usage of the NONE and other character sets is not recommended as it can lead to to unexpected runtime errors.

`CHECK` constraint change

Formerly, CHECK constraints were not SQL standard-compliant in regard to the handling of NULL. For example, CHECK (DEPTNO IN (10, 20, 30)) should allow NULL in the DEPTNO column but it did not.

In Firebird 2.0, if you need to make NULL invalid in a CHECK constraint, you must do so explicitly by extending the constraint. Using the example above:

 CHECK (DEPTNO IN (10, 20, 30) AND DEPTNO IS NOT NULL)

DML

Changed ambiguity rules in SQL

A. Brinkman

In summary, the changes are:

When an alias is present for a table, that alias, and not the table identifier, must be used to qualify columns; or no alias is used. Use of an alias makes it invalid to use the table identifier to qualify a column.
Columns can now be used without qualifiers in a higher scope level. The current scope level is checked first and ambiguous field checking is done at scope level.

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

Now, the engine will correctly report an error that the field RDB$RELATIONS.RDB$RELATION_NAME could not 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

a) 2. The next statement will now use the appropriate FieldID correctly from the subquery and from the updating table:

 UPDATE TableA
   SET
     FieldA = (SELECT SUM(A.FieldB) FROM TableA A
     WHERE A.FieldID = TableA.FieldID)

Note: Although it is possible in Firebird to provide an alias in an update statement, many other database vendors do not support it. These SQL statement syntaxes provide better interchangeability with other SQL database products.

a) 3. This example ran incorrectly in Firebird 1.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 contained 90 rows, it would return 90 * 90 = 8100 rows, but in Firebird 2.0 it will correctly return 90 rows.

b) 1. This would fail in Firebird 1.5, but is possible in Firebird 2.0:

 SELECT
   (SELECT RDB$RELATION_NAME FROM RDB$DATABASE)
   FROM RDB$RELATIONS

b) 2. Ambiguity checking in subqueries

This would run on Firebird 1.5 without reporting an ambiguity, but will report it in Firebird 2.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

Important: (v.2.1) A temporary "relaxation" of the restriction against mixing table identifiers and aliases was made possible in v.2.1, by the introduction of the configuration parameter RelaxedAliasChecking. It is not the default behaviour and its sole purpose is to allow a window for people to bring legacy code into line. It will be deprecated in future so there is no good reason for anyone to write non-compliant statements in new code!

Multiple hits to same column now illegal

It is no longer allowed to make multiple "hits" on the same column in an INSERT or UPDATE statement.

Thus, a statement like

 INSERT INTO T(A, B, A) ...

 UPDATE T SET A = x, B = y, A = z

will be rejected in Firebird 2.n, even though it was tolerated in InterBase^Ž and previous Firebird versions.

Query plans

Stricter validation of user-specified plans

User-specified plans are validated more strictly than they were formerly. If you encounter an exception related to plans, e.g. Table T is not referenced in plan, it will be necessary to inspect your procedure and trigger sources and adjust the plans to make them semantically correct.

Important: Such errors could also show up during the restore process when you are migrating databases to the new version. It will be necessary to correct these conditions in the original database before you attempt to perform a backup/restore cycle.

Plan must refer to all tables in a query

Using a plan without a reference to all tables in a query is now illegal and will cause an exception. Some previous versions would accept plans with missing references, but it was a bug.

PSQL

Restrictions on assignment to context variables in triggers

Assignments to the OLD context variables are now prohibited for every kind of trigger.
Assignments to NEW context variables in AFTER-triggers are also prohibited.

Tip: If you get an unexpected error Cannot update a read-only column then violation of one of these restrictions will be the source of the exception.

Reference to `current of <cursor>` outside scope of loop

In Firebird 1.5 and earlier, referring to current of <cursor> outside the scope of the cursor loop was accepted by the PSQL parser, allowing the likelihood of run-time occurring as a result. Now, it will be rejected in the procedure or trigger definition.

`NULL`s are now "lowest" for sorts

NULL is now treated as the lowest possible value for ordering purposes and sets ordered on nullable criteria are sorted accordingly. Thus:

for ascending sorts NULLs are placed at the beginning of the result set,
for descending sorts NULLs are placed at the end of the result set.

Important: In former versions, NULLs were always at the end. If you have client code or PSQL definitions that rely on the legacy NULLs placement, it will be necessary to use the NULLS LAST option in your ORDER BY clauses for ascending sorts.

`CURRENT_TIMESTAMP` now returns milliseconds by default

The context variable CURRENT_TIMESTAMP now returns milliseconds by default, while it truncated subseconds back to seconds in former versions. If you need to continue receiving the truncated value, you will now need to specify the required accuracy explicitly, i.e. specify CURRENT_TIMESTAMP(0).

`ORDER BY <ordinal-number>` now causes `SELECT *` expansion

When columns are referred to by the "ordinal number" (degree) in an ORDER BY clause, when the output list uses SELECT * FROM ... syntax, the column list will be expanded and taken into account when determining which column the number refers to.

This means that, now, SELECT T1.*, T2.COL FROM T1, T2 ORDER BY 2 sorts on the second column of table T1, while the previous versions sorted on T2.COL.

Tip: This change makes it possible to specify queries like SELECT * FROM TAB ORDER BY 5.

back to top of page
<< Security in Firebird 2 (all platforms) | Firebird 2 Migration & Installation | Configuration parameters >>