Hey all,

I keep banging my head into a mysterious index-length issue that's
killing me. For example, if I have the following table definition:

create table foo(
col1 decimal(18, 0) not null,
col2 varchar(188) not null
);

I can create an index as follows:

create index foo1 on foo(col1, col2);

However, if my table definition changes to this:

create table foo(
col1 decimal(18, 0) not null,
col2 varchar(189) not null
);

(Just a one-byte difference in col2) - then my index creation fails with:

unsuccessful metadata update
-key size exceeds implementation restriction for index "FOO1"

If the column size is 193 or larger, then I get this message:

unsuccessful metadata update
-key size too big for index FOO1

What's strange is that the limit (as far as I've read) for the size of
an index is 252 bytes. How could it be possible that a decimal(18, 0)
(pretty much a simple big integer which should take up about 10 bytes
or so) could have such an effect on the index size? The column isn't
multi-byte or anything.

I also notice that, whilest I can create an index on a varchar column
that's 252 characters long with no problem. But, attempting to create
an index on two varchar(126) columns fails in the same way. For a
compound index on two varchars to happen, they must be of a length of
100 or less. That means that there is (at least in these two cases) a
52-byte (approx) overhead of some kind when creating a compound index.

That's really bizarre. Shouldn't a compound index simply be the
concatenation of the column values? Is there a reference site that
describes the bizarre nature of compound index lengths in Firebird?

Thanks,

Marc