Issue Details (XML | Word | Printable)

Key: CORE-3741
Type: Bug Bug
Status: Open Open
Priority: Trivial Trivial
Assignee: Unassigned
Reporter: Mark Rotteveel
Votes: 0
Watchers: 0
Operations

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

RDB$INDICES information stored inconsistently after a CREATE INDEX

Created: 27/Jan/12 04:45 PM   Updated: 27/Jan/12 05:35 PM
Component/s: Engine
Affects Version/s: 2.5.1
Fix Version/s: None


 Description  « Hide
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);

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 27/Jan/12 04:52 PM - edited
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.

Dmitry Yemanov added a comment - 27/Jan/12 04:53 PM
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.

Mark Rotteveel added a comment - 27/Jan/12 05:26 PM - edited
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.

Dmitry Yemanov added a comment - 27/Jan/12 05:35 PM
COALESCE(RDB$INDEX_TYPE, 0) = 0