Firebird-5-Release-Notes | Support PLAN, ORDER BY And ROWS On UPDATE OR INSERT

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