Firebird Documentation IndexFirebird 3.0.2 Release NotesCompatibility Issues → SQL Language Changes
Firebird Home Firebird Home Prev: System Tables are Now Read-onlyFirebird Documentation IndexUp: Compatibility IssuesNext: Reserved Words

SQL Language Changes

Support for Mixed-Syntax Joins is Gone
Enforcement of Length Limit for Local Alias and Variable Names
Changes for User Names
Cursor Output Columns Must Be Named
Cursor Stability” Effects

It will be necessary to pay attention to some changes in the SQL language implementation.

Support for Mixed-Syntax Joins is Gone

Improperly mixed explicit and implicit joins are no longer supported, in accordance with the SQL specification. It also means that, in the explicit A JOIN B ON <condition>, the condition is not allowed to refer to any stream except A and B. See Tracker ticket CORE-2812 for more details.

Enforcement of Length Limit for Local Alias and Variable Names

The names for column and table aliases and for local variables names in PSQL are now restricted to 31 bytes in length. This enforcement has been made to comply with the SQL specification that requires them to be regular SQL identifiers, in accordance with the implementation-dependent limits. In Firebird, SQL identifiers are limited to 31 bytes.

See also: CORE-2350

Changes for User Names

User names are treated as SQL identifiers and are accordingly now limited to 31 bytes in length.

Case-sensitive user names are also supported now. The CONNECT and CREATE DATABASE statements in isql will thus allow user names to be specified in double quotes.

Cursor Output Columns Must Be Named

The DECLARE CURSOR statement in PSQL now requires all of the output columns to be explicitly named or aliased. The same requirement applies to the FOR SELECT ... AS CURSOR <cursor name> DO ... statement in PSQL. This requirement is necessitated by the new capability to read cursor elements directly as pseudo columns, e.g. MY_CURSOR.COLUMN_A/.

Illustration:

create procedure sp_test
as
  declare c cursor for (select 1 /* as a */ from rdb$database);
  declare n int;
begin
  open c;
  fetch c into n;
  close c;
end
          

  Statement failed, SQLSTATE = 42000
  unsuccessful metadata update
  -ALTER PROCEDURE SP_TEST failed
  -Dynamic SQL Error
  -SQL error code = -104
  -Invalid command
  -no column name specified for column number 1 in derived table C
          

Cursor Stability” Effects

Some statements may now work differently due to the cursor stability” improvement. Statements affected will be:

  • those that modify the table that is being explicitly or implicitly selected from within the same statement
  • (as a side effect) some MERGE statements, that might work differently if multiple matches are possible.

    Note

    The SQL standard stipulates that the MERGE statement must raise an error if multiple matches are found. Firebird is not so strict in this regard, but its behaviour should be considered undefined in these cases.

See also: CORE-3362, CORE-3317, CORE-4796.

Prev: System Tables are Now Read-onlyFirebird Documentation IndexUp: Compatibility IssuesNext: Reserved Words
Firebird Documentation IndexFirebird 3.0.2 Release NotesCompatibility Issues → SQL Language Changes