Firebird Documentation IndexFirebird 3.0.2 Release NotesData Manipulation Language (DML) → DML Improvements
Firebird Home Firebird Home Prev: Internal FunctionsFirebird Documentation IndexUp: Data Manipulation Language (DML)Next: Procedural SQL (PSQL)

DML Improvements

Alternatives for Embedding Quotes in String Literals
SQL:2008-Compliant OFFSET and FETCH Clauses
Prohibit Edgy Mixing of Implicit/Explicit Joins
Support for Left-side Parameters in WHERE Clause
Enhancements to the RETURNING Clause
Cursor Stability
An Improvement for GTTs
An Improvement for DML Strings
COUNT() Now Returns BIGINT
Optimizations
Dialect 1 Interface
Embedded SQL (ESQL) Enhancements

A collection of useful DML improvements is released with Firebird 3.

Alternatives for Embedding Quotes in String Literals

Adriano dos Santos Fernandes

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>
        

Rules

When <alternate start char> is '(', '{', '[' or '<', <alternate end char> is paired up with its respective “partner”, viz. ')', '}', ']' and '>'. In other cases, <alternate end char> is the same as <alternate start char>.

Inside the string, i.e., <char> items, single (not escaped) quotes could be used. Each quote will be part of the result string.

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
        

SQL:2008-Compliant OFFSET and FETCH Clauses

Mark Rotteveel

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.

  • The OFFSET clause specifies the number of rows to skip

  • The FETCH clause specifies the number of rows to fetch.

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;
      

Notes

  1. The FIRST/SKIP and ROWS clauses are non-standard alternatives.

  2. The OFFSET and/or FETCH clauses cannot be mixed with clauses from the FIRST/SKIP or ROWS alternatives in the same query expression.

  3. Expressions and column references are not allowed within either the OFFSET or the FETCH clause.

  4. Unlike the ROWS clause, OFFSET and FETCH are available only in SELECT statements.

  5. The “percentage FETCH” defined in the SQL standard is not supported.

  6. FETCH ... WITH TIES” defined in the SQL standard is not supported.

Prohibit Edgy Mixing of Implicit/Explicit Joins

Dmitry Yemanov

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.

Support for Left-side Parameters in WHERE Clause

Adriano dos Santos Fernandes

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

Important

Better SQL coding practice would be to use EXISTS in these cases; however, developers were stumbling over this problem when using generated SQL from Hibernate, which used the undesirable style.

Enhancements to the RETURNING Clause

Adriano dos Santos Fernandes

Two enhancements were added to the RETURNING clause:

RETURNING Clause Value Can be Aliased

When using the RETURNING clause to return a value to the client, the value can now be passed under an alias.

Example Without and With Aliases

  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
          

Note

The keyword AS is optional.

RETURNING Clause from Positioned Updates and Deletes

Support has been added for a RETURNING clause in positioned (WHERE CURRENT OF) UPDATE and DELETE statements.

Example

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

Cursor Stability

Vlad Khorsun

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.

Important

This stabilisation does NOT work with SUSPEND loops in PSQL.

An Improvement for GTTs

Vlad Khorsun

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

Read-only transaction in read-write database

Writable in both ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS

Read-only transaction in read-only database

Writable in ON COMMIT DELETE ROWS only

Also

  • Rollback for GTT ON COMMIT DELETE ROWS is faster

  • Rows do not need to be backed out on rollback

  • Garbage collection in GTT is not delayed by active transactions of other connections

Note

The same refinements were also backported to Firebird 2.5.1.

An Improvement for DML Strings

Adriano dos Santos Fernandes

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

  • NONE—non-ASCII characters are transformed to question marks

  • Others—the string is checked for malformed characters

COUNT() Now Returns BIGINT

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

Optimizations

Optimizations made for this release included:

SIMILAR TO

Adriano dos Santos Fernandes

The performance of SIMILAR TO was improved.

OR'ed Parameter in WHERE Clause

Dmitry Yemanov

Performance for (table.field = :param or :param = -1) in the WHERE clause was enhanced.

Better Choices for Navigation

Dmitry Yemanov

Previously, when an ORDER plan was in a SELECT structure, the optimizer would choose the first index candidate that matched the ORDER BY or GROUP BY clause. This “first come” approach is not the best when multiple index choices are available. The Firebird 3 engine surveys all of the available choices and picks the most suitable index.

See Tracker ticket CORE-4285.

Plainer Execution Path for UNION Queries

Dmitry Yemanov

Previously, the execution path for UNION queries was hierarchical, often causing redundant reads. This optimization replaces the hierarchical execution path with a plainer one that improves performance.

See Tracker ticket CORE-4165.

Index Walk for Compound Index

Dmitry Yemanov

The optimizer now allows an index walk (ORDER plan) when a suitable compound index (A, B) is available for a query condition of the style WHERE A = ? ORDER BY B.

See Tracker ticket CORE-1846.

Performance Improvement for SET STATISTICS INDEX

Vlad Khorsun

BTR_selectivity() would walk the whole leaf level of given index b-tree to calculate index selectivity. Throughout the process, the only rescheduling would happen at a disk I/O operation. The effect was to impose long waits for AST requests from concurrent attachments, such as page lock requests, monitoring, cancellation, etc. An improvement in Firebird 3 seems to solve that problem.

See Tracker ticket CORE-1846.

Dialect 1 Interface

Adriano dos Santos Fernandes

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

See Tracker CORE-3972

Embedded SQL (ESQL) Enhancements

Dmitry Yemanov

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

Support for UPDATE OR INSERT statement

See Tracker ticket CORE-4438

Support for the RETURNING clause

See Tracker ticket CORE-4437

Prev: Internal FunctionsFirebird Documentation IndexUp: Data Manipulation Language (DML)Next: Procedural SQL (PSQL)
Firebird Documentation IndexFirebird 3.0.2 Release NotesData Manipulation Language (DML) → DML Improvements