Broken foreign key handling for multi-segmented index using multi-level collations [CORE1997]

[firebird-automations]

Submitted by: @asfernandes

Is related to QA474

Using multilevel collations in a key with more than one field, FB uses interleaved keys.

For a key with fields (a, b):
- full key: <level 1 for a>...<level n for a><level 1 for b>...<level n for b>
- unique key for non-insensitive collations: same as full key
- unique key for insensitive collations, may use less number of levels: <level 1 for a><level 1 for b>

This doesn't cause any problem in these conditions:
- Non insensitive collations that compares strings as if it's comparing the keys
- Insensitive collations used in unique index, as the unique key is used there

For non-unique index (see TEXTTYPE_SEPARATE_UNIQUE in intlobj_new.h), the optimizer handle (or try :-)) the situation for index walking more or less as following:
a = 'x' and b = 'y' => a starting with 'x'

For handling the non-unique side of a foreign key (when deleting/modifying the unique/primary side), the same code should be used there. But this code is using BTR_key which generate keys different than BTR_make_key (used in index walk) and doesn't consider the lower/upper retrieval attributes.

Test case:

create table pk (
c1 varchar (5) character set utf8 collate unicode_ci,
c2 varchar (5) character set utf8 collate unicode_ci,
primary key (c1, c2)
);

create table fk (
c1 varchar (5) character set utf8 collate unicode_ci,
c2 varchar (5) character set utf8 collate unicode_ci,
foreign key (c1, c2) references pk
);

insert into pk values ('a', 'b');
insert into fk values ('A', 'b');

delete from pk; -- should not be allowed

select * from fk;

Commits: d16fad4 c3709b3 FirebirdSQL/fbt-repository@00fee42

[firebird-automations]

Modified by: @asfernandes

description: Using multilevel collations in a key with more than one field, FB uses interleaved keys.

For a key with fields (a, b):
- full key: <level 1 for a>...<level n for a><level 1 for b>...<level n for b>
- unique key for non-insensitive collations: same as full key
- unique key for insensitive collations, may use less number of levels: <level 1 for a><level 1 for b>

This doesn't cause any problem in these conditions:
- Non insensitive collations that compares strings as if it's comparing the keys
- Insensitive collations used in unique index, as the unique key is used there

For non-unique index (see TEXTTYPE_SEPARATE_UNIQUE in intlobj_new.h), the optimizer handle (or try :-)) the situation for

index walking more or less as following:
a = 'x' and b = 'y' => a starting with 'x'

For handling the non-unique side of a foreign key (when deleting/modifying the unique/primary side), the same code should

be used there. But this code is using BTR_key which generate keys different than BTR_make_key (used in index walk) and

doesn't consider the lower/upper retrieval attributes.

Test case:

create table pk (
c1 varchar (5) character set utf8 collate unicode_ci,
c2 varchar (5) character set utf8 collate unicode_ci,
primary key (c1, c2)
);

create table fk (
c1 varchar (5) character set utf8 collate unicode_ci,
c2 varchar (5) character set utf8 collate unicode_ci,
foreign key (c1, c2) references pk
);

insert into pk values ('a', 'b');
insert into fk values ('A', 'b');

delete from pk; -- should not be allowed

select * from fk;

=>

Using multilevel collations in a key with more than one field, FB uses interleaved keys.

For a key with fields (a, b):
- full key: <level 1 for a>...<level n for a><level 1 for b>...<level n for b>
- unique key for non-insensitive collations: same as full key
- unique key for insensitive collations, may use less number of levels: <level 1 for a><level 1 for b>

This doesn't cause any problem in these conditions:
- Non insensitive collations that compares strings as if it's comparing the keys
- Insensitive collations used in unique index, as the unique key is used there

For non-unique index (see TEXTTYPE_SEPARATE_UNIQUE in intlobj_new.h), the optimizer handle (or try :-)) the situation for index walking more or less as following:
a = 'x' and b = 'y' => a starting with 'x'

For handling the non-unique side of a foreign key (when deleting/modifying the unique/primary side), the same code should be used there. But this code is using BTR_key which generate keys different than BTR_make_key (used in index walk) and doesn't consider the lower/upper retrieval attributes.

Test case:

create table pk (
c1 varchar (5) character set utf8 collate unicode_ci,
c2 varchar (5) character set utf8 collate unicode_ci,
primary key (c1, c2)
);

create table fk (
c1 varchar (5) character set utf8 collate unicode_ci,
c2 varchar (5) character set utf8 collate unicode_ci,
foreign key (c1, c2) references pk
);

insert into pk values ('a', 'b');
insert into fk values ('A', 'b');

delete from pk; -- should not be allowed

select * from fk;

[firebird-automations]

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

[firebird-automations]

Commented by: Attila Molnár (e_pluribus_unum)

It seems similar to CORE3052. Are they related?

[firebird-automations]

Commented by: @asfernandes

> It seems similar to CORE3052. Are they related?

No.

[firebird-automations]

Modified by: @asfernandes

Fix Version: 2.5.2 [ 10450 ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA474 [ QA474 ]

[firebird-automations]

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully