Development News - August 2024

Inconsitent automatic type casting for built-in scalar functions

GitHub Issue: 8210 Affected versions: 3.0.12, 4.0.5, 5.0.1, 6.0 Initial

Inconsistent behavior of automatic type casting with built-in scalar functions with numeric arguments. Almost all functions do automatic string-to-numeric type cast for arguments. Example: abs('-11') returns -11.0, sin('3.14') returns 0.002, etc...

But where are some exceptions, which are (probably) bugs. I've done some some tests with FB-3.0.11 and got this (I hope full) list:

• round(x, y) - x (expression evaluation not supported)
• substring(x from y for z) - y (expression evaluation not supported)
• dateadd(x unit to y) - y (expression evaluation not supported)
• datediff(unit from x to y) - x, y (expression evaluation not supported)
• bin_and, bin_not, bin_or, bin_shl, bin_shr, bin_xor - all arguments (expression evaluation not supported)
• extract(day from x) - x (Specified EXTRACT part does not exist in input datatype.)

Suggested fix: add automatic string-to-number (string-to-timestamp) type cast.

Column update permissions works wrong in regard to object names case

GitHub Issue: 8212 Affected versions: 5.0.1, 6.0 Initial

isql -term !

create database 't.fdb'!

create table t1 (n1 integer, n2 integer)!

create procedure p1
as
begin
    update t1 set n1 = 0;
end!

create procedure "p1"
as
begin
    update t1 set n2 = 0;
end!

grant update (n1) on t1 to procedure p1!
grant update (n2) on t1 to procedure "p1"!

grant execute on procedure p1 to public!
grant execute on procedure "p1" to public!

commit!

set blob all!

-- Only procedure "p1" is listed
select s.rdb$acl
    from rdb$relations r
    join rdb$security_classes s on s.rdb$security_class = r.rdb$security_class
    where r.rdb$relation_name = 'T1'!

isql t.fdb -user user

-- Must work, but don't
execute procedure p1;
/*
Statement failed, SQLSTATE = 28000
no permission for UPDATE access to TABLE T1
-Effective user is USER
*/

execute procedure "p1";

Incorrect result of index list scan for a composite index, the second segment of which is a text field with COLLATE UNICODE_CI

GitHub Issue: 8214 Affected versions: 5.0.1, 6.0 Initial

Rare sporadic segfaults in test for core-6142 on windows

GitHub Issue: 8215 Affected versions: 5.0.1, 6.0 Initial

Cache writer thread sometimes segfaults on exit due to attempt to use already deleted object, passed to it as thread argument.

Problem with encryption of self-security database

GitHub Issue: 8227

Function MAKE_DBKEY may produce random errors if used with relation name

GitHub Issue: 8203 Affected versions: 5.0.0, 6.0 Initial Fixed for: 5.0.2, 6.0 Alpha 1

It happens in DEBUG build with table names of any length and in RELEASE build with table names greater than 15 bytes. As QA test gh_8168_test uses short table names, it could fail with DEBUG build only. gbak is not affected as it uses table ID's, not names.

DATEADD truncates milliseconds for month and year deltas

GitHub Issue: 8211 Affected versions: 4.0.4, 5.0.1, 6.0 Initial Fixed for: 4.0.5, 5.0.2, 6.0 Alpha 1

dataadd truncates milliseconds for month and year units, for other units (millisecond, second, hour, day and week) milliseconds preserved.

WHEN NOT MATCHED BY SOURCE - does not work with a direct table as source

GitHub Issue: 8213 Affected versions: 5.0.1, 6.0 Initial Fixed for: 5.0.2, 6.0 Alpha 1

“WHEN NOT MATCHED BY SOURCE” was introduced with ticket #6681. This has worked quite well so far. However, I have now noticed that “WHEN NOT MATCHED BY SOURCE” behaves differently if the source is a stored procedure than if you specify derived-table as the source.

For “WHEN NOT MATCHED BY TARGET”, stored procedure and derived-table behave in the same way.

Here's an example, it may not make much sense, but you can see the difference in a nutshell.

Moe details in ticket.

Crash when MAKE_DBKEY() is called with 0 or 1 arguments

GitHub Issue: 8221 Affected versions: 4.0.5, 5.0.1, 6.0 Initial Fixed for: 4.0.6, 5.0.2, 6.0 Alpha 1

MAKE_DBKEY() accepts 2 to 4 arguments. Engine should report error "function MAKE_DBKEY could not be matched" when number of arguments passed is not correct.

Database creation in 3.0.12, 4.0.5 and 5.0.1 slower than in previous releases

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

While running the Jaybird tests against Firebird 5.0.1, I noticed a slow down in test execution compared to Firebird 5.0.0.

The problem seems to be that database creation in Firebird 5.0.1 is roughly 30% slower compared to Firebird 5.0.0. For example, if I create 500 database against 5.0.0 (on Windows) it takes ~41.7 seconds, and on 5.0.1 it takes ~55.2 seconds.

Both 5.0.0 and 5.0.1 use the same configuration. The databases are created with isc_dpb_force_write set to 1, but not setting it makes no significant difference).

Firebird 4.0.5 and 3.0.12 have a similar performance decrease creating database compared to 4.0.4/3.0.11:

• 4.0.4: ~40.5 seconds, 4.0.5: ~56.8 seconds
• 3.0.11: ~41.8 seconds, 3.0.12: ~60.2 seconds

SubQueryConversion = true -- wrong resultset with FIRST/SKIP clauses inside the outer query

GitHub Issue: 8224 Affected versions: 5.0.1 Fixed for: 5.0.2

The FIRST limiter is applied earlier than necessary.

SubQueryConversion = true -- error "no current record for fetch operation" with complex joins

GitHub Issue: 8223 Affected versions: 5.0.1 Fixed for: 5.0.2

SubQueryConversion = true --multiple rows in singleton select

GitHub Issue: 8233 Affected versions: 5.0.1 Fixed for: 5.0.2

Ability to obtain PID of server process for current connection without querying mon$ tables

GitHub Issue: 8230

Please add ability to obtain PID of server process for current connection without querying mon$ tables ( e.g. via rdb$get_context('SYSTEM', 'SERVER_PID') ).

Provide ability to declare variables directly in .sql scripts

GitHub Issue: 8232