"Too many temporary blobs" with blob_append when selecting a stored procedure with rows-clause

[realcolaflasche]

Hi,

This is my select that works in Firebird 4.0.4 and Firebird 5:
select * from SP_COUNT(1, 18000) join SP_TEST on 1 = 1

If I use the ROWS-Clause rows 17500 to 18000 I got this error:

Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
Too many temporary blobs.
At procedure SP_TEST line: 6, col: 3.

Full Statement:
select * from SP_COUNT(1, 18000) join SP_TEST on 1 = 1 rows 17500 to 18000

This is the Source of the used stored procedures:
first, you need a possibility to generate records, i use this method:

create or alter procedure SP_COUNT (
ASTART bigint,
AEND bigint)
returns (
OVALUE bigint)
as
begin
:OValue = :AStart;

while (:OValue <= :AEND) do
begin
suspend;
:OValue = :OValue + 1;
end
end

than you need a stored procedure using Blob_append like this:

create or alter procedure SP_TEST
returns (
Memo BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8 COLLATE UTF8)
as
begin
:Memo = blob_append(null, '');
:Memo = blob_append(:Memo, 'Hello');
:Memo = blob_append(:Memo, ' World');

suspend;
end

Regards, Jan

[asfernandes]

This changed created a bug in v5 (and temporarily in master - fixed by another commit).

Here is the test:

execute block returns (vb varchar(20))
as
    declare b blob;
    declare bhandle integer;
    declare read_data varbinary(20);
begin
    -- Create a BLOB handle in the temporary space.
    b = rdb$blob_util.new_blob(true, true);

    -- Add chunks of data.
    b = blob_append(b, '1');
    b = blob_append(b, '2345');
    b = blob_append(b, '67');
    b = blob_append(b, '8');

    if (rdb$blob_util.is_writable(b)) then
    begin
        vb = '';
	    bhandle = rdb$blob_util.open_blob(b);

        while (true)
        do
        begin
            read_data = rdb$blob_util.read_data(bhandle, null);
            if (read_data is null) then
                break;

            vb = vb || read_data || '-';
        end

        execute procedure rdb$blob_util.close_handle(bhandle);

        suspend;
    end
end!

Correct result:

1-2345-67-8-

With this change, it became:

12345678-

Then in master, #8046 changed the path and make the bug not appear.

There is also fact that BLOB_APPEND does not copy segmented blobs correctly, but anyway, rdb$blob_util.new_blob is another way to create blob as BLOB_APPEND, so it makes no sense to have it closed automatically.

[hvlad]

BLOB_APPEND creates stream blobs and didn't care about segments by design.
How it should handle the case with both stream and segmented input blobs ?
BTW, I don't remember - why RDB$BLOB_UTIL have no way to write to the blob ? It would handle the need in segments when really required.

About fix for subject issue (#8056). I can't say I like it much.
Do you have better idea of how to close never used blobs produced as in this case (skipped records), or in case of OUTER JOIN, etc ?