Monthly News - January 2024

Inner join raises error "no current record for fetch operation"

GitHub Issue: 7937 Affected versions: 3.0.11, 4.0.4, 5.0 RC2 Fixed for: N/A

Inner join raises error "no current record for fetch operation" if a stored procedure depends on some table via input parameter and also has an indexed relationship with another table

Inconsistent state of master-detail...

GitHub Issue: 7899 Affected versions: 3.0.11, 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Alpha 1

Inconsistent state of master-detail occurs after RE-connect + 'SET AUTODDL OFF' + 'drop <FK>' which is ROLLED BACK

Discovered by Firebird QA.

The issue is related with inconsistency in relation metadata in memory. Right after (re)connect only basic relation's metadata is loaded into metadata cache. Some parts is delayed until really required. In this case information about partners (PKFK references) is not loaded from disk.

When FK is dropped, engine delete corresponding rows in system tables and in this process load missing metadata - but too late, after rows in RDB$RELATION_CONSTRAINTS was deleted. Thus relation have incomplete metadata in memory.

If transaction rolled back, relation still missing information about its partners and didn't check FK on DELETE.

replication.log remains empty...

GitHub Issue: 7899 Affected versions: 3.0.11, 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Alpha 1

replication.log remains empty (and without any error in firebird.log) until concurrent FB instance is running under different account and generates segments on its master. Significant delay required after stop concurrent FB it in order allow first one to write in its replication log.

Discovered by Firebird QA.

Wrong memory buffer alignment and IO buffer size when working in direct IO mode

GitHub Issue: 7899 Affected versions: 3.0.11, 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Alpha 1

User reports that nbackup issues error when backup database:

IO error reading file: database.fdb The parameter is incorrect.

Investigation shows that:

• The OS is Windows,
• error happens with direct IO ON only (switch -D ON),
• volume where database file reside have following properties (I show important ones only):

Bytes Per Sector : 4096 Bytes Per Physical Sector : 4096

Unable to restore database when .fbk was created on host with other ICU

GitHub Issue: 7950 Affected versions: 3.0.11, 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Alpha 1

Restore of wide table can fail with "adjusting an invalid decompression length from <N> to <M>"

GitHub Issue: 7974 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Alpha 1

Non-correlated sub-query is evaluated multiple times...

GitHub Issue: 7863 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Alpha 1

Non-correlated sub-query is evaluated multiple times if it is based on a VIEW rather than on appropriate derived table.

False validation error for short unpacked records

GitHub Issue: 7976 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Alpha 1

The problem is in validation code that not correctly handle short unpacked records (new in ODS 13.2)

How to reproduce:

firebird>isql
SQL> create database 'err.fdb';
SQL> create table tmp1(a1 integer);
SQL> insert into tmp1 values (1000);
SQL> commit;
SQL> exit;

firebird>gfix -v -full err.fdb
Summary of validation errors

    Number of record level errors   : 1

firebird.log contains:

Error: Record 0 is wrong length in table TMP1 (128)

System procedure/function inconsistency between ISQL SHOW FUNCTIONS and SHOW PROCEDURES

GitHub Issue: 7962 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Alpha 1

In Firebird 5.0.0.1306 ISQL, the SHOW FUNCTIONS command lists system functions in packages, while SHOW PROCEDURES correctly does not list system procedures in packages.

SHOW FUNCTIONS should be changed to exclude system functions (from RDB$BLOB_UTIL, RDB$PROFILER and RDB$TIME_ZONE_UTIL), as those should be queried with SHOW SYSTEM FUNCTIONS.

Reproduction:

create database 'e:\db\fb5\showfunc.fdb' user sysdba;
set term #;
-- including some functions and package functions for demonstration
create function dummy() returns integer as begin return 1; end#
CREATE OR ALTER PACKAGE APP_VAR
AS
BEGIN
   FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC;
   FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC;
END#
set term ;#

Now, when executing SHOW FUNCTIONS, the output includes the system functions:

SQL> show functions;
DUMMY
APP_VAR.GET_DATEBEGIN
APP_VAR.GET_DATEEND
RDB$BLOB_UTIL.IS_WRITABLE
RDB$BLOB_UTIL.NEW_BLOB
RDB$BLOB_UTIL.OPEN_BLOB
RDB$BLOB_UTIL.READ_DATA
RDB$BLOB_UTIL.SEEK
RDB$PROFILER.START_SESSION
RDB$TIME_ZONE_UTIL.DATABASE_VERSION

However, the expected output should exclude the system functions, that is, it should produce:

DUMMY
APP_VAR.GET_DATEBEGIN
APP_VAR.GET_DATEEND

Update Windows distributions with zlib version 1.3.1

GitHub Issue: 7978 Apply to: 4.0.5, 5.0.1, 6.0 Alpha 1

Cleanup RelaxedAliasChecking configuration setting

GitHub Issue: 7982 Apply to: 6.0 Alpha 1

This backward compatibility option was added after the v2.0 release, I believe 15 years was enough for migration and now it's a good time to wipe the thing out.

Remove FileSystemCacheThreshold setting

GitHub PR: 7983 Apply to: 6.0 Alpha 1

Remove FileSystemCacheSize setting

GitHub PR: 7984 Apply to: 6.0 Alpha 1

More cursor-related details in the plan output

Pull Request: 7441 Apply to: 5.0.0

• Main queries are still reported as "select expression", while its sub-queries are reported as "sub-query", and declared cursors are reported as "cursor ".

• Invariant sub-queries and scrollable cursors are reported as such (see also 7390).

• If the plan is requested for EXECUTE BLOCK or some cached PSQL object (procedure/function/trigger), every select expression inside that PSQL module is prefixed with its position (line/column numbers).

  Examples:

  -- line 23, column 2
  PLAN (DISTRICT INDEX (DISTRICT_PK))
  -- line 28, column 2
  PLAN JOIN (CUSTOMER INDEX (CUSTOMER_PK), WAREHOUSE INDEX(WAREHOUSE_PK))
  
  Select Expression (line 23, column 2)
      -> Singularity Check
          -> Filter
              -> Table "DISTRICT" Access By ID
                  -> Bitmap
                      -> Index "DISTRICT_PK" Unique Scan
  Select Expression (line 28, column 2)
      -> Singularity Check
          -> Nested Loop Join (inner)
              -> Filter
                  -> Table "CUSTOMER" Access By ID
                      -> Bitmap
                          -> Index "CUSTOMER_PK" Unique Scan
              -> Filter
                  -> Table "WAREHOUSE" Access By ID
                      -> Bitmap
                          -> Index "WAREHOUSE_PK" Unique Scan
  

• If you want php-firebird window builds for PHP 8.3, it's here:

SQL standard FORMAT clause for CAST between string types and datetime types

GitHub Issue: 2388 Apply to: 6.0 Aplha 1

Implement SQL standard FORMAT clause for CAST between string types and datetime types, to allow custom formatting of datetime values and conversion from string values with a specific format to datetime values.

DROP [IF EXISTS] ...

GitHub Issue: 4203 Apply to: 6.0 Aplha 1

Allow DEFAULT keyword in argument list

GitHub Issue: 7566 Apply to: 6.0 Aplha 1

When routine has many parameters and you want to pass non-default values for the last ones, without DEFAULT you need to pass every parameter up to the last informed.

SQL standard allows to use DEFAULT to help in this case:

func(1, 2, default, default, 3)

SQL spec:

<SQL argument> ::=
    <value expression>
    ...
    | <contextually typed value specification>
    | <named argument specification>

<named argument specification> ::=
    <SQL parameter name> <named argument assignment token>

<named argument SQL argument> ::=
    <value expression>
    | <target specification>
    | <contextually typed value specification>

<contextually typed value specification> ::=
    ...
    | <default specification>

<default specification> ::=
    DEFAULT

This feature is about functions and procedures (in execute and select).

Make it possible to handle attach errors in key holder plugin

GitHub Issue: 7951 Apply to: 6.0 Aplha 1

In some cases (like encryption key entered by user in dialogue mode) it's very useful to be able to handle attach errors in key holder in order to be able to repeat with modified key value.

Remove context limit for stored procedures/functions/triggers as well as for user SQL queries

GitHub Issue: 1195 Apply to: Seriously considered for 6.0

If a stored procedure / trigger is too complex, you'll get the following error:

Invalid token. Invalid request BLR at offset xxxx.
"context not defined (BLR error)"
or
"Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256"

Every table/procedure reference means a "context" and their total count is limited by 256 contexts. Removing or extending this limit would allow more complex PSQL code to be developed.

Support ordered-set (WITHIN GROUP) aggregates

GitHub Issue: 7632

Need to add support for ordered-set and hypothetical-set aggregate functions, as well as implementations of the instances defined in SQL:2008 (percentile_cont(), percentile_disc(), rank(), dense_rank(), percent_rank(), cume_dist()). It is good to added mode() though it is not in the spec. In addition, you can gracefully solve the problem of element unordering in the LIST aggregate function (in the SQL standard LISTAGG).

Implement ability to explicitly validate constraints

GitHub Issue: 7966

When creating constraint on non-empty table it could take a significant time to check if new constraint is valid. During this check no modifications on table should be allowed.

Another approach is to create constraint in state "not validated" and allow further modifications of table data. Such constraint should be validated later using special SQL statement. New data entered into table is validated and, if check failed - not allowed, as usual. Existing data will be validated using snapshot and, if check passed, constraint state will be set to "validated".

It will be good to have a way to validate few constraints at one pass over table data.

Implement a way to build an index without blocking concurrent data modifications

GitHub Issue: 7967

Currently, when index is build, any modifications of table data is not allowed. This is required to create correct index not missing new keys inserted during the build.

The time when such read lock is required could be significantly shortened.

Create iOS embedded build

GitHub Issue: 7973

Looks like this is the only widely used platform w/o FB port.

Make FOREIGN KEY constraint to use any suitable index

GitHub Issue: 7964

Ability to automatically choose existing index to check FK constraint allows to lower number of indices and consume less disk and CPU resources.

Implement ability to check FOREIGN KEY constraint not using index on referencing table

GitHub Issue: 7965

When data in referenced (parent) table is mostly stable and changed rarely it makes sense to not build index on referencing (detail) table to check FK. In rare case when parent table is modified, read lock on detail table is enough to check FK constraint.

This allows to significantly lower number of indices on detail table(s) thus saving both disk and CPU resources.

Option for GEN_UUID to generate v7 UUID

GitHub Issue: 7980

UUID v7 more index-able than UUID v4(Current used in FirebirdSQL, CMIIW), please bring support for UUID v7 in FirebirdSql.

Restore service must rely on crypt plugin name instead of key holder name

GitHub Issue: 7947

Currently database is restored as encrypted only if both crypt plugin name and key holder name are provided in command line or service parameters. Providing crypt plugin name should be enough because key holder may be configured globally in firebird.conf, for particular database in databases.conf or not needed for the crypt plugin at all.

Backup should write encryption plugin and key name into backup file

GitHub Issue: 7943

When performing backup-restore of an encrypted database it is expected that by default the new database has all characteristics of source, including encryption flag, encryption plugin and key.

This issue is different from 6070 which is aimed to backup file only but related to 6660 which says that database encryption plugin and key must be applied to backup file automatically by default.

Problem with using a computed index on a computed column

GitHub Issue: 7945 Affected versions: 3.0.11

In short: relation have a lot of computed fields, most of them are trivial, but some depends on stored procedures that in turn depend on the same relation. When relation metadata is loaded for a very first time, this dependency circle makes some computed fields to incorrectly compute its expression, as not all computed fields have its expressions loaded at this moment. When index is creating, engine sees already loaded table's metadata, thus index get correct expression and works (until disconnect).

This is not final conclusion, just after a first look. It can't be fixed too easy and quickly, I'm afraid. Looks like more complex case of 6592