Issue Details (XML | Word | Printable)

Key: CORE-1997
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 16/Jul/08 10:26 AM   Updated: 07/Apr/14 02:55 PM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.5 Initial, 2.1.0, 2.0.4, 2.5 Alpha 1, 2.1.1
Fix Version/s: 2.5.2, 3.0 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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;


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Attila Molnár added a comment - 13/Jan/12 11:56 AM
It seems similar to CORE-3052. Are they related?

Adriano dos Santos Fernandes added a comment - 13/Jan/12 12:04 PM
> It seems similar to CORE-3052. Are they related?

No.