A long-standing, legacy loophole in the handling of DPB parameters enabled ordinary users to make connection settings that could lead to database corruptions or give them access to SYSDBA-only operations. The loophole has been closed, a change that could affect several existing applications, database tools and connectivity layers (drivers, components). Details are in Chapter 3, Changes to the Firebird API and ODS.

A regression appeared after v.2.0.1, whereby events cannot work across the WNet protocol. A call to isc_que_events() will cause the server to crash. (Tracker ID CORE-1460).

Bugfix (CORE-1133) "The XNET (IPC) communication protocol would not work across session boundaries" affects those attempting to access databases using the local protocol on Windows Vista as well as those using remote terminal services locally on XP or Server 2003. This fix, done in v.2.0.1, should remove the problems encountered under these conditions.

An important reversion to 1.5 behaviour has occurred in sub-release 2.0.1, as follows:

In Firebird 2.0, a deliberate restriction was imposed to prevent anyone from dropping, altering or recreating a PSQL module if it had been used since the database was opened. An attempt to prepare the DDL statement would result in an “Object in Use” exception.

Many people complained that the restriction was unacceptable because they depended on performing these metadata changes “on the fly”. The restriction has therefore been removed. However, the reversion in no way implies that performing DDL on active PSQL modules is “safer” in Firebird 2.0.1 and higher than it was in V.1.5.

The Firebird Project has an integral user documentation project, a team of volunteers who are writing, editing and adapting user manuals, white papers and HowTos. At the time of this release, the hard-working coordinator of the Docs project is Paul Vinkenoog.

An index of available documents can be found in the on-line documentation index. Published docs currently include the essential Quick Start Guides for Firebird versions 1.5 and 2.0 in English and several other languages.

For the official documentation we use a Docbook XML format for sources and build PDF and HTML output using a suite of Java utilities customised for our purposes. These notes were developed and built under this system.

Paul Vinkenoog has written comprehensive, easy-to-follow manuals for writing Firebird documentation and for using our tools. You can find links to these manuals in the aforementioned index. New team members who want to do some writing or translating are always more than welcome. For information about the team's activities and progress you can visit the Docs Project's homepage. We have a lab forum for documenters and translators, firebird-docs, which you can join by visiting the Lists and Newsgroups page at the Firebird web site.

These release notes are your main documentation for Firebird 2. However, if you are unfamiliar with previous Firebird versions, you will also need the release notes for Firebird 1.5.3. For convenience, copies of both sets of release notes are included in the binary kits. They will be installed in the /doc directory beneath the Firebird root directory. Several other useful README documents are also installed there.

For future reference, if you ever need to get a copy of the latest release notes before beginning installation, you can download them from the Firebird web site. The link can be found on the same page that linked you to the binary kits, towards the bottom of the page.

If you do not own a copy of The Firebird Book, by Helen Borrie, then you will also need to find the beta documentation for InterBase® 6.0. It consists of several volumes in PDF format, of which the most useful will be the Language Reference (LangRef.pdf) and the Data Definition Guide (DataDef.pdf). The Firebird Project is not allowed to distribute these documents but they are easily found at several download sites on the Web using Google and the search key "LangRef.pdf". When you find one, you usually find them all!

--The Firebird Project

Firebird 2.0 has replaced the former implementation of the local transport protocol (often referred to as IPC or IPServer) with a new one, named XNET.

It serves exactly the same goal, to provide an efficient way to connect to server located on the same machine as the connecting client without a remote node name in the connection string. The new implementation is different and addresses the known issues with the old protocol.

Like the old IPServer implementation, the XNET implementation uses shared memory for inter-process communication. However, XNET eliminates the use of window messages to deliver attachment requests and it also implements a different synchronization logic.

WNET (a.k.a. NetBEUI) protocol no longer performs client impersonation.

In all previous Firebird versions, remote requests via WNET are performed in the context of the client security token. Since the server serves every connection according to its client security credentials, this means that, if the client machine is running some OS user from an NT domain, that user should have appropriate permissions to access the physical database file, UDF libraries, etc., on the server filesystem. This situation is contrary to what is generally regarded as proper for a client-server setup with a protected database.

Such impersonation has been removed in Firebird 2.0. WNET connections are now truly client-server and behave the same way as TCP ones, i.e., with no presumptions with regard to the rights of OS users.

BUGCHECK log messages now include file name and line number. (A. Brinkman)

Routines that print out various internal structures (DSQL node tree, BLR, DYN, etc) have been updated. (N. Samofatov)

Thread-safe and signal-safe debug logging facilities have been implemented. (N. Samofatov)

Syslog messages will be copied to the user's tty if a process is attached to it. (A. Peshkov)

From v.2.0.5 and v2.1.2 onward
Several DPB parameters have been made inaccessible to ordinary users, closing some dangerous loopholes. In some cases, they are settings that would alter the database header settings and potentially cause corruptions if not performed under administrator control; in others, they initiate operations that are otherwise restricted to the SYSDBA. They are.-

The parameter isc_dpb_set_page_buffers can still be used by ordinary users on Classic and it will set the buffer size temporarily for that user and that session only. When used by the SYSDBA on either Superserver or Classic, it will change the buffer count in the database header, i.e., make a permanent change to the default buffer size.

The API header file, ibase.h has been subjected to a cleanup. with the result that public headers no longer contain private declarations.

The new feature extends the WAIT mode by making provision to set a finite time interval to wait for the concurrent transactions. If the timeout has passed, an error (isc_lock_timeout) is reported.

Timeout intervals can now be specified per transaction, using the new TPB constant isc_tpb_lock_timeout in the API.

The function call isc_dsql_sql_info() has been extended to enable relation aliases to be retrieved, if required.

isc_blob_lookup_desc() now also describes blobs that are outputs of stored procedures

The macro definition FB_API_VER is added to ibase.h to indicate the current API version. The number corresponds to the appropriate Firebird version.

The current value of FB_API_VER is 20 (two-digit equivalent of 2.0). This macro can be used by client applications to check the version of ibase.h its being compiled with.

The following items have been added to the isc_database_info() function call structure:

The following items have been added to the isc_transaction_info() function call structure:

isc_info_tra_isolation,
  1, isc_info_tra_consistency | isc_info_tra_concurrency |
  2, isc_info_tra_read_committed,
     isc_info_tra_no_rec_version | isc_info_tra_rec_version
          

isc_info_tra_access, 1, isc_info_tra_readonly | isc_info_tra_readwrite
        

The following improvements have been added to the Services API:

#define isc_dpb_shut_cache               0x1
#define isc_dpb_shut_attachment          0x2
#define isc_dpb_shut_transaction         0x4
#define isc_dpb_shut_force               0x8
#define isc_dpb_shut_mode_mask          0x70

#define isc_dpb_shut_default             0x0
#define isc_dpb_shut_normal             0x10
#define isc_dpb_shut_multi              0x20
#define isc_dpb_shut_single             0x30
#define isc_dpb_shut_full               0x40
        

char dpb_buffer[256], *dpb, *p;
ISC_STATUS status_vector[ISC_STATUS_LENGTH];
isc_db_handle handle = NULL;

dpb = dpb_buffer;

*dpb++ = isc_dpb_version1;

const char* user_name = “SYSDBA”;
const int user_name_length = strlen(user_name);
*dpb++ = isc_dpb_user_name;
*dpb++ = user_name_length;
memcpy(dpb, user_name, user_name_length);
dpb += user_name_length;

const char* user_password = “masterkey”;
const int user_password_length = strlen(user_password);
*dpb++ = isc_dpb_password;
*dpb++ = user_password_length;
memcpy(dpb, user_password, user_password_length);
dpb += user_password_length;

// Force an immediate full database shutdown
*dpb++ = isc_dpb_shutdown;
*dpb++ = isc_dpb_shut_force | isc_dpb_shut_full;

const int dpb_length = dpb - dpb_buffer;

isc_attach_database(status_vector,
                    0, “employee.db”,
                    &handle,
                    dpb_length, dpb_buffer);

if (status_vector[0] == 1 && status_vector[1])
{
  isc_print_status(status_vector);
}
else
{
  isc_detach_database(status_vector, &handle);
}
        

On-disk structure (ODS) changes include the following:

SEQUENCE has been introduced as a synonym for GENERATOR, in accordance with SQL-99. SEQUENCE is a syntax term described in the SQL specification, whereas GENERATOR is a legacy InterBase syntax term. Use of the standard SEQUENCE syntax in your applications is recommended.

A sequence generator is a mechanism for generating successive exact numeric values, one at a time. A sequence generator is a named schema object. In dialect 3 it is a BIGINT, in dialect 1 it is an INTEGER.

Syntax patterns

    CREATE { SEQUENCE | GENERATOR } <name>
    DROP { SEQUENCE | GENERATOR } <name>
    SET GENERATOR <name> TO <start_value>
    ALTER SEQUENCE <name> RESTART WITH <start_value>
    GEN_ID (<name>, <increment_value>)
    NEXT VALUE FOR <name>
     

