Bugs Found
IN predicate incorrectly handles single parenthesized subquery as IN-list, instead of table subquery
GitHub Issue: 8182 Affected versions: 3.0.11, 4.0.4, 5.0.0
Currently IN with a parenthesized subquery is incorrect handled as an IN-list with a single value, instead of a table subquery.
The following use of IN with a subquery will work fine:
select * from RDB$CHARACTER_SETS where RDB$CHARACTER_SET_ID in ( select RDB$CHARACTER_SET_ID from RDB$COLLATIONS )
However, adding another set of parentheses in the IN, will make the parser choose the IN-list alternative instead of the IN-subquery route.
As a result, using:
select * from RDB$CHARACTER_SETS where RDB$CHARACTER_SET_ID in (( select RDB$CHARACTER_SET_ID from RDB$COLLATIONS ))
will result in error:
multiple rows in singleton select [SQLState:21000, ISC error code:335544652]
This is essentially an ambiguity in the syntax BNF of 8.4 <in predicate> of ISO 9075-2:2023, but the SQL standard has an explicit Syntax Rule to address this ambiguity.
BNF syntax (ambiguous):
<in predicate> ::=
<row value predicand> <in predicate part 2>
<in predicate part 2> ::=
[ NOT ] IN <in predicate value>
<in predicate value> ::=
<table subquery>
| <left paren> <in value list> <right paren>
<in value list> ::=
<row value expression> [ { <comma> <row value expression> }... ]
One of the <row value expression> productions is <scalar subquery>. Both <table subquery> and <scalar subquery> resolve to <subquery>, which resolves to (<query expression>), hence the error if it doesn't produce a singleton scalar value.
Syntax Rule 1 (resolving the ambiguity):
If <in value list> consists of a single <row value expression>, then that <row value expression> shall not be a <scalar subquery>.
NOTE 337 — This Syntax Rule resolves an ambiguity in which <in predicate value> might be interpreted either as a <table subquery> or as a <scalar subquery>. The ambiguity is resolved by adopting the interpretation that the <in predicate value> will be interpreted as a <table subquery>.
Bugs Fixed
The file iberror_c.h is missing in the Linux x64 tar archive
GitHub Issue: 8172 Affected versions: 5.0.0 Fixed for: 5.0.1, 6.0 Alpha 1
The file include/firebird/impl/iberror_c.h is missing in the Linux x64 tar archive. It is in the zip archive for Windows. Without it, it is difficult to build applications using the Legacy API in C. Perhaps it is not available in other architectures either.
Trace plugin unloaded if called method is not implemented
GitHub Issue: 8171 Affected versions: 4.0.2, 5.0.0, 6.0 Initial Fixed for: 4.0.5, 5.0.1, 6.0 Alpha 1
Using old trace plugin with new engines makes trace session to be stopped if any unimplemented method is called.
According to Vlad Khorsun:
It should be marked as [notImplemented(true)] in FirebirdInterface.idl.
Fix boolean conversion to string inside DataTypeUtil::makeFromList()
GitHub PR: 8178 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 a trace session is terminated spontaneously
GitHub issue: 8180 Affected versions: 4.0.4, 5.0.0, 6.0 Initial Fixed for: 4.0.5, 5.0.1, 6.0 Alpha 1
Sometimes a system session can just disappear. The easiest way to reproduce the problem is to run 2 tests via firebird-qa: python3 -m pytest --server local --disable-db-cache tests/bugs/core_2940_test.py tests/bugs/core_2981_test.py The second test will fail with the following result:
Trace session ID X not found
Fixed a few issues with IPC used by remote profiler
GitHub PR: 8186 Affected versions: 5.0.0, 6.0 Initial Fixed for: 5.0.1, 6.0 Alpha 1
Firebird 5 hang after starting remote profiling session
GitHub issue: 8176 Affected versions: 5.0.0, 6.0 Initial Fixed for: 5.0.1, 6.0 Alpha 1
Starting RDB$PROFILER.START_SESSION hangs server completely. It stops responding to requests and new connections.
- To reproduce the problem, run Flamerobin twice and connect to probably any database (but the same database for both instances).
- In the first Flamerobin instance, go to the system tables, right-click on e.g., RDB$FIELDS, choose "Browse data," and leave the window open.
- In the second Flamerobin instance, execute the following query (adjust 79 to the connection ID from the first Flamerobin instance).
SELECT RDB$PROFILER.START_SESSION('Profile without "DETAILED_REQUESTS"', NULL, 79)
FROM RDB$DATABASE;
I tried above session on current Firebird 5 snapshot on Windows (5.0.1.1432) 64bit, and the server hung completely. No new connections were allowed and current connections stopped responding.
Slow connection times with a lot of simultaneous connections and active trace session present
GitHub issue: 8189 Affected versions: 4.0.0, 5.0.0, 6.0 Initial Fixed for: 4.0.5, 5.0.1, 6.0 Alpha 1
The issue reported by customer. The stack trace shows that more than a hundred of threads waits while one thread populates cache of system privileges.
SIGSEGV in Firebird 5.0.0.1306 embedded during update on cursor
GitHub issue: 8185 Affected versions: 5.0.0, 6.0 Initial Fixed for: 5.0.1, 6.0 Alpha 1
The program was executing the following query:
update scheduler set id_sched=gen_id(sched_gen,1), wndopen=current_timestamp, wndclose=current_timestamp+(cast(apertura as numeric(9,5))/1440.0), cache_vect=(select pv1.rif_vect from arcpervect pv1 where pv1.rif_per=scheduler.rif_per and pv1.idx_vect=1), lastresult=null where current of SCHEDPEEK
New features/improvements
Added shutdown handler for Classic Server
GitHub PR: 8165 Apply to: 4.0.5, 5.0.1, 6.0 Aplha 1
Added shutdown handler for server to avoid active connections after termination. If you start the server with classic mode, then at the end of the server, there are still active connections.
Ensure the standalone CS listener on Linux uses the SO_REUSEADDR socket option
GitHub PR: 8181 Apply to: 3.0.12, 4.0.5, 5.0.1, 6.0 Aplha 1
In recent FB versions Classic on Linux uses its native listener instead of xinetd/systemd. While we usually don't expect it to crash, it's still possible. However, often it cannot continue listening the port immediately after restart because the port is still in the FIN_WAIT state and the SO_REUSEADDR socket option is not used by the standalone Classic listener, thus causing "address already in use" errors during startup. This patch ensures that any kind of listeners (SS/SC using SRVR_multi_thread and CS standalone server) uses the SO_REUSEADDR option to avoid the issue.
Maybe (after this patch) we should also cleanup the retry loop for INET_ADDR_IN_USE used later in this function, as it's likely to be pointless, but I leave it for another day.
New feature/improvement requests
Change zlib level compression from "best" to fast
GitHub Issue: 8174
Change zlib deflate compression level from Z_DEFAULT_COMPRESSION (6 for now) to Z_BEST_SPEED (1).
int ret = zlib().deflateInit(&port_send_stream, Z_DEFAULT_COMPRESSION); // remote/remote.cpp int ret = zlib().deflateInit(&strm, Z_DEFAULT_COMPRESSION); // burp/mvol.cpp
As can see on comparision table lz4 project, deflate Z_DEFAULT_COMPRESSION are "little slow" (about 36 MB/s). Deflate Z_BEST_SPEED (1) are almost three times faster (about 100 MB/s) with very small loss on compression degree and decompression speed for "typical" data and will much faster on incompressible data.
---
Compression algorithm and compression level maybe should be configurable.
ZLib for backwards support, LZ4 and Zstd might be the best choices. It all depends on everything. Data and transfer link speed (to/from hdd/ssd/super fast Ethernet/fast ethernet/slow internet etc...).
There most likely is use case when even very slow compression speed but good compression ratio of lzma would be best choice. Way back I used lzma with full compression level to make data transfer faster over very slow gprs transfer... Because transfer was so slow...
But ZLib/LZ4/Zstd for choices and compression level most likely would be more than enough.
Extend indexing feature to index multiple values, a little same as word index
GitHub Issue: 8184
Currently, when we create an expression index, it must be a singleton function.
For example:
CREATE IXAE_TEST_VALUES ON TEST COMPUTED BY(CALC_SINGLE_VALUE(MY_FIELD));
However, if you need to divide a field into components such as words, numbers, etc., this is not possible.
For instance, consider a field with the value 'My salary is 10000 and it contains 500 for x and 700 for y.' If you need to index each number, or if you have the text 'Lorem ipsum dolor sit amet, consectetur adipiscing elit' and need to index each word, you cannot do this without an external indexer like Lucene, or a helper table and procedure that divides the field into words. It complicate things which is not nescessary at all.
Please add the capability to index multiple values from the same field pointing to the same record.
For example:
CREATE IXAE_TEST_VALUES ON TEST COMPUTED BY(DIVIDE_VALUES_WITH_SUSSPED_PROC(MY_FIELD));
I believe this support is needed as you are planning to introduce JSON, which requires being indexable.
But you must think how to query by this values. Maybe some special word or syntax? Like SELECT * FROM TEST WHERE 'Lorem' IN MY_FIELD
Add generated files for OO API for C language to distribution
GitHub Issue: 8197
There are many programs written in C. At the moment they use the legacy isc API. But at the moment the isc API is not being developed and it is impossible to add new functionality to such programs. I know that with the help of CLOOP we can generate an OO API for the C language. But most developers are not aware of this. Moreover, they are afraid if some generated C interfaces get into their program, which over time can change in FB and they will become broken.
There are real examples of this need. Recently I made a patch for Firebird 4.0 data type support for the pdo_firebird driver for PHP. I simply bound all the new data types to strings, since PHP has no other types in which the values of the new FB types could be placed. And it works. With one exception, for TIME[STAMP] TZ data types, you cannot set the display format. This is easily solved in the OO API, but PHP is written in C, and it uses the isc API. In my opinion, it is quite strange to include two generated files fb_api.h and fb_impl_api.c in the pdo_firebird source code to solve this problem. I think maintainers will simply not accept such a patch, because the files are generated and may violate their coding standards. It would be a different matter if they were included in the Firebird installation and were located in ${fb_root}/include like other API files.