BOOLEAN DEFAULT FALSE NOT NULL for key-column

[Former Member]

Hello,

These statements show an unexpected behavior when a column is added to a table as 'BOOLEAN DEFAULT FALSE NOT NULL' and added afterward to the table's primary key column set:

create table test_1 (a char(1))

insert into test_1 values('A')

alter table test_1 add b boolean default false not null

alter table test_1 add primary key (a,b)

create table test_2 (a char(1), b boolean default false not null)

insert into test_2 (a) values('A')

alter table test_2 add foreign key f_test_1 (a,b) references test_1 (a,b)

-> [350]: Referential integrity violated

update test_1 set b=false

alter table test_2 add foreign key f_test_1 (a,b) references test_1 (a,b)

-> success

delete from test_2

delete from test_1

insert into test_1 (a,b) values('A',false)

insert into test_2 (a) values('A')

-> success

I think the error message '[350] Referential integrity violated' should not happen because the column 'b' really contains 'false'. But there obviously seem to be a difference before and after setting the column 'b' explicitly to 'false'. I can imagine that this depends on the way how the index for the primary key is updated. Probably the index is not properly updated in this context(?)

Gabriel

[Former Member]

Hallo,

sorry for any inconverniences.

This misbehaviour has been fixed with versions 7.7.04 and higher.

I think you are using some 7.6, aren't you?

The workaround (update ... = false) can be seen in your example.

Sorry again,

Elke

[Former Member]

Hello Elke, Lars

Thank you for your detailed and clear explanation.

Yes, sorry, I forgot to mention that we use version 7.6.05.09.

Best regards,

Gabriel

[lbreddemann]

Hi Gabriel,

you're right, this is a bug and indeed seems to caused by the way the DEFAULT boolean is stored in the page.

(There is no separate index for the primary key in MaxDB as all data is stored in B*trees - basically the table is the primary key).

This is how the record looks like when column b is 'false' only due to the change of the DEFAULT value:

ROOT/LEAF 460  perm       entries : 1         [block 0]
     bottom  : 93         filevers: 14888     convvers: 83
                                              writecnt: 1
 
  1: (pos 00081)
00001      recLen      : 12                recKeyLen   : 4
00005      recVarcolOff: 0                 recVarcolCnt: 0
     record
      1  2  3  4  5  6  7  8  9 10 11 12
     81 82 83 84 85 86 87 88 89 90 91 92
dec: 12  0  4  0  0  0  0  0 32 65  0  0
hex: 0C 00 04 00 00 00 00 00 20 41 00 00
chr:                             A

And this is how it looks like after the explicit UPDATE:

ROOT/LEAF 460  perm       entries : 1         [block 0]
     bottom  : 93         filevers: 14888     convvers: 84
                                              writecnt: 2
 
  1: (pos 00081)
00001      recLen      : 11                recKeyLen   : 3
00005      recVarcolOff: 0                 recVarcolCnt: 0
     record
      1  2  3  4  5  6  7  8  9 10 11
     81 82 83 84 85 86 87 88 89 90 91
dec: 11  0  3  0  0  0  0  0 32 65  0
hex: 0B 00 03 00 00 00 00 00 20 41 00
chr:                             A

Little difference but this leads to the problems during the foreign key validation.

I'll inform the developers next week about this.

As a workaround you'll have to explicitly update the columns for which you change the default setting.

regards,

Lars