Bugs
Hang in case of error when sweep thread is attaching to database - classic server case
GitHub Issue: 7985 Affected versions: 3.0.11, 4.0.4, 5.0.0 Fixed for: 3.0.12, 4.0.5, 5.0.1
The only purpose of having this issue separate from 7917 is release of FB5.0.0 without fixed classic server case, for master branch that's postfix for initial case.
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).
Firebird 3.0~5.0 Windows installers do not set the SYSDBA password if it was previously installed
GitHub Issue: 7826 Affected versions: 3.0, 4.0, 5.0 Fixed for: N/A
The Firebird 3.0 - 5.0 installers do not configure the SYSDBA password if a Firebird install has been run previously. Attempting to authenticate after installation results in error "Install incomplete. To complete security database initialization please CREATE USER. For details read doc/README.security_database.txt.", which indicates that the installer never attempted to create a SYSDBA user, or it failed without the installer failing.
I have tried it with:
Firebird-3.0.9.33560_0_x64.exe Firebird-3.0.10.33601_0_x64.exe Firebird-3.0.11.33703_0_x64.exe Firebird-4.0.2.2816-0-x64.exe Firebird-4.0.3.2975-0-Win32.exe Firebird-4.0.3.2975-0-x64.exe Firebird-5.0.0.1227-ReleaseCandidate1-windows-x64.exe (the official RC1 installer) Firebird-5.0.0.1261-0-RC2-windows-x64.exe (latest snapshot for RC2)
To be clear, I'm performing a fresh install, and the installation directory itself did not exist prior to running the installer. I'm using the default installer options, except I'm entering a custom SYSDBA password. With the 5.0.0.1261 installer, I also tried installing in a folder not under UAC control to see if it would work there (it didn't).
It seems that if any Firebird installer (or maybe an installer of the same major version) has been run, it will no longer set the SYSDBA password, even if that previous installation has been uninstalled, or was installed in a different directory.
The desired behaviour would be that if the security database did not exist yet in the target installation directory, that the SYSDBA password is set.
Note
From mrotteveel that reported the issue:
It is indeed not consistently reproducible: I just retried with the 3.0.11 installer twice, and both times it did initialize the security database.
Equivalence of boolean condition in partial index
GitHub Issue: 7826 Affected versions: 5.0.0 Fixed for: 6.0 Aplha 1, should be backported to v5 later
A simple partial index example:
create table OFFER ( OFFER_ID bigint generated always as identity primary key, PRODUCT_ID bigint not null, ARCHIVED boolean default false not null, PRICE decimal(9,2) not null ); create unique index IDX_OFFER_UNIQUE_PRODUCT on OFFER (PRODUCT_ID) where not ARCHIVED; insert into OFFER(PRODUCT_ID, ARCHIVED, PRICE) values (1, false, 18.95); insert into OFFER(PRODUCT_ID, ARCHIVED, PRICE) values (1, true, 17.95); insert into OFFER(PRODUCT_ID, ARCHIVED, PRICE) values (1, true, 16.95); -- following fails: insert into OFFER(PRODUCT_ID, ARCHIVED, PRICE) values (1, false, 19.95);
While playing around with this, I noticed the following:
set plan on;
select * from offer where not archived;
PLAN (OFFER INDEX (IDX_OFFER_UNIQUE_PRODUCT))
OFFER_ID PRODUCT_ID ARCHIVED PRICE
===================== ===================== ======== ============
1 1 <false> 18.95
select * from offer where archived = false;
PLAN (OFFER NATURAL)
OFFER_ID PRODUCT_ID ARCHIVED PRICE
===================== ===================== ======== ============
1 1 <false> 18.95
In other words, the equivalent expression archived = false does not use the index.
Then I made another index:
create index IDX_OFFER_ARCHIVED_PRODUCT
on OFFER (PRODUCT_ID)
where ARCHIVED;
select * from offer where archived;
PLAN (OFFER INDEX (IDX_OFFER_ARCHIVED_PRODUCT))
OFFER_ID PRODUCT_ID ARCHIVED PRICE
===================== ===================== ======== ============
2 1 <true> 17.95
4 1 <true> 16.95
SQL> select * from offer where archived = true;
PLAN (OFFER INDEX (IDX_OFFER_ARCHIVED_PRODUCT))
OFFER_ID PRODUCT_ID ARCHIVED PRICE
===================== ===================== ======== ============
2 1 <true> 17.95
4 1 <true> 16.95
Here the index is used in both cases. It seems the same optimization should be extended to not archived vs archived = false, as I think this might be a common use case, and using NOT vs = false is a stylistic choice which I think should not influence optimizer behaviour.
Assertion (space > 0) failure during restore
GitHub Issue: 7992 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Aplha 1
Unexpected results when using CASE WHEN with RIGHT JOIN
GitHub Issue: 7993 Affected versions: 3.0.11, 4.0.4, 5.0.0 Fixed for: 3.0.12, 4.0.5, 5.0.1, 6.0 Aplha 1
Considering the test case below:
CREATE TABLE t0(c0 BOOLEAN); CREATE TABLE t1(c1 BOOLEAN); INSERT INTO t0 (c0) VALUES (true); INSERT INTO t1 (c1) VALUES (false); SELECT * FROM t1 RIGHT JOIN t0 ON t0.c0; -- false true SELECT * FROM t1 RIGHT JOIN t0 ON t0.c0 WHERE (CASE t1.c1 WHEN t1.c1 THEN NULL ELSE true END ); -- null true (Unexpected) SELECT (CASE t1.c1 WHEN t1.c1 THEN NULL ELSE true END ) FROM t1 RIGHT JOIN t0 ON t0.c0; -- null
The second SELECT returns an unexpected result: if the result of the CASE expression is NULL, the value of the WHERE clause in the second should be NULL, and thus the second query should return empty result. Additionally, the output of the second query, containing a WHERE condition, must not include rows that differ from those returned by the first query.
I found this in version LI-T6.0.0.247 where I built from source code da67351
Besides, I have one question considering an error: Too many concurrent executions of the same request [SQLState:54001, ISC error code:335544663]. Does this mean that we couldn't send many same queries (e.g. SELECT * FROM t0) to the server at the same time?
gbak terminates/crashes when a read error occurs during restore
GitHub Issue: 7996 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Aplha 1
FB5 on Linux. When os_read function fails to read a backup file, gbak (or the server process in a case of restore via services) terminates/crashes.
Unexpected results after creating partial index
GitHub Issue: 7995 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Aplha 1
Considering the test case below:
CREATE TABLE t0(c0 BOOLEAN); CREATE TABLE t1(c0 INT, c1 INTEGER); CREATE UNIQUE INDEX t1i0 ON t1(c0 ) WHERE ((t1.c0) IS NOT NULL); INSERT INTO t0 (c0) VALUES (true); INSERT INTO t0 (c0) VALUES (false); INSERT INTO t1 (c0, c1) VALUES (0, 1); INSERT INTO t1 (c0) VALUES (1); INSERT INTO t1 (c0) VALUES (2); INSERT INTO t1 (c0) VALUES (3); INSERT INTO t1 (c0) VALUES (4); INSERT INTO t1 (c0) VALUES (5); INSERT INTO t1 (c0) VALUES (6); INSERT INTO t1 (c0) VALUES (7); INSERT INTO t1 (c0) VALUES (8); INSERT INTO t1 (c0) VALUES (9); INSERT INTO t1 (c0) VALUES (10); -- at least 11 rows data SELECT ((true OR t1.c1 > 0)AND(t0.c0)) FROM t1, t0; -- 11 rows of true SELECT * FROM t1, t0 WHERE ((true OR t1.c1 > 0)AND(t0.c0)); -- Expected: 11 rows -- Actual: empty result
The second SELECT returns an empty result, which is surprising: the WHERE condition is the same as the SELECT condition, and thus the second query should return the same rows as the true in the first query.
Сrash during partial index checking if the condition raises a conversion error
GitHub Issue: 7998 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Aplha 1
Consider the below test case:
CREATE TABLE t0(c0 VARCHAR(500), c1 INT); CREATE UNIQUE INDEX t0i0 ON t0(c0 , c1 ) WHERE (t0.c1 BETWEEN false AND true); INSERT INTO t0(c0, c1) VALUES (1, 2);
When executing the above statements by using isql, the following error is returned, which I suppose is unexpected. I could not simply exit the isql session by Ctrl+C, and had to kill the process.
Statement failed, SQLSTATE = 22018 Error during savepoint backout - transaction invalidated -conversion error from string "2"
Besides, if executes the above statements by using JDBC, the firebird server would crash.
Connection conn = DriverManager.getConnection("jdbc:firebirdsql://localhost:10009//app/default?user=SYSDBA&password=masterkey");
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE t0(c0 VARCHAR(500), c1 INT)");
stmt.execute("CREATE UNIQUE INDEX t0i0 ON t0(c0 , c1 ) WHERE (t0.c1 BETWEEN false AND true)");
stmt.execute("INSERT INTO t0(c0, c1) VALUES (1, 2)");
Characters are garbled when replicating BLOB SUB_TYPE TEXT fields
GitHub Issue: 7998 Affected versions: 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Aplha 1
Characters are garbled when replicating fields with type BLOB SUB_TYPE TEXT if the character set of the connection and the field are different.
gbak v4 can't backup database in ODS <13
GitHub Issue: 8003 Affected versions: 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Aplha 1
Unexpected results when comparing integer with string containing value out of range
GitHub Issue: 7997 Affected versions: 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Aplha 1
Consider the test case below. It is unexpected that, if the second query returns true, the third query returns an empty result, because the value of the WHERE predicate should be true as well. I'm not sure if the result of the second query is expected, but if it is, the third query should return -766027665. If removing the PK constraint, the third query returns -766027665, which is expected.
CREATE TABLE t0(c0 INTEGER, PRIMARY KEY(c0)); -- PK is needed INSERT INTO t0 (c0) VALUES (-766027665); SELECT t0.c0 FROM t0; -- -766027665 SELECT t0.c0 <= ((-9223372036854775808)||1) FROM t0; -- true SELECT t0.c0 FROM t0 WHERE t0.c0 <= ((-9223372036854775808)||1); -- Expected (if true for second query is expected): -766027665 -- Actual: Empty Table
Hang when database with disconnect trigger using MON$ tables is shutting down
GitHub Issue: 7997 Affected versions: 3.0.11, 4.0.4, 5.0.0 Fixed for: 3.0.12, 4.0.5, 5.0.1, 6.0 Aplha 1
When the following database:
SET SQL DIALECT 3; CREATE TABLE LOGGER (DET_TIME TIMESTAMP, AC INTEGER); SET TERM ^ ; CREATE TRIGGER LOGGER ACTIVE ON DISCONNECT POSITION 0 as declare variable c int; begin select count(*) from mon$attachments into :c; insert into logger values(current_timestamp, :c); end ^ SET TERM ; ^ COMMIT WORK;
having at least one additional attachment while shutting down, SS hangs.
Int128 datatype not supported in UDR
GitHub Issue: 8006 Affected versions: 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Aplha 1
An attempt to use int128 field in UDR fails when trying to describe it in the message.
Changes caused by ALTER DATABASE command are not replicated
GitHub Issue: 8008 Affected versions: N/A Fixed for: N/A
Most of the ALTER DATABASE sub-commands are intentionally not replicated, but some others should be but currently not.
Decfloat error working with int128 in UDR
GitHub Issue: 8006 Affected versions: 4.0.4, 5.0.0 Fixed for: 4.0.5, 5.0.1, 6.0 Aplha 1
On attempt to return really big int128 (42540766452641154071740215577757643572) from UDR function an error is raised.
[isql] Wrong message "Rolling back work" when have "set transaction" inside script
GitHub Issue: 8013 Affected versions: N/A Fixed for: N/A
Wrong query crashes the server
GitHub Issue: 8012 Affected versions: 3.0.11 Fixed for: N/A
Wrong error message when server timed out while waiting for auxiliary connection
GitHub Issue: 8012 Affected versions: 3.0.10 Fixed for: N/A
When dropping a function and a dependent table in the same transaction, error may or may not occurs
GitHub Issue: 8021 Affected versions: 4.0.4, 5.0.0, 6.0 Initial Fixed for: N/A
The behaviour should be consistent and not dependent on previous state of metadata cache.
Server crash
GitHub Issue: 8026 Affected versions: 5.0.0, 6.0 Initial Fixed for: 5.0.1, 6.0 Alpha 1
A reason of this bug was tdbb saved in a class stored in impure area. On the next call dead thread_db was used with obvious AV.
Broken gbak statistics
GitHub Issue: 8027 Affected versions: 3.0.11, 4.0.4, 5.0.0, 6.0 Initial Fixed for: 3.0.12, 4.0.5, 5.0.1, 6.0 Alpha 1
Sometimes one can see something like this in gbak output:
gbak: 0.976 0.000 0 0 writing domain RDB$235968 gbak: 0.976 0.000 18446744073709550696 18446744073709551615 writing domain RDB$235969 gbak: 0.976 0.000 920 1 writing domain RDB$235970
i.e. statistics contains definitely wrong data.
Changes
tzdata updated to version 2024a
Apply to: 4.0.5, 5.0.1, 6.0 Alpha 1
New features/improvements
Query issue conversion error from string
GitHub Issue: 7916 Apply to: 6.0 Alpha 1
New feature/improvement requests
Implement new "datatype" for blob handling
GitHub Issue: 7739
Currently we have BLOB field which require additional call to retrive it. It is so so slow. Consider implementing different datatype to handle "blob" data. It should be part of record transmission as normal field. Especially when this data column is readonly, like in belolw query result of LIST function.
Remove gfix -cache option
GitHub Issue: 8010
Gfix currently lists a -cache option which is documented as "shutdown cache manager"), and this option - looking at the alice/exe.cpp code - can be used in combination with -shutdown, but it doesn't seem to be actually doing anything (the associated isc_dpb_shutdown bitmap value of isc_dpb_shut_cache is never used in Firebird).
This seems to be some remnant from the InterBase days, and as such, it should be removed.
Gstat unnecessarily claims that switches are incompatible with -header
GitHub Issue: 8018
In general, gstat will always output the header information, yet when -header is explicitly combined with one of the other options, gstat will output either error:
option -h is incompatible with options -a, -d, -i, -r, -s and -t
or - when combined with -encryption:
option -e is incompatible with options -a, -d, -h, -i, -r, -s and -t
This seems unnecessarily restrictive. When -header is specified combined with any of the other primary switches, -all, -data, -index or -encryption, it should just be silently ignored as gstat will display the header anyway. When combined with only any of the secondary switches, -record, -system or -table, should it output an error (because then the default of -all is switched off, and a user should explicitly request -all, -data or -index).
Add alias -verbose for gbak switch -verify
GitHub Issue: 8025
Currently, gbak has a switch -verify (abbreviated to -v), which actually provides verbose output, and, more importantly, does not verify anything, and thus would be better represented as switch -verbose. I think it would make to make -verbose the new "normal" (and document it in the usage instruction) and make -verify its alias, not mentioned in the usage instruction.
Other open issues
Unnecessary sorting of an unused field
GitHub Issue: 8019
FB 5.0:
select distinct '' as small_field
from
(
select cast('' as varchar(8000)) as big_field from rdb$database
) bf
/*
Select Expression
-> Unique Sort (record length: 28, key length: 4)
-> Table "RDB$DATABASE" as "BF RDB$DATABASE" Full Scan
*/
select distinct '' as small_field
from
(
select cast('' as varchar(8000)) as big_field from rdb$database
union all
select cast('' as varchar(8000)) as big_field from rdb$database
) bf
/*
Select Expression
-> Unique Sort (record length: 8044, key length: 4)
-> Union
-> Table "RDB$DATABASE" as "BF RDB$DATABASE" Full Scan
-> Table "RDB$DATABASE" as "BF RDB$DATABASE" Full Scan
*/
Unique Sort (record length: 28, key length: 4) vs Unique Sort (record length: 8044, key length: 4)
When using large datasets, the query execution time differs many times over.