Examples

1.

   CREATE SEQUENCE S_EMPLOYEE;
     

2.

   ALTER SEQUENCE S_EMPLOYEE RESTART WITH 0;
     

See also the notes about NEXT VALUE FOR.

SYSDBA, the database creator or the owner of an object can grant rights on that object to other users. However, those rights can be made inheritable, too. By using WITH GRANT OPTION, the grantor gives the grantee the right to become a grantor of the same rights in turn. This ability can be removed by the original grantor with REVOKE GRANT OPTION FROM user.

However, there's a second form that involves roles. Instead of specifying the same rights for many users (soon it becomes a maintenance nightmare) you can create a role, assign a package of rights to that role and then grant the role to one or more users. Any change to the role's rights affect all those users.

By using WITH ADMIN OPTION, the grantor (typically the role creator) gives the grantee the right to become a grantor of the same role in turn. Until FB v2, this ability couldn't be removed unless the original grantor fiddled with system tables directly. Now, the ability to grant the role can be removed by the original grantor with REVOKE ADMIN OPTION FROM user.

Domains allow their defaults to be changed or dropped. It seems natural that table fields can be manipulated the same way without going directly to the system tables.

Syntax Pattern

  ALTER TABLE t ALTER [COLUMN] c SET DEFAULT default_value;
  ALTER TABLE t ALTER [COLUMN] c DROP DEFAULT;
      

The DDL statements RECREATE EXCEPTION and CREATE OR ALTER EXCEPTION (feature request SF #1167973) have been implemented, allowing either creating, recreating or altering an exception, depending on whether it already exists.

ALTER EXTERNAL FUNCTION has been implemented, to enable the entry_point or the module_name to be changed when the UDF declaration cannot be dropped due to existing dependencies.

The COMMENT statement has been implemented for setting metadata descriptions.

Syntax Pattern

  COMMENT ON DATABASE IS {'txt'|NULL};
  COMMENT ON <basic_type> name IS {'txt'|NULL};
  COMMENT ON COLUMN tblviewname.fieldname IS {'txt'|NULL};
  COMMENT ON PARAMETER procname.parname IS {'txt'|NULL};
      

An empty literal string '' will act as NULL since the internal code (DYN in this case) works this way with blobs.

  <basic_type>:
     DOMAIN
     TABLE
     VIEW
     PROCEDURE
     TRIGGER
     EXTERNAL FUNCTION
     FILTER
     EXCEPTION
     GENERATOR
     SEQUENCE
     INDEX
     ROLE
     CHARACTER SET
     COLLATION
     SECURITY CLASS1
     

¹not implemented, because this type is hidden.

FIRST/SKIP and ROWS syntaxes and PLAN and ORDER BY clauses can now be used in view specifications.

From Firebird 2.0 onward, views are treated as fully-featured SELECT expressions. Consequently, the clauses FIRST/SKIP, ROWS, UNION, ORDER BY and PLAN are now allowed in views and work as expected.

Syntax

For syntax details, refer to Select Statement & Expression Syntax in the chapter about DML.

The DDL statement RECREATE TRIGGER statement is now available in DDL. Semantics are the same as for other RECREATE statements.

The following changes will affect usage or existing, pre-Firebird 2 workarounds in existing applications or databases to some degree.

   declare filter <name> input_type <number> output_type <number>
     entry_point <function_in_library> module_name <library_name>;
      

   declare filter <name> input_type <mnemonic> output_type <mnemonic>
     entry_point <function_in_library> module_name <library_name>;
      

   select RDB$TYPE, RDB$TYPE_NAME, RDB$SYSTEM_FLAG
     from rdb$types
     where rdb$field_name = 'RDB$FIELD_SUB_TYPE';

   RDB$TYPE   RDB$TYPE_NAME                RDB$SYSTEM_FLAG
   ========= ============================ =================

           0 BINARY                                     1
           1 TEXT                                       1
           2 BLR                                        1
           3 ACL                                        1
           4 RANGES                                     1
           5 SUMMARY                                    1
           6 FORMAT                                     1
           7 TRANSACTION_DESCRIPTION                    1
           8 EXTERNAL_FILE_DESCRIPTION                  1
      

    declare filter pesh input_type 0 output_type 3
      entry_point 'f' module_name 'p';
      

    declare filter pesh input_type binary output_type acl
      entry_point 'f' module_name 'p';
      

  SQL> insert into rdb$types
  CON> values('RDB$FIELD_SUB_TYPE', -100, 'XDR', 'test type', 0);
  SQL> commit;
  SQL> declare filter pesh2 input_type xdr output_type text
  CON> entry_point 'p2' module_name 'p';
  SQL> show filter pesh2;
  BLOB Filter: PESH2
          Input subtype: -100 Output subtype: 1
          Filter library is p
          Entry point is p2
        

The SQL language extension EXECUTE BLOCK makes "dynamic PSQL" available to SELECT specifications. It has the effect of allowing a self-contained block of PSQL code to be executed in dynamic SQL as if it were a stored procedure.

Syntax pattern

   EXECUTE BLOCK [ (param datatype = ?, param datatype = ?, ...) ]
     [ RETURNS (param datatype, param datatype, ...) ]
   AS
   [DECLARE VARIABLE var datatype; ...]
   BEGIN
     ...
   END
      

For the client, the call isc_dsql_sql_info with the parameter isc_info_sql_stmt_type returns

The client should preprocess only the head of the SQL statement or use '?' instead of ':' as the parameter indicator because, in the body of the block, there may be references to local variables or arguments with a colon prefixed.

Example

The user SQL is

   EXECUTE BLOCK (X INTEGER = :X)
     RETURNS (Y VARCHAR)
   AS
   DECLARE V INTEGER;
   BEGIN
     INSERT INTO T(...) VALUES (... :X ...);
     SELECT ... FROM T INTO :Y;
     SUSPEND;
   END
      

The preprocessed SQL is

   EXECUTE BLOCK (X INTEGER = ?)
     RETURNS (Y VARCHAR)
   AS
   DECLARE V INTEGER;
   BEGIN
     INSERT INTO T(...) VALUES (... :X ...);
     SELECT ... FROM T INTO :Y;
     SUSPEND;
   END
      

Implemented support for derived tables in DSQL (subqueries in FROM clause) as defined by SQL200X. A derived table is a set, derived from a dynamic SELECT statement. Derived tables can be nested, if required, to build complex queries and they can be involved in joins as though they were normal tables or views.

Syntax Pattern

    SELECT
      <select list>
    FROM
      <table reference list>

    <table reference list> ::= <table reference> [{<comma> <table reference>}...]

    <table reference> ::=
        <table primary>
      | <joined table>

    <table primary> ::=
        <table> [[AS] <correlation name>]
      | <derived table>

    <derived table> ::=
        <query expression> [[AS] <correlation name>]
          [<left paren> <derived column list> <right paren>]

    <derived column list> ::= <column name> [{<comma> <column name>}...]
      

Examples

a) Simple derived table:

  SELECT
    *
  FROM
    (SELECT
       RDB$RELATION_NAME, RDB$RELATION_ID
     FROM
       RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID)
      

b) Aggregate on a derived table which also contains an aggregate

  SELECT
    DT.FIELDS,
    Count(*)
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       Count(*)
     FROM
       RDB$RELATIONS R
       JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)
     GROUP BY
       R.RDB$RELATION_NAME) AS DT (RELATION_NAME, FIELDS)
  GROUP BY
    DT.FIELDS
      

c) UNION and ORDER BY example:

  SELECT
    DT.*
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     UNION ALL
     SELECT
       R.RDB$OWNER_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     ORDER BY
       2) AS DT
  WHERE
    DT.RDB$RELATION_ID <= 4
      

Points to Note

The ROLLBACK RETAIN statement is now supported in DSQL.

A “rollback retaining” feature was introduced in InterBase 6.0, but this rollback mode could be used only via an API call to isc_rollback_retaining(). By contrast, “commit retaining” could be used either via an API call to isc_commit_retaining() or by using a DSQL COMMIT RETAIN statement.

Firebird 2.0 adds an optional RETAIN clause to the DSQL ROLLBACK statement to make it consistent with COMMIT [RETAIN].

Syntax pattern: follows that of COMMIT RETAIN.

ROWS syntax is used to limit the number of rows retrieved from a select expression. For an uppermost-level select statement, it would specify the number of rows to be returned to the host program. A more understandable alternative to the FIRST/SKIP clauses, the ROWS syntax accords with the latest SQL standard and brings some extra benefits. It can be used in unions, any kind of subquery and in UPDATE or DELETE statements.

It is available in both DSQL and PSQL.

Syntax Pattern

   SELECT ...
     [ORDER BY <expr_list>]
     ROWS <expr1> [TO <expr2>]
      

Examples

1.

  SELECT * FROM T1
    UNION ALL
  SELECT * FROM T2
    ORDER BY COL
    ROWS 10 TO 100
      

2.

   SELECT COL1, COL2,
     ( SELECT COL3 FROM T3 ORDER BY COL4 DESC ROWS 1 )
   FROM T4
      

3.

   DELETE FROM T5
     ORDER BY COL5
     ROWS 1
      

Points to Note

The rules for UNION queries have been improved as follows:

  UNION [{DISTINCT | ALL}]
        

   IIF (<search_condition>, <value1>, <value2>)
      

is implemented as a shortcut for

   CASE
     WHEN <search_condition> THEN <value1>
     ELSE <value2>
   END
      

It returns the value of the first sub-expression if the given search condition evaluates to TRUE, otherwise it returns a value of the second sub-expression.

Example

    SELECT IIF(VAL > 0, VAL, -VAL) FROM OPERATION
      

The infamous “Datatype unknown” error (SF Bug #1371274) when attempting some castings has been eliminated. It is now possible to use CAST to advise the engine about the data type of a parameter.

Example

  SELECT CAST(? AS INT) FROM RDB$DATABASE
      

The built-in function SUBSTRING() can now take arbitrary expressions in its parameters.

Formerly, the inbuilt SUBSTRING() function accepted only constants as its second and third arguments (start position and length, respectively). Now, the arguments can be anything that resolves to a value, including host parameters, function results, expressions, subqueries, etc.

x = varchar(50);
substring(x from 1 for 1);
        

The following features involving NULL in DSQL have been implemented:

   <value> IS [NOT] DISTINCT FROM <value>
        

   SELECT * FROM T1
     JOIN T2
       ON T1.NAME IS NOT DISTINCT FROM T2.NAME;
        

   SELECT * FROM T
     WHERE T.MARK IS DISTINCT FROM 'test';
        

  A = NULL
  B > NULL
  A + NULL
  B || NULL
        

  Database:  proc.fdb
  SQL> create table gnull(a int);
  SQL> insert into gnull values(null);
  SQL> insert into gnull values(1);
  SQL> select a from gnull order by a;
             A
  ============
        <null>
             1

  SQL> select a from gnull order by a asc;

             A
  ============
        <null>
             1

  SQL> select a from gnull order by a desc;

             A
  ============
             1
        <null>

  SQL> select a from gnull order by a asc nulls first;

             A
  ============
        <null>
             1

  SQL> select a from gnull order by a asc nulls last;

             A
  ============
             1
        <null>


  SQL> select a from gnull order by a desc nulls last;

             A
  ============
             1
        <null>

  SQL> select a from gnull order by a desc nulls first;

             A
  ============
        <null>
             1
        

CROSS JOIN is now supported. Logically, this syntax pattern:

   A CROSS JOIN B
      

is equivalent to either of the following:

   A INNER JOIN B ON 1 = 1
      

or, simply:

  FROM A, B
      

SELECT specifications used in subqueries and in INSERT INTO <insert-specification> SELECT.. statements can now specify a UNION set.

ROWS specifications and PLAN and ORDER BY clauses can now be used in UPDATE and DELETE statements.

Users can now specify explicit plans for UPDATE/DELETE statements in order to optimize them manually. It is also possible to limit the number of affected rows with a ROWS clause, optionally used in combination with an ORDER BY clause to have a sorted recordset.

Syntax Pattern

  UPDATE ... SET ... WHERE ...
  [PLAN <plan items>]
  [ORDER BY <value list>]
  [ROWS <value> [TO <value>]]
      

or

  DELETE ... FROM ...
  [PLAN <plan items>]
  [ORDER BY <value list>]
  [ROWS <value> [TO <value>]]
      

A number of new facilities have been added to extend the context information that can be retrieved:

    CURRENT_TIME [(<seconds precision>)]
    CURRENT_TIMESTAMP [(<seconds precision>)]
          

    1. SELECT CURRENT_TIME FROM RDB$DATABASE;
    2. SELECT CURRENT_TIME(3) FROM RDB$DATABASE;
    3. SELECT CURRENT_TIMESTAMP(3) FROM RDB$DATABASE;
          

    RDB$SET_CONTEXT( <namespace>, <variable>, <value> )
    RDB$GET_CONTEXT( <namespace>, <variable> )
       

  DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
      VARCHAR(80),
      VARCHAR(80)
  RETURNS VARCHAR(255) FREE_IT;

  DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
      VARCHAR(80),
      VARCHAR(80),
      VARCHAR(255)
  RETURNS INTEGER BY VALUE;
       

   set term ^;
   create procedure set_context(User_ID varchar(40), Trn_ID integer) as
   begin
     RDB$SET_CONTEXT('USER_TRANSACTION', 'Trn_ID', Trn_ID);
     RDB$SET_CONTEXT('USER_TRANSACTION', 'User_ID', User_ID);
   end ^

  create table journal (
     jrn_id integer not null primary key,
     jrn_lastuser varchar(40),
     jrn_lastaddr varchar(255),
     jrn_lasttransaction integer
  )^

CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UPDATE
  as
  begin
    new.jrn_lastuser = rdb$get_context('USER_TRANSACTION', 'User_ID');
    new.jrn_lastaddr = rdb$get_context('SYSTEM', 'CLIENT_ADDRESS');
    new.jrn_lasttransaction = rdb$get_context('USER_TRANSACTION', 'Trn_ID');
  end ^
  commit ^
  execute procedure set_context('skidder', 1) ^

  insert into journal(jrn_id) values(0) ^
  set term ;^
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', 'ru') 
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                0
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', 'ca')
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                1
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', NULL)
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                1
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', NULL)
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                0
          

Syntax rules

The following schema describing the syntax rules should be helpful when composing plans.

PLAN ( { <stream_retrieval> | <sorted_streams> | <joined_streams> } )

<stream_retrieval> ::= { <natural_scan> | <indexed_retrieval> | 
    <navigational_scan> }

<natural_scan> ::= <stream_alias> NATURAL

<indexed_retrieval> ::= <stream_alias> INDEX ( <index_name> 
    [, <index_name> ...] )

<navigational_scan> ::= <stream_alias> ORDER <index_name>
    [ INDEX ( <index_name> [, <index_name> ...] ) ]

<sorted_streams> ::= SORT ( <stream_retrieval> )

<joined_streams> ::= JOIN ( <stream_retrieval>, <stream_retrieval> 
    [, <stream_retrieval> ...] ) 
    | [SORT] MERGE ( <sorted_streams>, <sorted_streams> )
      

Details

Natural scan means that all rows are fetched in their natural storage order. Thus, all pages must be read before search criteria are validated.

Indexed retrieval uses an index range scan to find row ids that match the given search criteria. The found matches are combined in a sparse bitmap which is sorted by page numbers, so every data page will be read only once. After that the table pages are read and required rows are fetched from them.

Navigational scan uses an index to return rows in the given order, if such an operation is appropriate.-

A sort operation performs an external sort of the given stream retrieval.

A join can be performed either via the nested loops algorithm (JOIN plan) or via the sort merge algorithm (MERGE plan).-

A sort merge operates with two input streams which are sorted beforehand, then merged in a single run.

Examples

    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS
    WHERE RDB$RELATION_NAME LIKE 'RDB$%'
    PLAN (RDB$RELATIONS NATURAL)
    ORDER BY RDB$RELATION_NAME

    SELECT R.RDB$RELATION_NAME, RF.RDB$FIELD_NAME
    FROM RDB$RELATIONS R
      JOIN RDB$RELATION_FIELDS RF
      ON R.RDB$RELATION_NAME = RF.RDB$RELATION_NAME
    PLAN MERGE (SORT (R NATURAL), SORT (RF NATURAL))
      

Notes

Some useful improvements have been made to SQL sorting operations:

  ...
    GROUP BY
    SUBSTRING(CAST((A * B) / 2 AS VARCHAR(15)) FROM 1 FOR 2)
        

   SELECT *
     FROM RDB$RELATIONS
     ORDER BY 9
        

Added SQL-99 compliant NEXT VALUE FOR <sequence_name> expression as a synonym for GEN_ID(<generator-name>, 1), complementing the introduction of CREATE SEQUENCE syntax as the SQL standard equivalent of CREATE GENERATOR.

Examples

1.

   SELECT GEN_ID(S_EMPLOYEE, 1) FROM RDB$DATABASE;
      

2.

   INSERT INTO EMPLOYEE (ID, NAME)
     VALUES (NEXT VALUE FOR S_EMPLOYEE, 'John Smith');
      

The RETURNING clause syntax has been implemented for the INSERT statement, enabling the return of a result set from the INSERT statement. The set contains the column values actually stored. Most common usage would be for retrieving the value of the primary key generated inside a BEFORE-trigger.

Available in DSQL and PSQL.

Syntax Pattern

  INSERT INTO ... VALUES (...) [RETURNING <column_list> [INTO <variable_list>]]
      

Example(s)

1.

  INSERT INTO T1 (F1, F2)
    VALUES (:F1, :F2)
  RETURNING F1, F2 INTO :V1, :V2;
      

2.

 INSERT INTO T2 (F1, F2)
   VALUES (1, 2)
  RETURNING ID INTO :PK;
      

Alias handling and ambiguous field detecting have been improved. In summary:

Examples

ROW_COUNT has been enhanced so that it can now return the number of rows returned by a SELECT statement.

For example, it can be used to check whether a singleton SELECT INTO statement has performed an assignment:

  ..
  BEGIN
    SELECT COL FROM TAB INTO :VAR;

    IF (ROW_COUNT = 0) THEN
      EXCEPTION NO_DATA_FOUND;
  END
  ..
      

See also its usage in the examples below for explicit PSQL cursors.

It is now possible to declare and use multiple cursors in PSQL. Explicit cursors are available in a DSQL EXECUTE BLOCK structure as well as in stored procedures and triggers.

Syntax pattern

    DECLARE [VARIABLE] <cursor_name> CURSOR FOR ( <select_statement> );
    OPEN <cursor_name>;
    FETCH <cursor_name> INTO <var_name> [, <var_name> ...];
    CLOSE <cursor_name>;
      

Examples

1.

    DECLARE RNAME CHAR(31);
    DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME
                              FROM RDB$RELATIONS );
    BEGIN
      OPEN C;
      WHILE (1 = 1) DO
      BEGIN
        FETCH C INTO :RNAME;
        IF (ROW_COUNT = 0) THEN
          LEAVE;
        SUSPEND;
      END
      CLOSE C;
    END
      

2.

    DECLARE RNAME CHAR(31);
    DECLARE FNAME CHAR(31);
    DECLARE C CURSOR FOR ( SELECT RDB$FIELD_NAME
                           FROM RDB$RELATION_FIELDS
                           WHERE RDB$RELATION_NAME = :RNAME
                           ORDER BY RDB$FIELD_POSITION );
    BEGIN
      FOR
        SELECT RDB$RELATION_NAME
        FROM RDB$RELATIONS
        INTO :RNAME
      DO
      BEGIN
        OPEN C;
        FETCH C INTO :FNAME;
        CLOSE C;
        SUSPEND;
      END
    END
      

Defaults can now be declared for stored procedure arguments.

The syntax is the same as a default value definition for a column or domain, except that you can use '=' in place of 'DEFAULT' keyword.

Arguments with default values must be last in the argument list; that is, you cannot declare an argument that has no default value after any arguments that have been declared with default values. The caller must supply the values for all of the arguments preceding any that are to use their defaults.

For example, it is illegal to do something like this:   supply arg1, arg2, miss arg3, set arg4...

Substitution of default values occurs at run-time. If you define a procedure with defaults (say P1), call it from another procedure (say P2) and skip some final, defaulted arguments, then the default values for P1 will be substituted by the engine at time execution P1 starts. This means that, if you change the default values for P1, it is not necessary to recompile P2.

However, it is still necessary to disconnect all client connections, as discussed in the Borland InterBase 6 beta "Data Definition Guide" (DataDef.pdf), in the section "Altering and dropping procedures in use".

Examples

   CONNECT ... ;
   SET TERM ^;
   CREATE PROCEDURE P1 (X INTEGER = 123)
   RETURNS (Y INTEGER)
   AS
   BEGIN
     Y = X;
     SUSPEND;
   END ^
   COMMIT ^
   SET TERM ;^

   SELECT * FROM P1;

              Y
   ============

            123

   EXECUTE PROCEDURE P1;

              Y
   ============
            123

   SET TERM ^;
   CREATE PROCEDURE P2
   RETURNS (Y INTEGER)
   AS
   BEGIN
     FOR SELECT Y FROM P1 INTO :Y
     DO SUSPEND;
   END ^
   COMMIT ^
   SET TERM ;^

   SELECT * FROM P2;

              Y
   ============

            123

   SET TERM ^;
   ALTER PROCEDURE P1 (X INTEGER = CURRENT_TRANSACTION)
            RETURNS (Y INTEGER)
   AS
   BEGIN
     Y = X;
    SUSPEND;
   END; ^
   COMMIT ^
   SET TERM ;^

   SELECT * FROM P1;

              Y
   ============

           5875

   SELECT * FROM P2;

              Y
   ============

            123

   COMMIT;

   CONNECT  ... ;

   SELECT * FROM P2;

              Y
   ============

           5880

      

New LEAVE <label> syntax now allows PSQL loops to be marked with labels and terminated in Java style. The purpose is to stop execution of the current block and unwind back to the specified label. After that execution resumes at the statement following the terminated loop.

Syntax pattern

    <label_name>: <loop_statement>
    ...
    LEAVE [<label_name>]
      

where <loop_statement> is one of: WHILE, FOR SELECT, FOR EXECUTE STATEMENT.

Examples

1.

   FOR
     SELECT COALESCE(RDB$SYSTEM_FLAG, 0), RDB$RELATION_NAME
       FROM RDB$RELATIONS
       ORDER BY 1
     INTO :RTYPE, :RNAME
     DO
     BEGIN
       IF (RTYPE = 0) THEN
         SUSPEND;
       ELSE
         LEAVE; -- exits current loop
     END
      

2.

   CNT = 100;
   L1:
   WHILE (CNT >= 0) DO
   BEGIN
     IF (CNT < 50) THEN
       LEAVE L1; -- exists WHILE loop
     CNT = CNT - l;
   END
      

3.

   STMT1 = 'SELECT RDB$RELATION_NAME FROM RDB$RELATIONS';
   L1:
   FOR
     EXECUTE STATEMENT :STMT1 INTO :RNAME
   DO
   BEGIN
     STMT2 = 'SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS
       WHERE RDB$RELATION_NAME = ';
     L2:
     FOR
       EXECUTE STATEMENT :STMT2 || :RNAME INTO :FNAME
     DO
     BEGIN
       IF (RNAME = 'RDB$DATABASE') THEN
         LEAVE L1; -- exits the outer loop
       ELSE IF (RNAME = 'RDB$RELATIONS') THEN
         LEAVE L2; -- exits the inner loop
       ELSE
         SUSPEND;
     END
   END
      

The set of OLD context variables available in trigger modules is now read-only. An attempt to assign a value to OLD.something will be rejected.

The API client can now extract a simple stack trace Error Status Vector when an exception occurs during PSQL execution (stored procedures or triggers). A stack trace is represented by one string (2048 bytes max.) and consists of all the stored procedure and trigger names, starting from the point where the exception occurred, out to the outermost caller. If the actual trace is longer than 2Kb, it is truncated.

Additional items are appended to the status vector as follows:

   isc_stack_trace, isc_arg_string, <string length>, <string>
      

isc_stack_trace is a new error code with value of 335544842L.

Examples

Metadata creation

  CREATE TABLE ERR (
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(16));

  CREATE EXCEPTION EX '!';
  SET TERM ^;

  CREATE OR ALTER PROCEDURE ERR_1 AS
  BEGIN
    EXCEPTION EX 'ID = 3';
  END ^

  CREATE OR ALTER TRIGGER ERR_BI FOR ERR
    BEFORE INSERT AS
  BEGIN
    IF (NEW.ID = 2)
    THEN EXCEPTION EX 'ID = 2';

    IF (NEW.ID = 3)
    THEN EXECUTE PROCEDURE ERR_1;

    IF (NEW.ID = 4)
    THEN NEW.ID = 1 / 0;
  END ^

  CREATE OR ALTER PROCEDURE ERR_2 AS
  BEGIN
    INSERT INTO ERR VALUES (3, '333');
  END ^
      

1. User exception from a trigger:

  SQL" INSERT INTO ERR VALUES (2, '2');
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 2
  -At trigger 'ERR_BI'
      

2. User exception from a procedure called by a trigger:

  SQL" INSERT INTO ERR VALUES (3, '3');
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 3
  -At procedure 'ERR_1'
  At trigger 'ERR_BI'
      

3. Run-time exception occurring in trigger (division by zero):

  SQL" INSERT INTO ERR VALUES (4, '4');
  Statement failed, SQLCODE = -802
  arithmetic exception, numeric overflow, or string truncation
  -At trigger 'ERR_BI'
      

4. User exception from procedure:

  SQL" EXECUTE PROCEDURE ERR_1;
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 3
  -At procedure 'ERR_1'
      

5. User exception from a procedure with a deeper call stack:

  SQL" EXECUTE PROCEDURE ERR_2;
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 3
  -At procedure 'ERR_1'
  At trigger 'ERR_BI'
  At procedure 'ERR_2'
      

In PSQL, supported UDFs, e.g. RDB$SET_CONTEXT, can be called as though they were void functions (a.k.a “procedures” in Object Pascal). For example:

  BEGIN
     ...
     RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_VAR', '123');
     ...
  END
      

New flags are added to the header->pag_flags.

The flag btr_large_keys (32) is for storing compressed length/prefix and record-number. This meant also that length and prefix can be up to 1/4 of page-size (1024 for 4096 page-size) and is easy extensible in the future without changing disk-structure again.

Also the record-number can be easy extended to for example 40 bits. Those numbers are stored per 7-bits with 1 bit (highest) as marker (variable length encoding). Every new byte that needs to be stored is shifted by 7.

Examples

25 is stored as 1 byte 0x19, 130 = 2 bytes 0x82  0x01, 65535 = 3 bytes 0xFF  0xFF  0x03.

A new flag is also added for storing record-number on every node (non-leaf pages). This speeds up index-retrieval on many duplicates. The flag is  btr_all_recordnumber (16).

With this added information, key-lookup on inserts/deletes with many duplicates (NULLs in foreign keys, for example) becomes much faster (such as the garbage collection!).

Beside that duplicate nodes (length = 0) don't store their length information, 3 bits from the first stored byte are used to determine if this nodes is a duplicate.

Beside the ZERO_LENGTH (4) there is also END_LEVEL (1), END_BUCKET (2), ZERO_PREFIX_ZERO_LENGTH (3) and ONE_LENGTH (5) marker. Number 6 and 7 are reserved for future use.

A jump node is a reference to a node somewhere in the page.

It contains offset information about the specific node and the prefix data from the referenced node, but prefix compression is also done on the jump-nodes themselves.

Ideally a new jump node is generated after the first node that is found after every jumpAreaSize, but that's only the case on deactivate/active an index or inserting nodes in the same order as they will be stored in the index.

If nodes are inserted between two jump node references only the offsets are updated, but only if the offsets don't exceed a specific threshold (+/-10 %).

When a node is deleted only offsets are updated or a jump node is removed. This means a little hole can exist between the last jump node and the first node, so we don't waste time on generating new jump-nodes.

The prefix and length are also stored by variable length encoding.

Figure 8.3. Example data ((x) = size in x bytes)

Pointer after fixed header = 0x22

Pointer after jump info = 0x29

Pointer to first jump node = 0x29 + 6 (jump node 1) + 5 (jump node 2) = 0x34

Jump node 1 is referencing to the node that represents FIREBIRD as data, because this node has a prefix of 2 the first 2 characters FI are stored also on the jump node.

Our next jump node points to a node that represents FUEL with also a prefix of 2. Thus jump node 2 should contain FU, but our previous node already contained the F so, due to prefix compression, this one is ignored and only U is stored.

The data that needs to be stored is determined in the procedure compress() in btr.cpp.

For ASC (ascending) indexes no data will be stored (key is zero length). This will automatically put them as first entry in the index and thus correct order (For single field index node length and prefix is zero).

DESC (descending) indexes will store a single byte with the value 0xFF (255). To distinguish between a value (empty string can be 255) and an NULL state we insert a byte of 0xFE (254) at the front of the data. This is only done for values that begin with 0xFF (255) or 0xFE (254), so we keep the right order.

Figure 8.4. Examples

The following changes affect all databases.

        (NOT NOT A = 0) -> (A = 0)
        (NOT A > 0) -> (A <= 0)
        

  select rdb$relation_id, count(*)
  from rdb$relations
  group by rdb$relation_id
  having rdb$relation_id > 10

  select * from ( 
    select rdb$relation_id, count(*)
    from rdb$relations
    group by rdb$relation_id
    ) as grp (id, cnt)
  where grp.id > 10
        

This group of optimizations affects databases that were created under Firebird 2.

LOWER() returns the input argument converted to all lower-case characters.

Example

isql -q -ch dos850

SQL> create database 'test.fdb';
SQL> create table t (c char(1) character set dos850);
SQL> insert into t values ('A');
SQL> insert into t values ('E');
SQL> insert into t values ('Á');;
SQL> insert into t values ('É');
SQL>
C      LOWER
====== ======
A      a
E      e
Á      á
É      é
    

TRIM trims characters (default: blanks) from the left and/or right of a string.

Syntax Pattern

TRIM <left paren> [ [ <trim specification> ] [ <trim character> ]
  FROM ] <value expression> <right paren>

   <trim specification> ::=  LEADING  | TRAILING  | BOTH

   <trim character> ::= <value expression>
    

Rules

Examples

A)

  select
    rdb$relation_name,
    trim(leading 'RDB$' from rdb$relation_name)
  from rdb$relations
    where rdb$relation_name starting with 'RDB$';
    

B)

  select
    trim(rdb$relation_name) || ' is a system table'
  from rdb$relations
    where rdb$system_flag = 1;
    

Three new functions will return information about the size of strings:

Syntax Pattern

These three functions share a similar syntax pattern, as follows.-

<length function> ::=
{ BIT_LENGTH | CHAR_LENGTH | CHARACTER_LENGTH | OCTET_LENGTH } ( <value expression> <)
    

Example

  select
    rdb$relation_name,
    char_length(rdb$relation_name),
    char_length(trim(rdb$relation_name))
  from rdb$relations;
    

Firebird allows character sets and collations to be declared in any character field or variable declaration. The default character set can also be specified at database create time, to cause every CHAR/VARCHAR declaration that doesn't specifically included a CHARACTER SET clause to use it.

At attachment time you can specify the character set that the client is to use to read strings. If no "client" (or "connection") character set is specified, character set NONE is assumed.

Two special character sets, NONE and OCTETS, can be used in declarations. However, OCTETS cannot be used as a connection character set. The two sets are similar, except that the space character of NONE is ASCII 0x20, whereas the space character OCTETS is 0x00. NONE and OCTETS are "special" in the sense that they do not follow the rule that other charsets do regarding conversions.

Enhancements include:

  isql -q -ch dos850
  SQL> create database 'test.fdb';
  SQL> create table t (c char(1) character set dos850);
  SQL> insert into t values ('a');
  SQL> insert into t values ('e');
  SQL> insert into t values ('á');
  SQL> insert into t values ('é');
  SQL>
  SQL> select c, upper(c) from t;

  C      UPPER
  ====== ======
       a      A
       e      E
       á      á
       é      é
      

  C      UPPER
  ====== ======
       a      A
       e      E
       á      Á
       é      É
      

  select blob_column from table
    where blob_column collate unicode = 'foo';
        

   <intl_module fbintl>
       filename    $(this)/fbintl
   </intl_module>

   <charset ISO8859_1>
       intl_module    fbintl
       collation    ISO8859_1
       collation    DA_DA
       collation    DE_DE
       collation    EN_UK
       collation    EN_US
       collation    ES_ES
       collation    PT_BR
       collation    PT_PT
   </charset>

   <charset WIN1250>
       intl_module    fbintl
       collation    WIN1250
       collation    PXW_CSY
       collation    PXW_HUN
       collation    PXW_HUNDC
   </charset>
      

  isql -q -ch dos850
  SQL> create database 'test.fdb';
  SQL> create table t (c char(1) character set utf8);
  SQL> insert into t values ('a');
  SQL> insert into t values ('A');
  SQL> insert into t values ('á');
  SQL> insert into t values ('b');
  SQL> insert into t values ('B');
  SQL> select * from t order by c collate ucs_basic;

  C
  ======
  A
  B
  a
  b
  á

  SQL> select * from t order by c collate unicode;

  C
  ======
  a
  A
  á
  b
  B
        

  isql -q -ch dos850
  SQL> create database 'test.fdb';
  SQL> create table t (c char(1) character set iso8859_1 collate pt_br);
  SQL> insert into t values ('a');
  SQL> insert into t values ('A');
  SQL> insert into t values ('á');
  SQL> insert into t values ('b');
  SQL> select * from t order by c;

  C
  ======
  A
  a
  á
  b

  SQL> select * from t where c = 'â';

  C
  ======
  a
  A
  â
        

The following bugs related to character sets and collations were fixed:

SF #1073212   An Order By on a big column with a COLLATE clause would terminate the server.

SF #939844   A query in a UNICODE database would throw a GDS Exception if it was longer than 263 characters.

SF #977785   Wrong character lengths were being returned from some multi-byte character sets (UTF-8, East-Asian charsets).

SF #536243   A correct result is now returned when the UPPER() function is applied to a UNICODE_FSS string.

SF #942726   UPPER did not convert aacute to Aacute for ISO8859_1

SF #544630   Some problems were reported when connecting using UNICODE.

SF #540547   Some problems involving concatenation, numeric fields and character set were fixed.

Unregistered bug   A query could produce different results, depending on the presence of an index, when the last character of the string was the first character of a compression pair.

Unregistered bug   SUBSTRING did not work correctly with a BLOB in a character set.

Unregistered bug   Pattern matching with multi-byte BLOBs was being performed in binary mode.

Unregistered bug   Connecting with a multi-byte character set was unsafe if the database had columns using a different character set.

The new security database is renamed as security2.fdb. Inside, the user authentication table, where user names and passwords are stored, is now called RDB$USERS. There is no longer a table named “users” but a new view over RDB$USERS that is named “USERS”. Through this view, users can change their passwords.

For details of the new database, see New Security Database in the section about authentication later in this chapter.

For instructions on updating previous security databases, refer to the section Dealing with the New Security Database at the end of this chapter.

Password encryption/decryption now uses a more secure password hash calculation algorithm.

The SYSDBA remains the keeper of the security database. However, users can now modify their own passwords.

gsec now uses the Services API. The server will refuse any access to security2.fdb except through the Services Manager.

Attempts to get access to the server using brute-force techniques on accounts and passwords are now detected and locked out.

Support for brute-force attack protection has been included in both the attachment functions of the Firebird API and the Services API. For more details, see Protection from Brute-force Hacking

Several known vulnerabilities in the API have been closed.

Firebird authentication checks a server-wide security database in order to decide whether a database or server connection request is authorised. The security database stores the user names and passwords of all authorised login identities.

Special measures were thus taken to make remote connection to the security database completely impossible. Don't be surprised if some old program fails on attempting direct access: this is by design. Users information may now be accessed only through the Services API and the equivalent internal access to services now implemented in the isc_user_* API functions.

Current high-speed CPUs and fast WAN connections make it possible to try to brute-force Firebird server users' passwords. This is especially dangerous for Superserver which, since Firebird 1.5, performs user authentication very fast. Classic is slower, since it has to create new process for each connection, attach to the security database within that connection and compile a request to the table RDB$USERS before validating login and password. Superserver caches the connection and request, thus enabling a much faster user validation.

Given the 8-byte maximum length of the traditional Firebird password, the brute-force hacker had a reasonable chance to break into the Firebird installation.

The v.2.0 Superserver has active protection to make a brute-force attack more difficult. After a few failed attempts to log in, the user and IP address are locked for a few seconds, denying any attempt to log in with that particular user name OR from that particular IP address for a brief period.

No setup or configuration is required for this feature. It is active automatically as soon as the Firebird 2.0 SuperServer starts up.

To do the upgrade, follow these steps:

Now you should be able to connect to the Firebird 2 server using your old logins and passwords.

  RDB$PASSWD RDB$PASSWD,
        

  RDB$PASSWD RDB$PASSWD NOT NULL,
        

Fast, on-line, page-level incremental backup facilities have been implemented.

The backup engine comprises two parts:

    nbackup <options>
          

   -L <database>   Lock database for filesystem copy
   -N <database>       Unlock previously locked database
   -F <database>              Fixup database after filesystem copy
   -B <level> <database> [<filename>]  Create incremental backup
   -R <database> [<file0> [<file1>...]]   Restore incremental backup
   -U <user>             User name
   -P <password>         Password
            

Content

  -RECREATE_DATABASE [OVERWRITE]
          

  -R[EPLACE_DATABASE]
          

The following command-line switches were added:

The following commands have been added or enhanced.

  SET HEADing [ON | OFF]
          

  SET TRANSACTION WAIT SNAPSHOT NO AUTO UNDO LOCK TIMEOUT 10
          

opt/firebird/bin] isql -?
Unknown switch: ?
usage:    isql [options] [<database>]
        -a(all)                 extract metadata incl. legacy non-SQL tables
        -b(ail)                 bail on errors (set bail on)
        -c(ache) <num>          number of cache buffers
        -ch(arset) <charset>    connection charset (set names)
        -d(atabase) <database>  database name to put in script creation
        -e(cho)                 echo commands (set echo on)
        -ex(tract)              extract metadata
        -i(nput) <file>         input file (set input)
        -m(erge)                merge standard error
        -m2                     merge diagnostic
        -n(oautocommit)         no autocommit DDL (set autoddl off)
        -now(arnings)           do not show warnings
        -o(utput) <file>        output file (set output)
        -pag(elength) <size>    page length
        -p(assword) <password>  connection password
        -q(uiet)                do not show the message "Use CONNECT..."
        -r(ole) <role>          role name
        -r2 <role>              role (uses quoted identifier)
        -sqldialect <dialect>   SQL dialect (set sql dialect)
        -t(erminator) <term>    command terminator (set term)
        -u(ser) <user>          user name
        -x                      extract metadata
        -z                      show program and server version
          

SF #910430     ISQL and database dialect

fixed by C. Valderrama, B. Rodriguez Somoza

What was fixed    When ISQL disconnected from a database, either by dropping it or by trying to connect to a non-existent database, it remembered the SQL dialect of the previous connection, which could lead to some inappropriate warning messages.

    ~ ~ ~     

SF #223126     Misplaced collation when extracting metadadata with ISQL

fixed by B. Rodriguez Somoza

    ~ ~ ~     

SF #223513     Ambiguity between tables and views

fixed by B. Rodriguez Somoza

    ~ ~ ~     

SF #518349     ISQL SHOW mangles relationship

fixed by B. Rodriguez Somoza

    ~ ~ ~     

Unregistered bug     Possible crashes with long terminators

fixed by C. Valderrama

    ~ ~ ~     

Unregistered bug     Avoided several SQL> prompts when using the INPUT command interactively.

implemented by C. Valderrama

    ~ ~ ~     

Unregistered bugs     Some memory leaks

fixed by C. Valderrama

    ~ ~ ~     

gsec now returns an error code when used as a non-interactive utility. Zero indicates success; any other code indicates failure.

The options for gfix -shut[down] have been extended to include two extra states or modes to govern the shutdown.

New Syntax Pattern

      gfix <command> [<state>] [<options>]

  <command> ::= {-shut | -online}
  <state> ::= {normal | multi | single | full}
  <options> ::= {[-force | -tran | -attach] <timeout>}
        

- “normal” state = online database

- “multi” state = multi-user shutdown mode (the legacy one, unlimited attachments of SYSDBA/owner are allowed)

- “single” state = single-user shutdown (only one attachment is allowed, used by the restore process)

- “full” state = full/exclusive shutdown (no attachments are allowed)

The modes can be switched sequentially:

Examples

  gfix -shut single -force 0
  gfix -shut full -force 0
  gfix -online single
  gfix -online
        

You cannot use -shut to bring a database one level “more online” and you cannot use -online to make a database more protected (an error will be thrown).

For example, these sequence-pairs are prohibited:

  gfix -shut single -force 0
  gfix -shut multi -force 0
::
  gfix -online
  gfix -online full
::
  gfix -shut -force 0
  gfix -online single
        

In previous versions, the external function rand() sets the random number generator's starting point based on the current time and then generates the pseudo-random value.

   srand((unsigned) time(NULL));
   return ((float) rand() / (float) RAND_MAX);
      

The problem with this algorithm is that it will return the same value for two calls done within a second.

To work around this issue, rand() was changed in Firebird 2.0 so that the starting point is not set explicitly. This ensures that different values will always be returned.

In order to keep the legacy behaviour available in case somebody needs it, srand() has been introduced. It does exactly the same as the old rand() did.

The function IB_UDF_lower() in the IB_UDF library might conflict with the new internal function lower(), if you try to declare it in a database using the ib_udf.sql script from a previous Firebird version.

  /* ib_udf.sql declaration that now causes conflict */
  DECLARE EXTERNAL FUNCTION lower
      CSTRING(255)
      RETURNS CSTRING(255) FREE_IT
      ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';
      

The problem will be resolved in the latest version of the new ib_udf2.sql script, where the old UDF is declared using a quoted identifier.

  /* New declaration in ib_udf2.sql */
  DECLARE EXTERNAL FUNCTION "LOWER"
    CSTRING(255) NULL
    RETURNS CSTRING(255) FREE_IT
    ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';
      

The FBUDF library no longer depends on FBCLIENT to be built.

When you attach to some database using multiple hosts in the connection string, only the last host in this list is the one that opens the database. The other hosts act as intermediate gateways on port gds_db. Previously, when working, this feature was available unconditionally. Now, it can be configured.

Remote redirection is turned off by default.

The option CreateInternalWindow is no longer required to run multiple server instances and it has been removed.

The DeadThreadsCollection parameter is no longer used at all. Dead threads are now efficiently released "on the fly", making configuration unnecessary. Firebird 2.0 silently ignores this parameter.

An important change has been done to prevent accidental database overwrites as the result of users mistakenly treating “-R” as an abbreviation for “restore”. gbak -R was formerly a shortcut for “-REPLACE_DATABASE”. Now the -R switch no longer restores a database by overwriting an existing one, but instead reports an error.

If you actually want the former behaviour, you have two alternatives:

The transport internals for the local protocol have been reimplemented (XNET instead of IPServer). With regard to the local protocol, the new client library is therefore incompatible with older servers and older client libraries are incompatible with the Firebird 2 servers.

If you need to use the local protocol, please ensure your server and client binaries have exactly the same version numbers.

WNET (a.k.a. NetBEUI, Named Pipes) protocol no longer performs client impersonation. For more information, refer to Change to WNET Protcol in the chapter about new features.

The optional switch -i[nteractive] has been implemented in instsvc.exe to enable an interactive mode for LocalSystem services.

For v.1.5, it was required (as Allow service to interact with desktop) to run the local IPC protocol, as it used a windows message to connect the server. In v.2.0, it is no longer necessary and the server itself does not need this option.

