Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unique index could be created on non-unique data [CORE5161] #5444

Closed
firebird-issue-importer opened this issue Mar 21, 2016 · 6 comments
Closed

Comments

@firebird-issue-importer

Submitted by: Polesov (polesov)

Detected the error of violation of uniqueness of the primary key.
Check the database using GFIX program error is not detected.
Operation BACKUP/RESTORE runs without error, but a duplicate primary key remains.
Recreation of the primary key passes without error, but the duplicate remains.

Table structure:

CREATE DOMAIN D_ID AS INTEGER NOT NULL;

CREATE DOMAIN D_GUID AS VARCHAR(36) CHARACTER SET WIN1251 COLLATE WIN1251;

CREATE TABLE EMPLOYES (
ID D_ID NOT NULL,
GUID D_GUID NOT NULL
);

ALTER TABLE EMPLOYES ADD CONSTRAINT PK_EMPLOYES PRIMARY KEY (ID);

CREATE INDEX EMPLOYES_IDX_GUID ON EMPLOYES (GUID);

Commits: b0981f1 28709e9 33e65f6 e891e0f

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 21, 2016

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 21, 2016

Commented by: @hvlad

The problem occurs when records with same keys fall into separate sort run's and that run's not merged until
final phase of index creation (fast_load)

Test case:

SQL> create database 'core-5161.fdb';
Server version:
WI-T4.0.0.32400-dev Firebird 4.0 Unstable
SQL> create table t (id int);
SQL>
SQL> set term ^;
SQL> execute block as
CON> declare i int = 0;
CON> begin
CON> while (i < 100000) do
CON> begin
CON> insert into t values (:i);
CON> i = i + 1;
CON> end
CON> end
CON> ^
SQL> select count(*) from t;
CON> ^

            COUNT

=====================
100000

SQL> set term ;^
SQL> insert into t values(1);
SQL> commit;
SQL>
SQL> create unique index t_id on t(id);
SQL> select * from t where id = 1;

      ID

============
1
1

SQL> commit;
SQL> exit;

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 21, 2016

Modified by: @hvlad

Version: 3.0 RC2 [ 10048 ]

Version: 3.0 RC1 [ 10584 ]

Version: 3.0 Beta 2 [ 10586 ]

Version: 2.5.4 [ 10585 ]

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.1.7 [ 10651 ]

Version: 3.0 Beta 1 [ 10332 ]

Version: 2.5.3 [ 10461 ]

Component: Engine [ 10000 ]

summary: violation of uniqueness of the primary key => Unique index could be created on non-unique data

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 22, 2016

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.0 [ 10740 ]

Fix Version: 2.5.6 [ 10721 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 23, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 23, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment