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

RDB$INDICES information stored inconsistently after a CREATE INDEX [CORE3741] #4085

Closed
firebird-automations opened this issue Jan 27, 2012 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @mrotteveel

The information in RDB$INDICES is stored inconsistently for the true/false values of 1 and 0 when an index is created using CREATE INDEX.

RDB$UNIQUE_FLAG is 1 for a unique index, but NULL for non-unique (I would expect 0)
RDB$INDEX_INACTIVE is NULL for newly created indices (I would expect 0), issuing an ALTER INDEX <name> INACTIVE makes it 1 and 0 if it has been again altered to ACTIVE
RDB$INDEX_TYPE is 1 for DESCENDING indices, but NULL for ASCENDING (I would expect 0)

After a backup and restore, these fields do get the expected values.

Small DDL script that will demonstrate most of this:
CREATE TABLE TEST_COLUMN_DEFAULTS
(
COL_INTEGER_DEFAULT_NULL Integer DEFAULT NULL,
COL_INTEGER_DEFAULT_999 Integer DEFAULT 999,
COL_VARCHAR_DEFAULT_NULL Varchar(100) DEFAULT NULL,
COL_VARCHAR_DEFAULT_USER Varchar(100) DEFAULT USER,
COL_VARCHAR_DEFAULT_LITERAL Varchar(100) DEFAULT 'literal'
);
CREATE INDEX IDX_COMPUTED ON TEST_COLUMN_DEFAULTS COMPUTED BY ('!'||COL_VARCHAR_DEFAULT_USER||'!');
CREATE DESCENDING INDEX IDX_COMPUTED2 ON TEST_COLUMN_DEFAULTS COMPUTED BY ('!'||COL_VARCHAR_DEFAULT_USER||'!');
CREATE DESCENDING INDEX IDX_TEST_COLUMN_DEFAULTS1 ON TEST_COLUMN_DEFAULTS (COL_INTEGER_DEFAULT_NULL);
CREATE UNIQUE INDEX IDX_TEST_COLUMN_DEFAULTS2 ON TEST_COLUMN_DEFAULTS (COL_INTEGER_DEFAULT_999);

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Also note that in a newly created database all ASCENDING system indices also have RDB$INDEX_TYPE set to NULL instead of 0 (the other fields are set as expected). A backup and restore does not change this.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

While I agree it may look inconsistent, I wouldn't call this a bug. Historically, zero and NULL mean the same thing in the system tables, at least for the various flag fields. I remember Claudio "fixing" this inconsistency for RDB$SYSTEM_FLAG, but some other fields still keep the legacy semantics.

That said, I don't insist on turning this ticket into the improvement request, a bug with a low priority is also fine with me.

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

If you would query the system tables for lets say RDB$INDEX_TYPE = 0 or RDB$INDEX_TYPE != 1 when you are interested only in ASCENDING indices, then you will not get back all expected indices.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

COALESCE(RDB$INDEX_TYPE, 0) = 0

@dyemanov
Copy link
Member

RDB$UNIQUE_FLAG and RDB$INDEX_TYPE are filled correctly (as you expect) starting with FB3, however RDB$INDEX_INACTIVE is still NULL. I'm going to commit a remaining fix.

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

No branches or pull requests

2 participants