Development News - June 2025

PERCENT_RANK may return NaN instead of 0

GitHub Issue: 8589 Affected versions: 4.0.5, 5.0.2, 6.0 Initial Fixed for: 4.0.6, 5.0.3, 6.0 Alpha 1

If the partition has one row, the PERCENT_RANK should be 0, not NaN.

Presence of 'ROWS <n_limit>' causes garbage in error message when string conversion problem raises

GitHub Issue: 8592 Affected versions: 4.0.5, 5.0.2, 6.0 Initial Fixed for: 4.0.6, 5.0.3, 6.0 Alpha 1

Conflict resolution code uses constraint name instead of index name

GitHub Issue: 8139 Affected versions: 4.0.5, 5.0.2, 6.0 Initial Fixed for: 4.0.6, 5.0.3, 6.0 Alpha 1

Line "BLR to Source mapping:" may look broken when procedures.rdb$debug_info is queried using remote protocol

GitHub Issue: 8590 Affected versions: 4.0.5, 5.0.2, 6.0 Initial Fixed for: 4.0.6, 5.0.3, 6.0 Alpha 1

IN predicate incorrectly handles single parenthesized subquery as IN-list, instead of table subquery

GitHub Issue: 8182 Affected versions: 3.0.11, 4.0.5, 5.0.2, 6.0 Initial Fixed for: 3.0.13, 4.0.6, 5.0.3, 6.0 Alpha 1

Currently IN with a parenthesized subquery is incorrect handled as an IN-list with a single value, instead of a table subquery.

The following use of IN with a subquery will work fine:

select *
from RDB$CHARACTER_SETS
where RDB$CHARACTER_SET_ID in (
  select RDB$CHARACTER_SET_ID
  from RDB$COLLATIONS
)

However, adding another set of parentheses in the IN, will make the parser choose the IN-list alternative instead of the IN-subquery route.

As a result, using

select *
from RDB$CHARACTER_SETS
where RDB$CHARACTER_SET_ID in ((
  select RDB$CHARACTER_SET_ID
  from RDB$COLLATIONS
))

will result in error:

multiple rows in singleton select [SQLState:21000, ISC error code:335544652]

This is essentially an ambiguity in the syntax BNF of 8.4 <in predicate> of ISO 9075-2:2023, but the SQL standard has an explicit Syntax Rule to address this ambiguity.

BNF syntax (ambiguous):

<in predicate> ::=
  <row value predicand> <in predicate part 2>

<in predicate part 2> ::=
  [ NOT ] IN <in predicate value>

<in predicate value> ::=
    <table subquery>
  | <left paren> <in value list> <right paren>

<in value list> ::=
  <row value expression> [ { <comma> <row value expression> }... ]

One of the <row value expression> productions is <scalar subquery>. Both <table subquery> and <scalar subquery> resolve to <subquery>, which resolves to (<query expression>), hence the error if it doesn't produce a singleton scalar value.

Syntax Rule 1 (resolving the ambiguity):

1. If <in value list> consists of a single <row value expression>, then that <row value expression> shall not be a <scalar subquery>.

   NOTE 337 — This Syntax Rule resolves an ambiguity in which <in predicate value> might be interpreted either as a <table subquery> or as a <scalar subquery>. The ambiguity is resolved by adopting the interpretation that the <in predicate value> will be interpreted as a <table subquery>.

Add sub routines info in the BLR debug blob filter

GitHub Issue: 8579 Apply to: 6.0 Alpha 1

Increase MIN_PAGE_SIZE to 8192

GitHub Issue: 8406 Apply to: 6.0 Alpha 1

With changes in system indexes for the support of schemas, page size of 4096 cannot be used anymore.

Add procedure RDB$SQL.PARSE_UNQUALIFIED_NAMES

GitHub Issue: 8405 Apply to: 6.0 Alpha 1

RDB$SQL.PARSE_UNQUALIFIED_NAMES is a selectable procedure that parses a list of unqualified SQL names and returns one row for each name. The input must follow parse rules for names and the output of unquoted names are uppercased.

select *
  from rdb$sql.parse_unqualified_names('schema1, schema2, "schema3", "schema 4", "schema ""5"""');

-- SCHEMA1
-- SCHEMA2
-- schema3
-- "schema 4"
-- "schema "5"

Add support for SQL Schemas

GitHub Issue: 1113 Apply to: 6.0 Alpha 1

Syntax rules for ambiguous name resolution

GitHub Issue: 8439 Apply to: 6.0 Alpha 1

<schema>.<object> (a schema and its object)
<package>.<object> (a package and its object)

<name> % { SCHEMA | PACKAGE } . <name>

select *
    from plg$profiler%schema.plg$prof_sessions;

execute procedure rdb$profiler%package.pause_session;

call rdb$profiler%package.pause_session();

select rdb$time_zone_util%package.database_version()
    from system%schema.rdb$database;

select *
    from rdb$time_zone_util%package.transitions('America/Sao_Paulo', timestamp '2017-01-01', timestamp '2019-01-01');