FirebirdSQL logo
 Data Definition Language (DDL)Procedural SQL (PSQL) 

Allow parenthesized query expressions

Adriano dos Santos Fernandes

Tracker ticket: #6740

The DML syntax was extended to allow a parenthesized query expression (select including order by, offset and fetch clauses, but without with clause) to occur where previously only a query specification (select without with, order by, offset and fetch clauses) was allowed.

This allows more expressive queries, especially in UNION statements, and offers more compatibility with statements generated by certain ORMs.

Note

Using parenthesized query expressions comes at a cost, as they consume an additional query context compared to a plain query specification.The maximum number of query contexts in a statement is 255.
Example
(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

Support PLAN and ORDER BY on MERGE

The MERGE statement now supports the PLAN and ORDER BY clauses.

Syntax
MERGE INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join_condition>
  <merge_when> [<merge_when> ...]
  [PLAN <plan-expr>]
  [ORDER BY <ordering-list>]
  [RETURNING <returning_list> [INTO <variables>]]

Support PLAN, ORDER BY and ROWS on UPDATE OR INSERT

The UPDATE OR INSERT statement now supports the PLAN, ORDER BY and ROWS clauses.

Syntax
UPDATE OR INSERT INTO
  target [(<column_list>)]
  [<override_opt>]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [PLAN <plan-expr>]
  [ORDER BY <ordering-list>]
  [ROWS <m> [TO <n>]]
  [RETURNING <returning_list> [INTO <variables>]]

OPTIMIZE FOR Clause

Dmitry Yemanov

SELECT statements now support the OPTIMIZE FOR clause.

Syntax
SELECT
  ...
  [WITH LOCK [SKIP LOCKED]]
  [OPTIMIZE FOR {FIRST | LAST} ROWS]

The OPTIMIZE FOR clause can only occur on a top-level SELECT.

This feature allows the optimizer to consider another (hopefully better) plan if only a subset or rows is fetched initially by the user application (with the remaining rows being fetched on demand), thus improving the response time.

It can also be specified at the session level using the SET OPTIMIZE management statement.

The default behaviour can be specified globally using the OptimizeForFirstRows setting in firebird.conf or databases.conf.

Changes to literals

Full SQL standard character string literal syntax

Adriano dos Santos Fernandes

Tracker ticket: #5589

The syntax of character string literals was changed to support the full SQL standard syntax.This means a literal can be “interrupted” by whitespace or a comment.This can be used, for example, to break up a long literal over several lines, or provide inline comments.

<character string literal> ::=
  [ <introducer> <character set specification> ]
    <quote> [ <character representation>... ] <quote>
    [ { <separator> <quote> [ <character representation>... ] <quote> }... ]

<separator> ::=
  { <comment> | <white space> }...
— ISO/IEC 9075-2:2016 SQL - Part 2: Foundation
Examples
-- whitespace between literal
select 'ab'
       'cd'
from RDB$DATABASE;
-- output: 'abcd'

-- comment and whitespace between literal
select 'ab' /* comment */ 'cd'
from RDB$DATABASE;
-- output: 'abcd'

Full SQL standard binary string literal syntax

Adriano dos Santos Fernandes

Tracker ticket: #5588

The syntax of binary string literals was changed to support the full SQL standard syntax.This means a literal can contain spaces to separate hexadecimal characters, and it can be “interrupted” by whitespace or a comment.This can be used, for example, to make the hex string more readable by grouping characters, or to break up a long literal over several lines, or provide inline comments.

<binary string literal> ::=
  X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>
    [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
    <hexit> [ <space>... ] }... ] <quote> }... ]
— ISO/IEC 9075-2:2016 SQL - Part 2: Foundation
Examples
-- Group per byte (whitespace inside literal)
select _win1252 x'42 49 4e 41 52 59'
from RDB$DATABASE;
-- output: BINARY

-- whitespace between literal
select _win1252 x'42494e'
                 '415259'
from RDB$DATABASE;
-- output: BINARY
Note

The usage of the _win1252 introducer in above example is a non-standard extension and equivalent to an explicit cast to a CHAR of appropriate length with character set WIN1252.