Weird violation of FOREIGN KEY for [var]binary datatype when PK and FK indices have opposite direction

[Pavel Zotov]

Consider script:

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

set list on;
set echo on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

recreate table test(f01 varchar(16) character set octets not null, f02 varchar(16) character set octets);
set term ^;
execute block as
    declare n int = 1; ------------------ [ 1 ]
begin
    while ( n > 0 ) do
    begin
        insert into test(f01) values(gen_uuid());
        n = n - 1;
    end
end
^
set term ;^
update test set f02 = f01;
commit;
select count(*) as mism_cnt from test where f02 is distinct from f01;
commit;

alter table test add constraint test_f01_pkey primary key(f01)
-- using descending index test_f01_pk_desc
;
alter table test add constraint test_f02_fkey foreign key(f02) references test(f01)
using descending index test_f02_fk_desc
;

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

On 3.x ... 6.x,  there is a 99.(9)% chance this script will complete OK.

Now change value of `n` in the line marked as `[  1 ]` to 20'000 and repeat.

With same chance (99.9%) script will fail with tail like this:

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

Statement failed, SQLSTATE = 23000
unsuccessful metadata update
-ALTER TABLE "PUBLIC"."TEST" failed
-violation of FOREIGN KEY constraint "TEST_F02_FKEY" on table "PUBLIC"."TEST"
-Foreign key reference target does not exist
-Problematic key value is ("F02" = x'00E9E83671334B3894CC8E2F3CA04AF2')

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

Of course, we can check presense of row with problematic value:

echo set list on; select f01,f02,f01 is distinct from f02 from test where f02=x'00E9E83671334B3894CC8E2F3CA04AF2'; | C:\FB\60SS\isql -q /:r:\temp\tmp4test.fdb

But the output always will be:

F01    00E9E83671334B3894CC8E2F3CA04AF2
F02    00E9E83671334B3894CC8E2F3CA04AF2
BOOL   <false>

It seems that problem occurs when:

1) FK and PK indices have opposite directtion;

2) only when datatype as varbinary.

Reproduced on:

3.0.14.33855; 4.0.8.3279; 5.0.5.1826; 6.0.0.2002

[Vlad Khorsun]

11.06.2026 23:23, Pavel Zotov:

> Now change value of `*n*` in the line marked as `[  1 ]` to 20'000 and repeat.

> With same chance (99.9%) script will fail with tail like this:
> ===============
> Statement failed, SQLSTATE = 23000
> unsuccessful metadata update
> -ALTER TABLE "PUBLIC"."TEST" failed
> -violation of FOREIGN KEY constraint "TEST_F02_FKEY" on table "PUBLIC"."TEST"
> -Foreign key reference target does not exist
> -Problematic key value is ("F02" = x'00E9E83671334B3894CC8E2F3CA04AF2')
> ===============
>
> Of course, we can check presense of row with problematic value:
> echo set list on; select f01,f02,f01 is distinct from f02 from test where f02=x'00E9E83671334B3894CC8E2F3CA04AF2'; | C:\FB\60SS\isql
> -q /:r:\temp\tmp4test.fdb
>
> But the output always will be:
> F01    00E9E83671334B3894CC8E2F3CA04AF2
> F02    00E9E83671334B3894CC8E2F3CA04AF2
> BOOL   <false>
>
> It seems that problem occurs when:
> 1) FK and PK indices have opposite directtion;
> 2) only when datatype as varbinary.
>
> Reproduced on:
> 3.0.14.33855; 4.0.8.3279; 5.0.5.1826; 6.0.0.2002

Create the issue, please

Regards,
Vlad

[Pavel Zotov]

https://github.com/FirebirdSQL/firebird/issues/9059