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 the chapter New Reserved Words and Changes 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.

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

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) ...

or

  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 original database before you attempt to perform a backup/restore cycle.
Plan must refer to all tables in query: Using a plan without a reference to all tables in 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.

NULLS 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 sub-seconds 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.