Development News - April 2025

Conversion Error with old.field in UPDATE OR INSERT

GitHub Issue: 8508 Affected versions: 5.0.2, 6.0 Initial Fixed for: 5.0.3, 6.0 Alpha 1

When using aliases for old.field in UPDATE OR INSERT, we encounter the error: conversion error from string “999”. This error only occurs when the value is longer than 1 character and the value already exists in the table via MATCHING (i.e., old.value is not null).

The issue occurs specifically with old.t_key as o, while new works correctly. Using CAST helps resolve the issue. (returning cast(old.t_key as int) as o, new.t_key as n)

This issue was not present in Firebird version 3.10.

CREATE DOMAIN PKEY_test AS INTEGER NOT NULL;


CREATE TABLE test_pkey (
    t_key PKEY_test,
    CONSTRAINT pk PRIMARY KEY (t_key)
);

UPDATE OR INSERT INTO test_pkey (t_key)
VALUES (999)
MATCHING (t_key)
RETURNING old.t_key as o, new.t_key as n

"Error creating private namespace" message in firebird.log

GitHub Issue: 8509 Affected versions: 4.0.3, 5.0.2, 6.0 Initial Fixed for: 4.0.6, 5.0.3, 6.0 Alpha 1

firebird.log contains message:

Error creating private namespace
operating system directive OpenPrivateNamespace failed
The system cannot find the path specified.

This could happens on Windows when Firebird process starting while another Firebird process exiting. Most probably with Classic architecture.

Error reason is possible race condition when starting process creates private namespace while exiting process destroy it.

Error in iTransaction.getInfo() on embedded connection

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

A call to iTransaction.getInfo() with request code 11 (DB_PATH) returns dsn of connected database (host:db_path) when connected via network protocol, but raises error "Incorrect parameters provided to internal function INF_transaction_info" when called with embedded connection.

Python test code:

with db_connection.main_transaction as tr:
    print(tr.info.database)

Range-based FOR statement

GitHub Issue: 8498

[<label> :]
FOR <variable> = <initial value> {TO | DOWNTO} <final value> [BY <by value>] DO
    <statement>

execute block returns (out integer)
as
begin
    for out = 1 to 3 do
        suspend;
end

/* Result:
1
2
3
*/

execute block returns (out integer)
as
begin
    for out = 9 downto 7 do
        suspend;
end

/* Result:
9
8
7
*/

execute block returns (out integer)
as
begin
    for out = 5 to 3 do
        suspend;
end

/* Result:
*/

execute block returns (out numeric(5,2))
as
begin
    for out = 9 downto 7 by 0.5 do
        suspend;
end

/* Result:
9.00
8.50
8.00
7.50
7.00
*/

execute block
as
    declare i integer;
begin
    for i = 1 to 10 do
    begin
        insert into table1 values (i);
        insert into table2 values (i);
    end
end

/* Result:
10 records inserted into table1
10 records inserted into table2
*/

Built Firebird binaries with minimal debug info (-g1 on analog) in addition to separate (full) debug symbols

GitHub Issue: 8527

Level 1 produces minimal information, enough for making backtraces ...

Full debug info are very big and mainly no sense for "plain users", but backtraces often need for first time analysis.

Add identification for connection created by gbak -se -par -b

GitHub Issue: 8533

For now no reliable way identify connection created by "request" of gbak -se -par -b May be set some special name for such connection?

Convert IN subquery into UNIQUE INNER JOIN

GitHub Issue: 8531

Currently we are able to convert IN into a semi-join:

select * from t1 where t1.fld1 in (select fld2 from t2);

is executed like:

select * from t1 semi join t2 on t1.fld1 = t2.fld2;

The problem here is that if table t1 is large and the IN predicate is selective (often it contains PK values), then the whole table t1 is read. Instead, it makes more sense to use the subquery as a pre-filter with a plan like this:

select * from t2 lateral join (select * from t1 where t1.fld1 = t2.fld2);

or simply INNER JOIN with the join order to be chosen by the optimizer.

If the subquery returns a non-unique field, a DISTINCT should be applied implicitly, but it's still likely to be cheaper (for a small t2) than a t1 fullscan.

The major problem here is to estimate the subquery cardinality before joining, currently we have no such ability. Without that it's impossible to decide whether DISTINCT + INNER JOIN is cheaper than SEMI JOIN.