Monthly News - December 2023

Server must prevent "return BY VALUE" for data types ISC_TIMESTAMP_TZ, ISC_TIME_TZ and so on

GitHub Issue: 7883 Affected versions: 4.0.4, 5.0.0 Fixed for: N/A

Firebird prohibits the creation of UDF with "return BY VALUE" for data types ISC_TIMESTAMP, ISC_QUAD based on structs but allows it for:

FB_I128_t
FB_DEC34_t
FB_DEC16_t
ISC_TIMESTAMP_TZ
ISC_TIME_TZ

Unstable error messages in services due to races related with service status vector

GitHub Issue: 7885 Affected versions: 3.0.11, 4.0.4 Fixed for: 4.0.5, 5.0.0, 6.0 Aplha 1

Failed restore (using services API) can miss final message "Exiting before completion due to errors".

We have QA test for CORE-5771. It verifies that attempt to restore DB via services when target DB exists does not cause FB crash. Outcome if this test became unstable when ServerMode = SS since 6.0.0.100: message "-Exiting before completion due to errors" often is missed from expected output.

Segfault during TPC initialization

GitHub Issue: 7905 Affected versions: 4.0.4 Fixed for: 4.0.5, 5.0.0, 6.0 Aplha 1

Bug was introduced when fixing 7348.

When some error (for example - access denied to shared memory files) takes place in partially initialized TPC an attempt to access m_tpcHeader == nullptr causes segfault in StatusBlockData::clear() during error processing.

Crash "Fatal lock manager error: Process disappeared in LockManager::acquire_shmem"

GitHub Issue: 7809 Affected versions: 3.0.11, 4.0.4 Fixed for: 4.0.5, 5.0.0, 6.0 Aplha 1

Race condition issue, probably related to 7762, 7371 and 7530.

CRC32 returns inverted value

GitHub Issue: 7898 Affected versions: N/A Fix not available

CRC32 checksum (polynom 0x04C11DB7) for 'Firebird' is 0x023938D0 (3421780262). See here.

But Firebird function hash( 'Firebird' using CRC32 ) returns 0xD0383902 (-801621758). As you see it's inverted byte order value of true result.

CRC32 -> 0x023938D0
Firebird -> 0xD0383902

Firebird 4.0.3 Windows IA

Some default values is set incorrectly for SCCS architectures

GitHub Issue: 7927 Affected versions: 4.0.0, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Aplha 1

Default values for TempCacheLimit, DefaultDbCachePages and GCPolicy settings is set as if ServerMode = Super, even if ServerMode is set to Classic or SuperClassic.

The issue is with default values only, if mentioned settings is set explicitly - correct values is used.

Hang in case of error when sweep thread is attaching to database

GitHub Issue: 7917 Affected versions: 3.0.11, 4.0.0, 5.0 RC1, 6.0 Initial Fixed for: 3.0.12, 4.0.5, 5.0.0 RC2, 6.0 Aplha 1

Sweep thread very rarely has any errors on startup. But in a case when encrypted database to be swept the thread fails on attach except a case when shared between attachments key is provided by key holder plugin. This makes all attachments hang on detach (i.e. sweep thread exits not releasing appropriate lock).

ALTER TABLE ALTER COLUMN <textual_field> can not be changed properly in some cases

GitHub Issue: 7924 Affected versions: N/A Fix not available

In some cases ALTER TABLE ... ALTER COLUMN <textual_field> type ... <new_charset> can lead either to unchanged character set of <textual_field> or this character set is changed but COLLATE attribute gets value that can not be explained.

Changed plan (possible regression) in FB 5.x / 6.x comparing to FB 3.x / 4.x

GitHub Issue: 7909 Affected versions: 5.0 RC1, 6.0 Initial Fix not available

Obsolete ClearGTTAtRetaining option removed from firebird.conf

GitHub Issue: 7897 Apply to: 5.0.0, 6.0 Aplha 1

Compatibility options MaxIdentifierByteLength and MaxIdentifierCharLength removed from firebird.conf in v6

GitHub Issue: 7900 Apply to: 6.0 Aplha 1

Backward compatibility option that disables joins transformation added to firebird.conf in v5

GitHub Issue: 7910

Given a few tickets showing performance regressions after this improvement and feedback from users at the conference asking about some compatibility option to simplify migration in cases when LEFT JOINs were widely used intentionally just as optimizer hints -- here it is, a backward compatibility option is provided.

May act like a workaround for tickets 7681 and 7909.

Info about quotes in fbtrace.conf updated

Pull Request: 7914

String values should be enclosed into double quotes if contains spaces embedded, for example:

log_filename "C:\\Documents and Settings\\Firebird\\My Documents\\trace.log"

However, they are not required when name = value syntax is used, i.e. when = is not missed.

Make TempCacheLimit setting to be per-database, (not per-attachment) for SuperClassic

GitHub Issue: 7928 Apply to: 4.0.5, 5.0.1, 6.0 Aplha 1

Improvement 5984 have side-effect that breaks backward compatibility for SuperClassic architecture.

Current implementation makes TempCacheLimit as per-attachment limit, while it was per-process before.

When users migrates from previous versions to the FB4 (or later) and left TempCacheLimit setting tuned for the known SuperClassic memory usage, it is not expected that actual memory consumption could be a lot larger than before.

This ticket offers to make TempCacheLimit as common limit for all attachments to the same database in the given SuperClassic process.

It will not affect Classic and SuperServer architectures.

Provide ability to see info about auth plugin which was used for login in trace

GitHub Issue: 7901

Support preliminary/tentative index use when planning LIKE with a non-literal argument (parameter, column reference, etc)

GitHub Issue: 7912

Currently LIKE can only use parameter if it has a literal argument and it doesn't start with a wildcard. As soon as a parameter or column reference is used, an index can no longer be used.

Guess it could be possible to implement a way for LIKE to also use an index in those cases, conditionally on the actual value of the argument. That is, if the value doesn't start with a wildcard, it uses an index, and if it does start with a wildcard, a full scan is used.

Support for bit field in other way

GitHub Issue: 7358

Currently we have Boolean field. But sometimes it is too big overhead to have 1 byte for 1 bit information. 100 boolean fields are 100 bytes instead of only 100/8.

The best will be bit field datatype maintained by Firebird itself but i suppose below solution can be also interesting.

Currently we can use Integer field and use bin operations, and help self a little by computed fields e.g.:

CREATE TABLE TEST
(
  ID INTEGER NOT NULL PRIMARY KEY,
  FLAGS INTEGER NOT NULL DEFAULT 0,
  IS_STORED COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 0), 1)),
  IS_COMPOUND COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 1), 1)),
  IS_SOMETHING COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 2), 1)),
  ...
);

or to have boolean fields results

CREATE TABLE TEST
(
  ID INTEGER NOT NULL PRIMARY KEY,
  FLAGS INTEGER NOT NULL DEFAULT 0,
  IS_STORED COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 0), 1)=1),
  IS_COMPOUND COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 1), 1)=1),
  IS_SOMETHING COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 2), 1)=1),
  ...
);

But above is only for reading. You must do same BIN_AND operation and remember which bit to use to perform update. You cannot do:

UPDATE TEST SET IS_COMPOUND = TRUE WHERE ID=xxx;

But i see also other way to go. Introduce new syntax (only proposition of syntax, you can find the better probably)

CREATE TABLE TEST
(
  FLAGS INTEGER NOT NULL DEFAULT 0,
  IS_STORED  BIT 0 OF FLAGS,
  IS_COMPOUND BIT 1 OF FLAGS,
  IS_SOMETHING BIT 2 OF FLAGS,
);

And then you can simply support for bit informations for select and also for update statement. This have one advantage compared to bit datatype. You can still set all flags by simply updating FLAGS field itself.

Maybe syntax should be only as some extension for computed field e.g. IS_STORED COMPUTED BY BIT 0 OF FLAGS but it must be finally updateable.

Query issue conversion error from string

GitHub Issue: 7916 Affected versions: 4.0.4 Fixed for: N/A

A complex query that was working fine up until FB 4.0.2. Recently applied patch 4.0.4 and now the query fails with "conversion error from string 'xxx'".

Dmitry Yemanov thinks that the v4.0.2 optimizer could not detect that expression may actually use an index, while it became possible in v4.0.4. Thus this issue surfaced.

Unexpected (wrong?) fetch and load INTO variable logic

GitHub Issue: 7906

Looks like the full row evaluation/fetch not happens before loading values INTO variables, but the fetch+load happens for every column one after another.

It is not yet clear whether this is a deviation from the SQL standard or not. So far open for discussion.

Building a result can cause memory overload

GitHub Issue: 7913

Remote protocol 13 (FB3+) allows to operate with 32bit number of buffer sizes.

To say more exactly, the maximum size of data can be up to 0xFFFF0000-1 bytes.

It allows within one round trip send and receive more data that it was allowed in older protocols.

For example, we can define large number in p_sqlst_buffer_length and get a definition of few thousand columns at one call to server.

It is assumed that server will use this number to limit a size of result but not allocate all the memory for result buffer immediately. Unfortunately it does.

When max buffer size equals to USHRT_MAX (65535 bytes), server allocates for about 0.5GB. When the maximum size of result buffers is set (0xFFFF0000-1 bytes), server allocates more than 32GB memory.

Verified by multi-threaded test (8 threads) using FB4.

Server should use a more smart code for allocating memory for results

Installation of Firebird (3, 4) with install_service.bat and blanks in servicename on Windows

GitHub Issue: 7911

Installing Firebird with install_service.bat and blanks in service name doesn't work:

works: install_service.bat "123"
does not work: install_service.bat "1 2 3"