However, some custom UDFs may use the Win32 messaging facilities and this option allows them to work as expected.

On Windows, you have three server models to choose from: Superserver, Classic and Embedded Server. This means you have some decisions to make before installing Firebird 2.0.

If you already have an earlier version of Firebird or InterBase® on your server and you think you might want to go back to it, set up your fall-back position before you begin.

This is really the easy part: the actual install. Just run the executable and respond to the dialogs. After you have answered a few dialogs about licensing and installation notes, you should see one where you decide on the location of the Firebird root directory.

Next, you should see a screen where you choose the installation you want:

Choose the installation you want and hit the "Next" button to carry on responding to dialogs.

The next screen of interest enables you to set up how you want the server to run.

Choose the options you want, according to your choice of server model.

Eventually, the dialogs will stop, you will press “Install” and the server will either silently start the server (if you requested it) or prompt you for permission to reboot. Reboot will be needed if the installer was unable to update a DLL due to its being already loaded when the installer started up.

The installation of FB 2.0 is similiar in principle to previous versions. If you don't have a special setup program (it's distributed separately) the steps are the following:

    instsvc.exe install -classic
      

    fbserver.exe -a
        

Some changes between Firebird 1.5 and Firebird 2.0 mean the existing docs are slightly out-of-date. For convenience, the following are the updated notes.

The embedded server is a fully functional server linked as a dynamic library (fbembed.dll). It has exactly the same features as the usual Superserver and exports the standard Firebird API entrypoints.

The embedded server acts as a true local server for a single client accessing databases on a local machine. It can also act as a remote gateway that redirects all network calls to other hosts, just as the regular client library does.

  c:\my_app\app.exe
  c:\my_app\gds32.dll
  c:\my_app\ib_util.dll
  c:\my_app\icudt30.dll
  c:\my_app\icuin30.dll
  c:\my_app\icuuc30.dll
  c:\my_app\firebird.conf
  c:\my_app\firebird.msg
  c:\my_app\intl\fbintl.dll
  c:\my_app\intl\fbintl.conf
  c:\my_app\udf\fbudf.dll
        

  c:\my_app\app.exe
  c:\my_app\gds32.dll
  c:\my_app\ib_util.dll
  c:\my_app\icudt30.dll
  c:\my_app\icuin30.dll
  c:\my_app\icuuc30.dll
  c:\my_app\firebird.conf
  d:\fb\firebird.msg
  d:\fb\intl\fbintl.dll
  d:\fb\intl\fbintl.conf
  d:\fb\udf\fbudf.dll
        

  RootDirectory = d:\fb
        

The following instructions describe the Classic installation. For installation of Superserver the "CS" in the package name is replaced by "SS". For example, the package FirebirdCS-2.0.0-nnnnn.i686.rpm is replaced by FirebirdSS-2.0.0-nnnnn.i686.rpm.

Log in as root, or open a root shell. In the example filenames, replace nnnnn with the build number of the kit you actually have.

  $rpm -ivh FirebirdCS-2.0.0-nnnnn.i686.rpm
        

  $tar -xzf FirebirdCS-2.0.0-nnnnn.tar.gz
  $cd FirebirdCS-2.0.0-nnnnn.i686
  $./install.sh
        

What the Linux install scripts will do

The Linux install scripts will

In addition to the standard install files the following scripts are provided in the bin directory of this release.-

If you need to uninstall, do it as root user. The following examples use Classic server but the same holds true for SuperServer by replacing the CS with SS.

  $rpm -e FirebirdCS-2.0.0
        

  $/opt/firebird/bin/uninstall.sh
        

Install Firebird Classic & SuperServer on Solaris 2.7 Sparc, not currently available. Please refer older releasenotes as a reference to 2.0 installations.

Install Firebird Classic on MacOS X / Darwin, not currently available. Please refer to older releasenotes as a reference to 2.0 installations.

Not currently available. Please refer to older releasenotes as a reference to 2.0 installations.

Not currently available. Please refer to the relevant pages at the Debian site for your Debian version and Firebird 2.0 build.

(CORE-3736)     The WITH LOCK clause was allowed for users with read-only rights on a table, which would block others from updating it.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3730)     The API function isc_dsql_exec_immed2() would lose the value of an input parameter value when the request had a RETURNING clause.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3631)     Duplicate records with NULLs were being checked incorrectly.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3557)     The server could crash while preparing a query against a table that was being dropped.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3440)     The server could crash if isc_que_events() had queued events totalling zero.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3397)     The INTL library had some unresolved symbols.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3374)     The server could crash or corrupt data if SELECT WITH LOCK was issued against records not in the latest format.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3351)     The Windows client could write 10054 error messages into firebird.log at connection time.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3283)     Bad plans were generated when LEFT OUTER JOIN was used in a subquery.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3220)     isc_info_svc_get_users was returning error messages in the result cluster.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3205)     isc_dsql_exec_immed2() was not returning the error codes isc_stream_eof and isc_sing_select_err.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3202)     The execute_immediate API call family could crash the remote server.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3148)     SQZ_apply_differences contained some dangerous code.

fixed by D. Kovalenko, A. Peshkov

    ~ ~ ~     

(CORE-3137)     Partial rollback was possible for a selectable procedure modifying data.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3115)     Bugs were discovered in the internal record compression routines.

fixed by A. Peshkov, D. Kovalenko

    ~ ~ ~     

(CORE-3097)     Updating a BLOB field could cause the server to crash with an ACCESS_VIOLATION exception.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3067)     Objects were not being unmapped when the shared memory region was closed.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3064)     Using both the name of a procedure name and its alias inside an explicit plan would crash the server.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-3050)     A race condition in LocksCache::get() could lead to an engine crash.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-3022)     GPRE was throwing C++ compiler warnings with GCC 4.4.1.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3019)     Superserver failed to start on Linux Gentoo.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-3001)     POSIX install was failing to create the “firebird” user and group.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2977)     FB 2.1 and below handled indexed columns of type DATE incorrectly in old databases having on-disk structure lower than 10.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2900)     The server would crash due to an access violation when the DISTINCT aggregate was used.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2936)     Wrong page type (expected 7 found N) error.

If two consecutive leaf index pages were removed from an index (garbage collected) by two different connections at the same time, the linked list of sibling pages could become broken and the sibling pointer at another index page could point to the freed index page. When the freed page was again allocated, this index corruption would be reported.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2928)     Buffer overflow in gsec.

For reasons unknown, the gsec code copies the value of the password hash to an internal user data structure during a display operation. Since V.2.0, when the newer hash algorithm made the hash longer than previously, the buffer used for storing it could be too short.

This does not create a vulnerability because the hash value does not travel anywhere. It is harmless, anyway: the buffer overflow cannot be exploited because the first, middle and last names are filled immediately after the password. It is fixed now, thus avoiding having newer versions of glibc detecting this overflow.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2919)     The Linux installation script was ignoring non-standard ports.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2871)     If a derived table or a view contained both a left/right join and an ORDER BY clause and the outer query also contained an ORDER BY clause, the outer ORDER BY clause would have no effect.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2856)     A non-NULL key in a unique index could not be found when the key was removed

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2846)     When gfix -shut <mode> -attach <timeout> failed after the specified timeout due to connections being still active, it became impossible to connect to the database.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2831)     Database and user name should not be in the output when a script is extracted.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2822)     The error “no current row for fetch operation” was being thrown when a subquery included a non-trivial derived table.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2820)     Queries with PLAN ORDER were exhibiting small memory leaks as a side effect of an earlier, major fix.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2741)     Metadata extract would misinterpret the DDL of a CHECK constraint if the CHECK keyword was in any character mix other than all lower case or all upper case.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2720)     Division involving a divisor consisting of unary addition or subtraction expressions was being evaluated wrongly, often producing an incorrect result.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2698)     If a new cached lock is needed and the permitted number of cached locks is already used up, the least recently used lock should be released and its key should be reset to a new value. When the least recently used lock could not be unlocked because it was being held by some code for too long, the call to LocksCache::get would wait indefinitely.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2684)     “Error page NNN is of wrong type (expected 7, found N)” error would occur (wrongly) sometimes, due to a logic bug in garbage collection.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2648)     NBackup's delta file was not respecting the “Forced Writes” database setting.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2640)     Under some conditions, the lock manager could fail to detect a regular deadlock and cause the server to hang.

fixed by V. Khorsun, D. Yemanov

    ~ ~ ~     

(CORE-2635)     A unique index could be corrupted at level 1 if it contained a lot of NULL keys.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2616)     Error “page <N> is of wrong type (expected 7, found 5)” could occur under load, giving the impression that something had corrupted the database. On restart, there would be no evidence of corruption.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2591)     High mutex wait ratio and degraded performance would start to show up after a period of normal performance.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2563)     It was possible to shut down the Superserver's main port (3050 by default) by sending a malformed packet of some special format, that would lead to a Denial of Service condition for new incoming connections. This exploit could be used by an unauthenticated client.

Reported 15-Jul-2009 by Core Security Technologies.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2507)     A flagging issue on Windows server platforms was causing CreateFile() failures intermittently.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2449)     An unexpected “lock conflict” error could be thrown in lieu of the expected exception.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2444)     The engine could hang when multiple attachments registered their interest in events simultaneously and free space in the events table became exhausted.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2437)     A buffer overflow could occur on a client when events were being delivered.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2415)     Firebird could crash when the system ran out of temporary space

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2411)     The optimizer in v.2.0.5 would choose a slower PLAN for certain types of query than it would in version 2.0.4.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2395)     Problem in the API with handling UTF-8 4-byte characters for Japanese collations.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2368)     An isc_cancel_events() call would be succeeded by an access violation if the event was not found.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2355)     Incorrect handling of LOWER/UPPER when result string shrinks in terms of byte length.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2354)     “fb_lock_print -ia” output was not being flushed to the file between iterations.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2326)     Committing a new user object (a view, for example) caused an access violation if a user-defined trigger had been applied to the system table RDB$RELATIONS.

It should be noted that no Firebird server version either supports, or retains after a backup and restore, any user-defined trigger on a system table. The strong recommendation against defining such triggers remains. The fix recognises one way that user interference with system tables can compromise internal operations and disarms it.

The ability to define “DDL triggers” through the regular DDL mechanisms is on the drawing board for V.3.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2306)     Superserver could terminate abnormally when some worker thread failed to start.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2291)     The error Bugcheck 284 (cannot restore singleton select data) would be thrown on bad trigger code involving [FOR] SELECT, when the engine should have been detecting the error and throwing the proper exception.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2282)     Truncating UDFs were broken for negative numbers below -1.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2281)     Rounding UDFs were broken for negative numbers.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2272)     The server would start returning garbage when killing an events connection attempt.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2271)     The gfix utility had a legacy bug that exhibited itself during the database validation/repair routines on large databases. The privilege level of the user running these routines was being checked too late in the operation, thus allowing a non-privileged user (i.e., not SYSDBA or Owner) to start a validation operation. Once the privilege check occurred, the database validation could halt in mid-operation and thus be left unfinished, resulting in logical corruption that might not have been there otherwise.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2270)     When run in a zlogin console, isql would consume all memory and crash.

fixed by J. Swierczynski, A. Peshkov

    ~ ~ ~     

(CORE-2247)     In the QLI utility, message and descriptor buffers were not properly aligned.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2245)     A database with long exception messages defined would exhibit errors when being restored from a backup.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2173)     The server would crash after an abnormal disconnection if there was an open ExecuteStatement call.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2157)     Known issue: a bug in gcc 3.2.x, the compiler used to build the official x86 Linux packages, can cause problems when people try to build binaries that depend on the Firebird client without using the -pthread switch. Setting the -pthread switch removes the dependency of the output binary on libpthread.

Reported by A. Peshkov

    ~ ~ ~     

(CORE-1961)     A Bugcheck 210 (page in use during flush) consistency check error would be thrown during database validation.

fixed by D. Yemanov, R. Simakov

    ~ ~ ~     

(CORE-1923)     On Windows, successful execution of instsvc.exe remove was returning 1 as its completion code, instead of 0.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1089)     Selecting from a view that used DISTINCT and LEFT JOIN returned records in the wrong order if the ORDER BY clause did not include columns from the right-side (non-mandatory) table.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-195)     Regression of an old bug, previously fixed in v.1.5.1, whereby a bugcheck 291 (cannnot find back record version) would occur when updating the same record that had already fired an action in a BEFORE UPDATE trigger. The regression that was reintroduced in v.2.0 was less destructive, insofar as it affected only the record that was physically first in the table.

fixed by A. Peshkov

    ~ ~ ~     

Unregistered bug     When Firebird is configured to run in some specific directory (/usr/local/firebird, /opt/firebird or any other) the @prefix@ macro should be substituted with that directory path. On MacOS it was not done and caused exceptions to be thrown when the engine tried to locate some of its components.

fixed by P. Beach

    ~ ~ ~     

(CORE-2223)     gbak was encountering several bugs when operating on the access control lists (ACLs) that store SQL privileges.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2221)     On POSIX platforms, any attachment to any database would fail after the access rights for security2.fdb were modified from 0660 to 0666.

fixed by P. Beach, A. Peshkov

    ~ ~ ~     

(CORE-2108)     When using the new implementation of Windows local protocol (XNET), the next available map number was calculated incorrectly, thus allowing the server to try to reuse a map number that already existed. If the “new” map's timestamp was equal to the timestamp of the pre-existing map, it would cause the get_free_slot() function to fail.

fixed by V. Horsun

    ~ ~ ~     

(CORE-2078)     The optimizer always had some trivial heuristics to estimate the effective stream selectivity, even if no indices could be used for the retrieval. This code missed being migrated into the ODS11 optimizer logic. The effect was that join orders chosen for cases involving non-indexed predicates were likely to be ineffective.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2077)     On POSIX platforms, the Classic server in embedded mode, i.e., loaded into the user's application space, would handle the TERM signal but would fail to call any other signal handlers in the queue. The effect was that signal handlers set by the application were not executed and the application would keep working after the termination. It was a bad idea to invoke ISC_signal_cancel() from the signal handler and the mechanism has been reworked.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2073)     The implementation of expression indexes exhibited a bug whereby an incorrect result was returned when an inverted Boolean predicate was applied to test an indexed expression.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2065)     The MacOSX installation package was in violation of platform rules by not including the client library in the dynamic loader search paths.

fixed by P. Beach

    ~ ~ ~     

(CORE-2055)     Backported a fix for a known buffer overflow in the Firebird client library.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2050)     Fixed a performance regression resulting from a surfeit of semop() system calls.

fixed by V. Horsun

    ~ ~ ~     

(CORE-2049)     Fixed a performance regression resulting from a surfeit of sigprocmask() system calls.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2000)     Under high load conditions, the lock manager could report false deadlocks.

fixed by V. Horsun

    ~ ~ ~     

(CORE-1984)     Lock manager would report false deadlocks if one of the deadlock participants was in WAIT with a permitted timeout.

fixed by V. Horsun

    ~ ~ ~     

(CORE-1983)     In any POSIX environment except Solaris, the engine was mishandling the “out of memory” condition, causing the server to crash.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1982)     Simultaneous backups or restores using the Services API under Superserver could interfere with one another.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1972)     A non-SYSDBA user was able to change the Forced Writes mode of any database, along with several other database characteristics that should be restricted to the SYSDBA. This long-standing, legacy loophole in the handling of DPB parameters could lead to database corruptions or give ordinary users access to SYSDBA-only operations. The changes could affect several existing applications, database tools and connectivity layers (drivers, components). Details are in Chapter 3, in Changes to the Firebird API and ODS.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1970)     A “Lock conversion denied (bugcheck 215)” error could occur. This fix is related to CORE-1984 and CORE-2000 (above).

fixed by V. Horsun

    ~ ~ ~     

(CORE-1958)     When attempting to update the same record multiple times, a “Bugcheck 179 (decompression overran buffer)” failure could occur.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1957)     Because of a change done in the conversion to C++ at v.1.5, ACLs (Access Control Lists) longer than about 20 characters were being truncated. This has caused particular problems for applications that construct access privileges in run-time and has also given rise to privileges “going missing” when there are more than about 2000 privileges (for a report of the latter, see Tracker issue CORE-216).

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1930)     In a situation where a stored procedure was altered to remove output parameters and dependent procedures are not recompiled, the engine should properly track the dependencies and return an exception when the altered procedure is called. Instead, it was crashing.

fixed by V. Horsun

    ~ ~ ~     

(CORE-1919)     Memory corruptions in EXECUTE STATEMENT could crash the server.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1909)     Garbage text was being printed to firebird.log on AMD64 Linux.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1887)     Newly created databases were being created on POSIX platforms with the wrong access rights. Now, access rights are set properly, by an explicit chmod call immediately after creation of the file.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1886)     On Windows Vista, the server would refuse to start as an application under a restricted user account.

This fix is a backport from the v.2.1 code that will need to be field-tested during RC.

fixed by N. Samofatov

    ~ ~ ~     

(CORE-1884)     Using expressions as the default values of input parameters for stored procedures could cause random server crashes.

fixed by V. Horsun

    ~ ~ ~     

(CORE-1854)     When using Unix native OS user authentication, the engine would return CURRENT_USER in the native (case-sensitive) form instead of the upper-cased form that Firebird user names should be resolved to.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1844)     Valgrind often reports “Conditional jump or move depends on uninitialised value(s)” in check_status_vector(), caused by poor data type matching which had the potential to corrupt the error status vector when there were multiple errors.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1841)     A view that used derived tables and long names for the tables or aliases could cause an overflow in RDB$VIEW_RELATIONS.RDB$CONTEXT_NAME.

fixed by V. Horsun