Can insert DUPLICATE keys in UNIQUE index
------------------------------------------
Key: CORE-3660
URL: http://tracker.firebirdsql.org/browse/CORE-3660
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.1, 2.1.4
Reporter: Veselin Pavlov
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 http://tracker.firebirdsql.org/browse/CORE-3610, but I'm not sure
the issue is the same.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
RSA(R) Conference 2012
Save $700 by Nov 18
Register now
http://p.sf.net/sfu/rsa-sfdev2dev1
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
