It is now possible to use a character, or character pair, other than the doubled (escaped) apostrophe, to embed a quoted string inside another string. The keyword q or Q preceding a quoted string informs the parser that certain left-right pairs or pairs of identical characters within the string are the delimiters of the embedded string literal.

Syntax

<alternate string literal> ::=
    { q | Q } <quote> <alternate start char> [ { <char> }... ] <alternate end char> <quote>
        

Examples

select q'{abc{def}ghi}' from rdb$database;        -- result: abc{def}ghi
select q'!That's a string!' from rdb$database;    -- result: That's a string
        

New SQL:2008 compliant OFFSET and FETCH clauses provide a standard equivalent for FIRST and SKIP, and an alternative for ROWS...TO, when fetching sets from ordered output.

As with SKIP and FIRST, OFFSET and FETCH clauses can be applied independently, in both top-level and nested query expressions. They are available in PSQL and DSQL.

Syntax Pattern

  SELECT ... [ ORDER BY <expr_list> ]
      [ OFFSET <simple_value_expr> { ROW | ROWS } ]
      [ FETCH { FIRST | NEXT } [ <simple_value_expr> ] { ROW | ROWS } ONLY ]
      

<simple_value_expr> is a (numeric) literal, a DSQL parameter (?) or a PSQL named parameter (:namedparameter) that resolves to an integer data type.

Examples

-- 1:
SELECT * FROM T1 ORDER BY COL1
  OFFSET 10 ROWS;
-- 2:
SELECT * FROM T1 ORDER BY COL1
  FETCH FIRST 10 ROWS ONLY;
-- 3:
SELECT * FROM (
               SELECT * FROM T1 ORDER BY COL1 DESC
               OFFSET 1 ROW
               FETCH NEXT 10 ROWS ONLY
               ) a
       ORDER BY a.COL1
       FETCH FIRST ROW ONLY;
      

While mixing of implicit and explict join syntaxes is not recommended at all, the parser would allows them, nevertheless. Certain “mixes” actually cause the optimizer to produce unexpected results, including “No record for fetch” errors. The same edgy styles are prohibited by other SQL engines and now they are prohibited in Firebird.

To visit some discussion on the subject, see the Tracker ticket CORE-2812.

The following style of subquery, with the parameter in the left side of a WHERE...IN (SELECT...) condition, would fail with the error “The data type of the parameter is unknown”.

This style is now accepted. For example:

SELECT <columns> FROM table_1 t1
  WHERE <conditions on table_1>
  AND (? IN (SELECT some_col FROM table_2 t2 WHERE t1.id = t2.ref_id))
      

Two enhancements were added to the RETURNING clause:

  UPDATE T1 SET F2 = F2 * 10
    RETURNING OLD.F2, NEW.F2; -- without aliases

  UPDATE T1 SET F2 = F2 * 10
    RETURNING OLD.F2 OLD_F2, NEW.F2 AS NEW_F2; -- with aliases
          

  UPDATE T1 SET F2 = F2 * 10 WHERE CURRENT OF C
    RETURNING NEW.F2;
          

Until this release, Firebird suffered from an infamous bug whereby a data modification operation could loop infinitely and, depending on the operation, delete all the rows in a table, continue updating the same rows ad infinitum or insert rows until the host machine ran out of resources. All DML statements were affected (INSERT, UPDATE, DELETE, MERGE). It occurred because the engine used an implicit cursor for the operations.

To ensure stability, rows to be inserted, updated or deleted had to be marked in some way in order to avoid multiple visits. Another workaround was to force the query to have a SORT in its plan, in order to materialize the cursor.

From Firebird 3, engine uses the Undo log to check whether a row was already inserted or modified by the current cursor.

Global temporary tables (GTTs) are now writable even in read-only transactions. The effect is as follows.-

Also

Strings in DML queries are now transformed or validated to avoid passing malformed strings to the engine internals, for example, to the MON$STATEMENTS.MON$SQL_TEXT column.

The solution adopted depends on the character set of the attachment.-

The COUNT() aggregator now returns its result as BIGINT instead of INTEGER.

Optimizations made for this release included:

Selection of SQL_INT64, SQL_DATE and SQL_TIME in dialect 1 was enabled.

See Tracker CORE-3972

Two enhancements were included in the Embedded SQL subset in this release:

Context variables added in v.3.0.4: