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

[firebird-automations]

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]

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]

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]

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]

Commented by: @dyemanov

COALESCE(RDB$INDEX_TYPE, 0) = 0

[dyemanov]

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.