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

CREATE INDEX considers NULL and empty string being the same in compound indices [CORE3675] #4025

Closed
firebird-issue-importer opened this issue Nov 24, 2011 · 17 comments

Comments

@firebird-issue-importer

Submitted by: Edson Teixeira Marques (edsontmarques)

Is related to CORE2709
Is duplicated by CORE3937

Votes: 1

The situation is as follows:
A table has an alternate key constraint (unique). The expression of key is composed of four distinct fields of the table. In two rows from the table, the field of order 2 (in the expression of key), has the following "values" (in quotes because is "null" is not value):null and ''(empty) respectively. That is, in the record 'n' the field is set to null, in the record n​​+1 the same field have the value''(empty). The other values ​​of the other fields components of the alternate key in these records have values ​​equal to each other. As illustrated below:

TABLE1
FIELD_A, FIELD_B, ..., FIELD_1 | FIELD_2 | FIELD_3 | FIELD_4, ..., FIELD_n
-------------------------------------------------- -----------------------------------------------
...
any, any, ..., any | any | any | any, ..., any
any, any, ..., x | null | y | z, ..., any
any, any, ..., x | | y | z, ..., any
any, any, ..., any | any | any | any, ..., any
...
-------------------------------------------------- -----------------------------------------------
('any': any values​​, different from each other. x, y and z can be any value, but equals in the two rows)

What happens is that when I recover a ".fbk" of this database, gbak can not activate the alternate key index coposed by FIELD_1, FIELD_2, FIELD_3 and FIELD_4, because it believes there may be a violation of the unique key constraint, however, if the TABLE1 does not have any records (no data), gbak, obviously, can recover the database and enable the constraint and, in this situation, I can insert all these records with all these values ​​(that gbak couldn't recover previously), and there's no unique key violation.
This issue has been identified in firebird 2.5.1. We tested the same database in firebird 2.5.0 and this problem did not happen.

Commits: 161386e cf6e3d0

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 25, 2011

Commented by: Andrei Kireev (andreik)

Seems a similar problem as of CORE3660 and CORE3610

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 25, 2011

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE3610 [ CORE3610 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 25, 2011

Modified by: Sean Leyne (seanleyne)

Link: This issue is related to CORE3660 [ CORE3660 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Modified by: @dyemanov

Link: This issue is related to CORE3610 [ CORE3610 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Commented by: @dyemanov

No, they look different (at least CORE3610).

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Modified by: @dyemanov

summary: gbak can't activate unique key index when field is null => CREATE INDEX considers NULL and empty string being the same in compound indices

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Modified by: @dyemanov

Component: Engine [ 10000 ]

Component: GBAK [ 10006 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Modified by: @dyemanov

Link: This issue is related to CORE3660 [ CORE3660 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Modified by: @dyemanov

Link: This issue is related to CORE2709 [ CORE2709 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 29, 2011

Commented by: @dyemanov

Looks like a regression introduced by CORE2709.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 14, 2012

Modified by: @dyemanov

Fix Version: 2.5.2 [ 10450 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 4, 2012

Modified by: @dyemanov

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 7, 2012

Modified by: @dyemanov

Link: This issue is duplicated by CORE3937 [ CORE3937 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 23, 2013

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 29, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants