Is blob ID 0 a valid or an invalid blob id, or some special case?

[Mark Rotteveel]

Yesterday, a case was brought to my attention that seems to occur since
Firebird 4.0.3 (see https://github.com/FirebirdSQL/jaybird/issues/763).
It looks like something (either Jaybird, or another application, or
maybe Firebird itself), is setting a blob to blob id 0. It is currently
unclear if that blob should have been set to NULL, or to an empty blob.

When that value is then retrieved through Jaybird, this results in an
"Invalid BLOB ID" error, because Jaybird considers blob id 0 not to be
valid (that is, it does not try to ask the server for it, but
immediately throws an exception).

This raises the following questions for me:

1) Is blob id 0 actually a valid blob id and should I just send it to
the server as is?

2) Is there a change (optimization?) in Firebird 4.0.3 which could
result in blob id 0 being used for an empty blob?

Mark
--
Mark Rotteveel

[Vlad Khorsun]

14.09.2023 13:59, 'Mark Rotteveel' via firebird-devel wrote:
> Yesterday, a case was brought to my attention that seems to occur since Firebird 4.0.3 (see
> https://github.com/FirebirdSQL/jaybird/issues/763). It looks like something (either Jaybird, or another application, or maybe
> Firebird itself), is setting a blob to blob id 0. It is currently unclear if that blob should have been set to NULL, or to an empty
> blob.
>
> When that value is then retrieved through Jaybird, this results in an "Invalid BLOB ID" error, because Jaybird considers blob id 0
> not to be valid (that is, it does not try to ask the server for it, but immediately throws an exception).
>
> This raises the following questions for me:
>
> 1) Is blob id 0 actually a valid blob id and should I just send it to the server as is?

This is definitely not a valid blob id.

> 2) Is there a change (optimization?) in Firebird 4.0.3 which could result in blob id 0 being used for an empty blob?

Empty blob id always initialized with zero's but it should not be stored into database
as is. I.e. there should be NULL or non-zero blob id in any field of any record. And I doubt
engine could accept zero blob id from user application.

I can guess that NULL flag was missed somewhere between client and server, but need test
case to validate, find and fix it.

Regards,
Vlad

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 14.09.2023 12:59:
>
> 1) Is blob id 0 actually a valid blob id and should I just send it to the server
> as is?

Theoretically it may be valid if an engine provider decide it to be valid.
Practically a lot of Firebird engine code explicitly check for zero value as an
invalid id.
Besides a valid blob id for persistent BLOBs contains table id which cannot
be zero.

> 2) Is there a change (optimization?) in Firebird 4.0.3 which could result in
> blob id 0 being used for an empty blob?

No. Even temporary blob ids are counted from 1 and zero is explicitly skipped.

--
WBR, SD.

[Mark Rotteveel]

On 14-09-2023 13:10, Vlad Khorsun wrote:
> 14.09.2023 13:59, 'Mark Rotteveel' via firebird-devel wrote:
>> This raises the following questions for me:
>>
>> 1) Is blob id 0 actually a valid blob id and should I just send it to
>> the server as is?
>
>   This is definitely not a valid blob id.

OK. I did see that blb:open2 accepts blob id 0, and marks it as
immediately empty (blob->blb_flags |= BLB_eof). That might explain why
ISQL doesn't raise errors when querying the example database I've received.

>> 2) Is there a change (optimization?) in Firebird 4.0.3 which could
>> result in blob id 0 being used for an empty blob?
>
>   Empty blob id always initialized with zero's but it should not be
> stored into database
> as is. I.e. there should be NULL or non-zero blob id in any field of any
> record. And I doubt
> engine could accept zero blob id from user application.
>
>   I can guess that NULL flag was missed somewhere between client and
> server, but need test
> case to validate, find and fix it.

So far, the reporter hasn't been able to identify where those values are
introduced. I tried a number of things in Jaybird with setting fields to
empty and null, but they all resulted in expected values, and not blob id 0.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 14-09-2023 13:10, Vlad Khorsun wrote:

> 14.09.2023 13:59, 'Mark Rotteveel' via firebird-devel wrote:
>> 2) Is there a change (optimization?) in Firebird 4.0.3 which could
>> result in blob id 0 being used for an empty blob?
>
>   Empty blob id always initialized with zero's but it should not be
> stored into database
> as is. I.e. there should be NULL or non-zero blob id in any field of any
> record. And I doubt
> engine could accept zero blob id from user application.

The engine accepts a blob with id = 0 without complaint from user code
in an insert statement, it does not reject it.

However, that is the case in older versions as well, and this specific
problem seems to only have surfaced after upgrades to Firebird 4.0.3. In
any case, the reporter is investigating to see if they can identify
where the value is set to blob id 0, and I'll consider if Jaybird should
simply try and get data for blob id 0 instead of rejecting it outright
to make it match what Firebird itself does.

Mark
--
Mark Rotteveel

[Vlad Khorsun]

14.09.2023 14:26, 'Mark Rotteveel' via firebird-devel wrote:
> On 14-09-2023 13:10, Vlad Khorsun wrote:
>> 14.09.2023 13:59, 'Mark Rotteveel' via firebird-devel wrote:
>>> This raises the following questions for me:
>>>
>>> 1) Is blob id 0 actually a valid blob id and should I just send it to the server as is?
>>
>>    This is definitely not a valid blob id.
>
> OK. I did see that blb:open2 accepts blob id 0, and marks it as immediately empty (blob->blb_flags |= BLB_eof). That might explain
> why ISQL doesn't raise errors when querying the example database I've received.

To have Blob ID = 0 as input for blb::open, there must be such value in record data
or such blob id should be passed from client app. In the case of stored data it is a
big question how it could happens. I can think about:
- wrong data migrated from old Firebird versions (fb 1.x, ib6)
- by-hand editing of system tables to set NOT-NULL flag (before fb3)
- bug in the engine around setting NOT NULL flag without proper check for NULL blob's.

>>> 2) Is there a change (optimization?) in Firebird 4.0.3 which could result in blob id 0 being used for an empty blob?
>>
>>    Empty blob id always initialized with zero's but it should not be stored into database
>> as is. I.e. there should be NULL or non-zero blob id in any field of any record. And I doubt
>> engine could accept zero blob id from user application.
>>
>>    I can guess that NULL flag was missed somewhere between client and server, but need test
>> case to validate, find and fix it.
>
> So far, the reporter hasn't been able to identify where those values are introduced. I tried a number of things in Jaybird with
> setting fields to empty and null, but they all resulted in expected values, and not blob id 0.

Seems Jaybird is fine, then. At least - current version :)

Regards,
Vlad

[Vlad Khorsun]

14.09.2023 14:54, 'Mark Rotteveel' via firebird-devel write:
> On 14-09-2023 13:10, Vlad Khorsun wrote:
>> 14.09.2023 13:59, 'Mark Rotteveel' via firebird-devel wrote:
>>> 2) Is there a change (optimization?) in Firebird 4.0.3 which could result in blob id 0 being used for an empty blob?
>>
>>    Empty blob id always initialized with zero's but it should not be stored into database
>> as is. I.e. there should be NULL or non-zero blob id in any field of any record. And I doubt
>> engine could accept zero blob id from user application.
>
> The engine accepts a blob with id = 0 without complaint from user code in an insert statement, it does not reject it.

Hmm... is it converted to the NULL value ? Did you try to read it back from relation ?

See in blb::move():

// If either the source value is null or the blob id itself is null
// (all zeros), then the blob is null.

if ((request->req_flags & req_null) || source->isEmpty())
{
record->setNull(fieldId);
destination->clear();
return;
}

source->isEmpty() means blob id is 0.

Regards,
Vlad

[Mark Rotteveel]

On 14-09-2023 13:55, Vlad Khorsun wrote:
> 14.09.2023 14:26, 'Mark Rotteveel' via firebird-devel wrote:
>   To have Blob ID = 0 as input for blb::open, there must be such value
> in record data
> or such blob id should be passed from client app. In the case of stored
> data it is a
> big question how it could happens. I can think about:
> - wrong data migrated from old Firebird versions (fb 1.x, ib6)
> - by-hand editing of system tables to set NOT-NULL flag (before fb3)

Both of these don't seem to apply here. The faulty data was produced
after upgrading to 4.0.3 (from 4.0.2).

> - bug in the engine around setting NOT NULL flag without proper check
> for NULL blob's.

Possibly.

>> So far, the reporter hasn't been able to identify where those values
>> are introduced. I tried a number of things in Jaybird with setting
>> fields to empty and null, but they all resulted in expected values,
>> and not blob id 0.
>
>   Seems Jaybird is fine, then. At least - current version :)

I can't discount the possibility Jaybird is doing something wrong
somewhere, but that wouldn't explain why it worked fine in 4.0.2 (with
the same version of Jaybird).

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 14-09-2023 14:10, Vlad Khorsun wrote:
> 14.09.2023 14:54, 'Mark Rotteveel' via firebird-devel write:
>> On 14-09-2023 13:10, Vlad Khorsun wrote:
>>>    Empty blob id always initialized with zero's but it should not be
>>> stored into database
>>> as is. I.e. there should be NULL or non-zero blob id in any field of
>>> any record. And I doubt
>>> engine could accept zero blob id from user application.
>>
>> The engine accepts a blob with id = 0 without complaint from user code
>> in an insert statement, it does not reject it.
>
>   Hmm... is it converted to the NULL value ? Did you try to read it
> back from relation ?

It produces a non-NULL value, and on retrieval, a blob id 0 is returned.
Setting explicitly to null instead of to blob id 0 produces a NULL value
as expected.

> See in blb::move():
>
>     // If either the source value is null or the blob id itself is null
>     // (all zeros), then the blob is null.
>
>     if ((request->req_flags & req_null) || source->isEmpty())
>     {
>         record->setNull(fieldId);
>         destination->clear();
>         return;
>     }
>
> source->isEmpty() means blob id is 0.

Does it actually hit that code? Isn't it skipped due to the earlier

if (*source == *destination)
return;

Mark
--
Mark Rotteveel

[Vlad Khorsun]

14.09.2023 15:19, 'Mark Rotteveel' via firebird-devel wrote:
> On 14-09-2023 14:10, Vlad Khorsun wrote:
>> 14.09.2023 14:54, 'Mark Rotteveel' via firebird-devel write:
>>> On 14-09-2023 13:10, Vlad Khorsun wrote:
>>>>    Empty blob id always initialized with zero's but it should not be stored into database
>>>> as is. I.e. there should be NULL or non-zero blob id in any field of any record. And I doubt
>>>> engine could accept zero blob id from user application.
>>>
>>> The engine accepts a blob with id = 0 without complaint from user code in an insert statement, it does not reject it.
>>
>>    Hmm... is it converted to the NULL value ? Did you try to read it back from relation ?
>
> It produces a non-NULL value, and on retrieval, a blob id 0 is returned. Setting explicitly to null instead of to blob id 0 produces
> a NULL value as expected.

I see, thanks

>> See in blb::move():
>>
>>      // If either the source value is null or the blob id itself is null
>>      // (all zeros), then the blob is null.
>>
>>      if ((request->req_flags & req_null) || source->isEmpty())
>>      {
>>          record->setNull(fieldId);
>>          destination->clear();
>>          return;
>>      }
>>
>> source->isEmpty() means blob id is 0.
>
> Does it actually hit that code? Isn't it skipped due to the earlier
>
>     if (*source == *destination)
>         return;

You right, it is skipped and allows to put blob id 0 into record.

I've reproduced it and found it worked this way always. While engine handle zero blob
id's, I consider it is a bug. Such blob id's should not be accepted or it should be
replaced by NULL's, imho.

Of course there was no specific changes in 4.0.3 in this regards, you may try your
test with ahy other Firebird version. I did with 2.5 with the same results.


Regards,
Vlad

[Mark Rotteveel]

On 14-09-2023 21:41, Vlad Khorsun wrote:
>   You right, it is skipped and allows to put blob id 0 into record.
>
>   I've reproduced it and found it worked this way always. While engine
> handle zero blob
> id's, I consider it is a bug. Such blob id's should not be accepted or
> it should be
> replaced by NULL's, imho.
>
>   Of course there was no specific changes in 4.0.3 in this regards, you
> may try your
> test with ahy other Firebird version. I did with 2.5 with the same results.

Yes, I could reproduce that you can store blob id 0 into a record in
earlier versions, but the reporter explicitly stated that the problem
they had started after upgrading from 4.0.2 to 4.0.3.

They have multiple deployments, and for each it started after upgrading
the Firebird instance of that deployment. Which would indicate that
there is something in Firebird 4.0.3, or between Firebird and Jaybird
(or possibly Firebird and FIB+, as that is used by another application
they use) that causes this problem specifically to occur.

But given they haven't found yet when/where/how the faulty data is
introduced in the database, that makes it hard to identify at this time.

Mark
--
Mark Rotteveel

[Vlad Khorsun]

15.09.2023 9:55, 'Mark Rotteveel' via firebird-devel wrote:
> On 14-09-2023 21:41, Vlad Khorsun wrote:
>>    You right, it is skipped and allows to put blob id 0 into record.
>>
>>    I've reproduced it and found it worked this way always. While engine handle zero blob
>> id's, I consider it is a bug. Such blob id's should not be accepted or it should be
>> replaced by NULL's, imho.
>>
>>    Of course there was no specific changes in 4.0.3 in this regards, you may try your
>> test with ahy other Firebird version. I did with 2.5 with the same results.
>
> Yes, I could reproduce that you can store blob id 0 into a record in earlier versions, but the reporter explicitly stated that the
> problem they had started after upgrading from 4.0.2 to 4.0.3.

As I understand the problem is with reading that data by application.

Are you sure error is raised on blob id = 0 ? It looks very strange that with the same
data Firebird raised error when using one tool (DBeaver) while no error with another tool
(IBE). Can it be that DBeaver (or some code below it) passed not 0 as blob id but something
else, really invalid ? Engine handles blob id 0 without error, as we found, i.e. it looks
like something else happens.

To investigate the issue, I can prepare custom build that reports wrong blob id value among
with the original error (isc_bad_segstr_id). Or user could prepare simplest .exe for me that
reads data and makes Firebird raise error.

> They have multiple deployments, and for each it started after upgrading the Firebird instance of that deployment. Which would
> indicate that there is something in Firebird 4.0.3, or between Firebird and Jaybird (or possibly Firebird and FIB+, as that is used
> by another application they use) that causes this problem specifically to occur.
>
> But given they haven't found yet when/where/how the faulty data is introduced in the database, that makes it hard to identify at
> this time.

As we have found, blob id 0 could have been stored into the database at any time in the
past, so this is not something we should be looking at, I believe.


Regards,
Vlad

[Mark Rotteveel]

On 15-09-2023 09:31, Vlad Khorsun wrote:
>   As I understand the problem is with reading that data by application.

That error is just a symptom of reading the invalid data, and how they
detected the problem. The actual problem is that the invalid data ended
up in the database.

>   Are you sure error is raised on blob id = 0 ? It looks very strange
> that with the same
> data Firebird raised error when using one tool (DBeaver) while no error
> with another tool
> (IBE). Can it be that DBeaver (or some code below it) passed not 0 as
> blob id but something
> else, really invalid ? Engine handles blob id 0 without error, as we
> found, i.e. it looks
> like something else happens.

I'm sure of it, because I have a copy of the affected table. And the
reason that DBeaver throws an error is because DBeaver also uses
Jaybird, and Jaybird considers blob id 0 invalid and throws an exception
if asked to read a blob with id 0.

To be clear, Jaybird itself is throwing the isc_bad_segstr_id; it
doesn't ask the server for the blob, and it is not the server raising
the error.

On the other hand IBExpert (and ISQL) will simply request blobs with id
0, which the server then treats as an empty blob, so that wouldn't raise
an error.

>   To investigate the issue, I can prepare custom build that reports
> wrong blob id value among
> with the original error (isc_bad_segstr_id). Or user could prepare
> simplest .exe for me that
> reads data and makes Firebird raise error.

The error is a symptom, and the invalid id is 0 (confirmed both with
ISQL, and by inspecting variables with a debugger when querying through
Jaybird).

>   As we have found, blob id 0 could have been stored into the database
> at any time in the
> past, so this is not something we should be looking at, I believe.

If blobs with id 0 had ended up in the database before, they would
already have had that problem in earlier versions. In any case, I think
we'll need to wait for the reporter to have a reproducible case.

Mark
--
Mark Rotteveel

[Vlad Khorsun]

15.09.2023 11:07, 'Mark Rotteveel' via firebird-devel wrote:
> On 15-09-2023 09:31, Vlad Khorsun wrote:
>>    As I understand the problem is with reading that data by application.
>
> That error is just a symptom of reading the invalid data, and how they detected the problem. The actual problem is that the invalid
> data ended up in the database.

Perhaps I confused you saying that blob id 0 is invalid, while engine actually handles it
as null/empty blob. My opinion not changed. But we should work with what we have now.

>>    Are you sure error is raised on blob id = 0 ? It looks very strange that with the same
>> data Firebird raised error when using one tool (DBeaver) while no error with another tool
>> (IBE). Can it be that DBeaver (or some code below it) passed not 0 as blob id but something
>> else, really invalid ? Engine handles blob id 0 without error, as we found, i.e. it looks
>> like something else happens.
>
> I'm sure of it, because I have a copy of the affected table. And the reason that DBeaver throws an error is because DBeaver also
> uses Jaybird, and Jaybird considers blob id 0 invalid and throws an exception if asked to read a blob with id 0.
>
> To be clear, Jaybird itself is throwing the isc_bad_segstr_id; it doesn't ask the server for the blob, and it is not the server
> raising the error.

This is missing piece, thank. I don't think driver should do it, raising such errors is
engine responsibility. Looking at isql and engine code, I'd suggest to treat blob id 0 as
NULL (or as a valid known empty blob) at client side.

> On the other hand IBExpert (and ISQL) will simply request blobs with id 0, which the server then treats as an empty blob, so that
> wouldn't raise an error.

Exactly.

>>    To investigate the issue, I can prepare custom build that reports wrong blob id value among
>> with the original error (isc_bad_segstr_id). Or user could prepare simplest .exe for me that
>> reads data and makes Firebird raise error.
>
> The error is a symptom, and the invalid id is 0 (confirmed both with ISQL, and by inspecting variables with a debugger when querying
> through Jaybird).

Ok.

>>    As we have found, blob id 0 could have been stored into the database at any time in the
>> past, so this is not something we should be looking at, I believe.
>
> If blobs with id 0 had ended up in the database before, they would already have had that problem in earlier versions. In any case, I
> think we'll need to wait for the reporter to have a reproducible case.

Perhaps, reporter have some backup made before switch to the 4.0.3 ?

Regards,
Vlad