Issue Details (XML | Word | Printable)

Key: CORE-6252
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Mattia Rosin
Votes: 0
Watchers: 5
Operations

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

UNIQUE CONSTRAINT violation

Created: 19/Feb/20 02:32 PM   Updated: 25/Feb/20 02:01 AM
Component/s: Engine
Affects Version/s: 3.0.5
Fix Version/s: 4.0 Beta 2, 3.0.6

Environment:
Server Version : Ubuntu 16.04.6 LTS Linux
Firebird Version : LI-V3.0.5.33220 Firebird 3.0


QA Status: Done successfully


 Description  « Hide
Updating the data of a table with non-unique data and the creation of a unique costraint on the same table in the same transaction, does not result in the exception of non-unique records

Example :

SET TERM ^;
/* Creating simple stucture */
DROP TABLE test_table
^

CREATE TABLE test_table(
  a integer,
  b integer
)
^

COMMIT
^

/* Simple Data*/
INSERT INTO test_table(a, b) VALUES (1,1)
^

INSERT INTO test_table(a, b) VALUES (1,2)
^

COMMIT
^

/* Problem start here */
UPDATE test_table
SET b = -999
^

ALTER TABLE TEST_TABLE
ADD CONSTRAINT UNQ1_TEST_TABLE
UNIQUE (A)
^

COMMIT
^
/* Now test_table have a unique costraint on column a but column a not contains unique value */


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Carlos H. Cantu added a comment - 19/Feb/20 02:57 PM
I hear this for years: Mixing DML and DDL in the same transaction is ask for trouble :-(
FB should deny such situations since it seems that mixing both operations in the same transaction is not "safe". A better option, of course, would be to fix the problems, but I'm not sure if this is possible.

Pavel Zotov added a comment - 19/Feb/20 03:03 PM
This is author's slightly modified script; checked on 2.1.7, 2.5.9, 3.0.6, 4.0.0 - result the same. It will create PK and UNIQUE for table with fully duplicate rows.

set bail on;
shell del c:\temp\tmp4test.fdb 2>nul;
create database 'localhost:c:\temp\tmp4test.fdb';
show version;

set autoddl off; -- [ !! ]

commit;
set echo on;
recreate table test(
  a int not null,
  b int not null
);

commit;

insert into test(a, b) values (1,1);
insert into test(a, b) values (1,2);

commit;


-------------------------------------------------------
update test set b = 1;
alter table test add constraint test_unq unique (a);
-------------------------------------------------------
commit;

-------------------------------------------------------
update test set a = 1;
alter table test add constraint test_pk primary key (b);
-------------------------------------------------------
commit;

show table test;
show index test;

select * from test;
commit;