firebird-automations
commented
12 years ago Commented by: @hvlad
> f1=1, f2=2, f3=null > f1=1, f2=2, f3=null > > if I make a query > > select * from table1 t where > f1=1 > and f2=2 > and f3 is null > > the result contains only one of the records if the plan is using the "UNQ1_TABLE1" index > the result contains both records if PLAN (T NATURAL) is used.
Looks like corrupted index. Run gfix -v -fu and show result here, please.
> After backup and restore under FB2.5 the problem persists.
I you mean that at restore FB not allows to create unique index on duplicated data then it is expected behavior. Same as below:
> If I drop the index and try to create it again I receive the error: "Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values. > attempt to store duplicate value (visible to active transactions) in unique index "UNQ_STOCK"."
> One of the null values is not exactly null
I don't understand this. Please explain.
Submitted by: Veselin Pavlov (pavlov_v)
Votes: 1
I have several databases working with fb 2.1.3 that has unique constrains on a group of fields but in fact there is duplicated records. Always one of the fields has null value. For example: CREATE TABLE TABLE1 ( ID INTEGER NOT NULL, F1 INTEGER, F2 INTEGER, F3 INTEGER ); ALTER TABLE TABLE1 ADD CONSTRAINT UNQ1_TABLE1 UNIQUE (F1, F2, F3); ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (ID);
f1=1, f2=2, f3=null f1=1, f2=2, f3=null
if I make a query
select * from table1 t where f1=1 and f2=2 and f3 is null
the result contains only one of the records if the plan is using the "UNQ1_TABLE1" index the result contains both records if PLAN (T NATURAL) is used.
After backup and restore under FB2.5 the problem persists. If I drop the index and try to create it again I receive the error: "Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values. attempt to store duplicate value (visible to active transactions) in unique index "UNQ_STOCK"."
I still can not simulate the problem. Only can observe the effect - One of the null values is not exactly null
I saw issue CORE3610, but I'm not sure the issue is the same.