Commented by: Arioch (arioch)

P.S. it is sad that there is no dro-in debug versio nof fbclient.dll that would merely useWIndows OutputDebugString to log all the API calls.....
When Trace API just are way too limited to peep inside...

Commented by: @hvlad

The metadata is

CREATE TABLE T_BT (id integer primary key, payload BLOB SUB_TYPE TEXT);
CREATE TABLE T_VC (id integer primary key, payload VarChar(200));

The query is

select case when v.payload <> '' then v.payload else b.payload end as data
from T_VC v, T_BT b
where http://b.id = http://v.id

Note, the query returns blob, either permanent one (b.payload) or temporary one (v.payload casted to blob).

The application bug is that it fetches result set in one transaction, then commits, and fetched blobs contents in the new transaction.
It could work for permament blobs but not for temporary blobs

Commented by: @hvlad

Trace API perfectly show what happens:

2019-08-12T10:39:45.1900 (9644:0000000001F9BEF8) START_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_100, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

2019-08-12T10:39:45.2000 (9644:0000000001F9BEF8) EXECUTE_STATEMENT_FINISH
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_100, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

Statement 184:
-------------------------------------------------------------------------------
Select /* http://b.id as i1, CURRENT_TIMESTAMP as t, http://v.id as i2, */ case when v.payload <> '' then v.payload else b.payload end as data from T_VC v, T_BT b where http://b.id = http://v.id

5 records fetched
0 ms, 33 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T_VC 5
T_BT 5

2019-08-12T10:39:45.2000 (9644:0000000001F9BEF8) START_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_101, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

2019-08-12T10:39:45.2000 (9644:0000000001F9BEF8) COMMIT_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_101, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
0 ms, 1 read(s), 1 write(s), 1 fetch(es), 1 mark(s)

2019-08-12T10:39:45.2000 (9644:0000000001F9BEF8) START_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_102, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

2019-08-12T10:39:45.2100 (9644:0000000001F9BEF8) COMMIT_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_102, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
0 ms, 1 read(s), 1 write(s), 1 fetch(es), 1 mark(s)

2019-08-12T10:39:45.2100 (9644:0000000001F9BEF8) START_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_103, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

2019-08-12T10:39:53.5730 (9644:0000000001F9BEF8) ERROR AT jrd8_open_blob2
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
335544329 : invalid BLOB ID

2019-08-12T10:39:59.2830 (9644:0000000001F9BEF8) COMMIT_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_103, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
1 ms, 1 read(s), 1 write(s), 1 fetch(es), 1 mark(s)

2019-08-12T10:39:59.2830 (9644:0000000001F9BEF8) COMMIT_TRANSACTION
DB.FDB (ATT_10, SYSDBA:NONE, WIN1251, XNET:WIN7X64)
Project10.exe:8816
(TRA_100, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)
0 ms, 1 read(s), 1 write(s), 1 fetch(es), 1 mark(s)

SELECT statement run in Tx100.
Then every blob is fetched in own transaction !
First two blobs is permanent and fetched OK (tx 101 and 102).
Third blob is temporary and not exists in tx103

Same select running in isql:

SQL> Select case when v.payload <> '' then v.payload else b.payload end as data
CON> from T_VC v, T_BT b
CON> where http://b.id = http://v.id ;

         DATA

=================
81:0

DATA:
1 1 1 1

         81:2

==============================================================================
DATA:
2 2 2 2

          0:b

==============================================================================
DATA:
AAAAAAAAAAAAAAAAAA

         81:6

==============================================================================
DATA:
4 4 4 4

         81:8

==============================================================================
DATA:
5 5 5 5

Commented by: Arioch (arioch)

> The application bug is that it fetches result set in one transaction, then commits, and fetched blobs contents in the new transaction.
> It could work for permanent blobs but not for temporary blobs

no, it does not

it seems to really spawn separate nested transactions for fetching BLOBs, which is indeed a bug in the closed source library.

however it was doing it WHILE fetching rows form original query, and that means that the first TX, which created those blobs, were not committed yet !

IOW it was not the case "temporary BLOB does no more exist" but a case "temporary BLOB does exist, but we would prohibit access to it from the nested transaction, until parent TRANSACTION COMMITS (killing the BLOB along the path)"

Notice: "nested transaction" is probably not Firebird concept, but it is one in DBX v4 library, as compared with "parallel transactions" concept of DBX v3 library of 2005-2007

Can we perhaps have DIFFERENT error messages from two distinct cases "BLOB does not exist" vs "BLOB does exist, but access was denied" ?

Commented by: @hvlad

Again:

- there is no such thing as "nested transaction" in Firebird
- lifetime and scope of temporary blob is bounded to transaction where it was created
- temporary blob with blob_id 1 at txA is not the same as temporary blob with blob_id 1 at txB
- this case have nothing common with access checks\restrictions

Commented by: Arioch (arioch)

Reliable reproduction of bugs is not always possible, and when it is, it is not always possible to dispatch a developer with full sources and full development environment to the customer site.

So it would be nice if Firebird reported three different typical cases w.r.t. temporary blobs:

- this blob is real but is not accessible form this transaction, isolation violation
- this blob likely did existed but does no more
- this blob id is total lunacy, makes no any sense

what prevents it is overlapping of blob ids - "blob_id 1 at txA is not the same as temporary blob with blob_id 1 at txB"

making them non-overlapping would provide for better diagnostics and for moving this class of errors from "happens with Firebird" into "clear cause is known and what code to fix in application can be easily found" domain

for two easy examples, temporary blob_id can be taken form in-memory GENERATOR, or can be constructed as transaction id hash in high digits with linear per-transaction counter in lower digits, counter oveflows would be quite OK in these cases, so no limits upon continuous uptime of database connections would be imposed. Extra burden on the server, accounting for all other works for BLOB transfers, should not be significant either.