DECLARED TEMP TABLE. Internal consistency check raises on attempt to insert literal values to BLOB column

[Pavel Zotov]

Consider script:

===========

set bail on;
set list on;
set autoterm on;
set names utf8;
shell del r:\temp\tmp4test.fdb 2>nul;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
set autoddl off;
commit;
execute block returns (o int) as
    declare temporary table tbase(
        id016 smallint
       ,id032 int
       ,id064 bigint
       ,id128 int128
       ,flt float
       ,dbl double precision
       ,dec_02_2 decimal(2,2)
       ,dec_04_0 decimal(4)   -- int (exact)
       ,dec_04_2 decimal(4,2) -- int (data * 1e2)
       ,num_02_2 numeric(2,2) -- smallint
       ,num_04_0 numeric(4)   -- smallint (exact)
       ,num_04_2 numeric(4,2) -- smallint (data * 1e2)
       ,num_09_0 numeric(9)
       ,num_09_9 numeric(9,9)
       ,num_10_0 numeric(10)     -- bigint (data * 1e4)
       ,num_10_10 numeric(10,10)
       ,num_38_0 numeric(38)
       ,num_38_38 numeric(38,38) -- int128 (data * 1e6)
       ,df_16 decfloat(16)
       ,df_34 decfloat(34)
       ,dt date
       ,tm time
       ,ts timestamp
       ,tmtz time with time zone
       ,tstz timestamp with time zone
       ,tbin binary -- default length: 1
       ,tchr char(10)
       ,vbin varbinary(10)
       ,vchr_utf8 varchar(10) character set utf8 collate unicode_ci_ai
       ,vchr_1250 varchar(10) character set win1250 collate win_cz_ci_ai
       ,nchr nchar(10) -- iso8859-1
       ,vnch national char varying(10)
       ,boo boolean
       ,bbin blob sub_type 0
       ,btxt blob sub_type 1 character set win1250 collate win_cz
    );
begin
        insert into tbase(
           id016
           ,id032
           ,id064
           ,id128
           ,flt
           ,dbl
           ,dec_02_2
           ,dec_04_0
           ,dec_04_2
           ,num_02_2
           ,num_04_0
           ,num_04_2
           ,num_09_0
           ,num_09_9
           ,num_10_0
           ,num_10_10
           ,num_38_0
           ,num_38_38
           ,df_16
           ,df_34
           ,dt
           ,tm
           ,ts
           ,tmtz
           ,tstz
           ,tbin
           ,tchr
           ,vbin
           ,vchr_utf8
           ,vchr_1250
           ,nchr
           ,vnch
           ,boo
           ,bbin
           ,btxt
        ) values(
            -32768
           ,-2147483648
           ,-9223372036854775808
           ,-170141183460469231731687303715884105728
           ,pi()
           ,pi()
           ,-327.68
           ,-327.68
           ,-327.68
           ,-327.68
           ,-327.68
           ,-327.68
           ,999999999
           ,.999999999
           ,9999999999
           ,.9999999999
           ,99999999999999999999999999999999999999
           ,.99999999999999999999999999999999999999
           ,cast( 9.999999999999999E384 as decfloat(16))
           ,cast( 9.999999999999999999999999999999999E6144 as decfloat(34))
           ,date '01.01.0001'
           ,time '23:59:59.999'
           ,'31.12.9999 23:59:59.999'
           ,time '11:11:11.111 Indian/Cocos'
           ,timestamp '2018-12-31 12:31:42.543 Pacific/Fiji'
           ,'A'                    -- tbin binary
           ,'CAFEBABE'             -- tchr char(10)
           ,x'FACEFEED'            -- vbin varbinary(10)
           ,'𒀂𒀃𒀄𒀅𒀆𒀇𒀈𒀉𒀊𒀋' -- vchr_utf8 varchar(10)
           ,'ŁŞŻŔĹĆÇČĘŮ'           -- vchr_1250 varchar(10)
           ,'ÁÂÃÄÅÆÇÈÉÊ'           -- nchr nchar(10)
           ,'çèéêëìíîïð'           -- vnch national char varying(10)
           ,true                   -- boolean
           ,null -- x'BAAAAAAD'    -- blob binay
           ,null -- 'BAAAAAAD'     -- blob text
        )
    ;
    o = 1;
    suspend;
end;

===========

I completes OK.

Now replace any of 'null'  (last two literals in INSERT-VALUES statement, marked in yellow) with non-empty values.

FB will terminate with issuing in log:

===========

PZ Wed Jul 22 00:25:37 2026
Database: R:\TEMP\TMP4TEST.FDB
internal Firebird consistency check (pointer page vanished from relation list in locate_space (254), file: dpm.cpp line: 4035)
...
PZ Wed Jul 22 00:25:40 2026
REMOTE INTERFACE/gds__detach: Unsuccessful detach from database.
Uncommitted work may have been lost.
Error writing data to the connection.

===========

Dump, stack trace and snapshot (checked on 6.0.0.2090-eecfe01-x64) are here.

[Pavel Zotov]

PS.

OFFTOP. Question about syntax:

DECLARE [LOCAL] TEMPORARY TABLE <table_name>

-- for what here 'temporary' clause is needed ?

(if such kind of tables can exists only within scope of enclosing PSQL units)

 

[Adriano dos Santos Fernandes]

On 7/21/26 18:36, Pavel Zotov wrote:
> Now replace any of 'null'  (last two literals in INSERT-VALUES
> statement, marked in yellow) with non-empty values.

Fixed, thanks.


Adriano

[Pavel Zotov]

> Now replace any of 'null'  (last two literals in INSERT-VALUES
> statement, marked in yellow) with non-empty values.

Fixed, thanks.

Unfortunately, fix seems to be incompleted.

Consider this example:

===========

set bail on;
set blob all;
set list on;
set names utf8;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb 2>nul;

create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

set autoterm on;
set autoddl off;
commit;
create or alter procedure sp_test returns(
   txt_1 blob sub_type 1 character set win1250 collate win_cz

) as
   
    declare temporary table tbase(

       txt_1 blob sub_type 1 character set win1250 collate win_cz

    )
    ;
begin
    insert into tbase(

        txt_1
    ) values(
        'qwerty'
    ) returning
        txt_1
    into
        txt_1
    ;
    suspend;
end;

select p.* from sp_test p;

===========

 

[Pavel Zotov]

[  Consider this example:  ]

... if we run it then outcome will be:

Statement failed, SQLSTATE = 42000
invalid BLOB ID

firebird.log:

==============

PZ Wed Jul 22 14:23:27 2026
Database: R:\TEMP\TMP4TEST.FDB
internal Firebird consistency check (record disappeared (186), file: Savepoint.cpp line: 281)
...
PZ Wed Jul 22 14:23:32 2026

REMOTE INTERFACE/gds__detach: Unsuccessful detach from database.
Uncommitted work may have been lost.
Error writing data to the connection.

==============

This is URL to dump, stack trace and snapshot  6.0.0.2090-cb53751.

[Adriano dos Santos Fernandes]

On 7/22/26 08:44, Pavel Zotov wrote:
>
> > Now replace any of 'null'  (last two literals in INSERT-VALUES
> > statement, marked in yellow) with non-empty values.
>
> Fixed, thanks.
>
>
> Unfortunately, fix seems to be incompleted.

Fixed.


Adriano

[Pavel Zotov]

> Now replace any of 'null'  (last two literals in INSERT-VALUES
> statement, marked in yellow) with non-empty values.
Fixed, thanks.

> Unfortunately, fix seems to be incompleted.
Fixed.

Confirmed fix on 6.0.0.2097-ba7ca6c. 

Two tests has been committed:  test_dtt_datatypes.py ;  test_dtt_dml_statements.py