unexpected message 'unable to allocate memory from OS' when trying to get left( <blob>, 10) if it's length between 8126433 and is 8126464 [CORE4065]

[firebird-automations]

Submitted by: @pavel-zotov

create database 'tb.fdb'; commit; create sequence g; commit;

create table c(id int, f char(31)); commit;

-- fill table `c` with IDs and character strings: --================================= insert into c select gen_id(g,1), f.rdb$field_name from rdb$fields f,rdb$fields,rdb$relations; commit;

set blob all; set stat on; select left(x, 10) from (select list(f) x from (select f from c where id<262144)) where x similar to '%$%';

         LEFT

================= 0:5d

LEFT: RDB$VIEW_C

Current memory = 348919064 Delta memory = 343949652 Max memory = 563184888 Elapsed time= 3.84 sec Buffers = 1024 Reads = 13291 Writes 2332 Fetches = 1363855

SQL> select left(x, 10) from (select list(f) x from (select f from c where id<262145)) where x similar to '%$%';

         LEFT

================= Statement failed, SQLSTATE = HY001 unable to allocate memory from operating system

-- NOTE: this message looks very strange because I have about 2 Gb RAM on machine and Firebird consumes only \~384 Mb at the peak of this operation.

SQL> select sum(char_length(f)) from c where id<262144;

              SUM

===================== 8126433 -- this is the value where we yet *CAN* get result of list()

SQL> select sum(char_length(f)) from c where id<262145;

              SUM

===================== 8126464 -- this is the value where we can *NOT* get result of list()

1) What is the `magic` in the length of the blob that is to be processed via SIMILAR TO ? 2) Why the trivial search of FIRST occurence of single '$' character (moreover, it is just at the beginning of blob) is extremely slow (3.84 sec) ?

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

the length values are identical (8126433), is that intentional?

[firebird-automations]

Commented by: @pavel-zotov

PS ISQL Version: WI-V2.5.1.26351 Firebird 2.5 Server version: Firebird/x86/Windows NT (access method), version "WI-V2.5.1.26351 Firebird 2.5" .......... ISQL Version: WI-V2.5.3.26566 Firebird 2.5 Server version: Firebird/x86/Windows NT (access method), version "WI-V2.5.3.26566 Firebird 2.5" .......... ISQL Version: WI-T2.5.2.26538 Firebird 2.5 Experimental Server version: Firebird/x86/Windows NT (access method), version "WI-T2.5.2.26538 Firebird 2.5 Experimental"

[firebird-automations]

Commented by: @pavel-zotov

sorry, the erratum is in the subject: larger value is 8126464

[firebird-automations]

Modified by: @dyemanov

summary: unexpected message 'unable to allocate memory from OS' when trying to get left\( , 10\) if it's length between 8126433 and 8126433 =\> unexpected message 'unable to allocate memory from OS' when trying to get left\( , 10\) if it's length between 8126433 and is 8126464

[firebird-automations]

Commented by: @dyemanov

I cannot reproduce the out-of-memory error, but I see the huge memory consumption. AFAIU, the problem has nothing to do with LEFT, it's related to the SIMILAR TO predicate only. It seems to dynamically allocate more than 32 millions of its control structures (> 1GB in total), supposedly one structure per byte of the input string (represented in the canonical form). So the current algorithm seems being hardly usable for long blobs.