--- In firebird-support@yahoogroups.com, "sasidhardoc"
<madhusasidhar@...> wrote:

>
> Adam,
> I must admit that you are extraordinarily intuitive.

No need to be like that. The release notes clearly state

4. Don't use the regular bug-tracker or the firebird-support list to
report bugs in the beta or to ask
for expanded details about how a new feature works.

> Given the problem
> of case insensitive searches, I looked up the release notes - which
> states that "Expression indices have exactly the same features and
> limitations as regular indices, except that, by definition, they
> cannot be composite (multi-segment)." In fact, even the example uses
> the same column twice. Then, I proceeded to actually try to create

a

> multisegment index
> RECREATE TABLE DRG_DRUG_ID
> (
> DRUG_ID INTEGER NOT NULL,
> DRUG_NAME VARCHAR( 255) COLLATE NONE,
> PRIMARY KEY (DRUG_ID)
> );
>
> CREATE UNIQUE ASC INDEX I_DRG_DRUG_NAME ON DRG_DRUG_ID (DRUG_NAME,
> DRUG_ID);
>
> CREATE ASC INDEX I_DRG_DRUG_ID_TEST ON DRG_DRUG_ID COMPUTED BY
> (upper(DRUG_NAME)||DRUG_ID);
>
> - which actually compiled successfully. Suspecting that I may not
> have understood the meaning of the term "composite index" I checked

pg

> 322 of The Firebird Book which states "...it will be of benefit to
> create a multi-column index(also known as a composite or complex
> index)". If there is a better place to ask this question, please

let

> me know - I did not think this was a 'bug'.
>

It certainly falls into the 'expanded details about how a new feature
works' clause.

(Upper(DRUG_NAME)||DRUG_ID) is not as far as I understand a composite
index. It is an expression that returns a single column that just
happens to be sourced from two different fields.

(Upper(DRUG_NAME),DRUG_ID) would be a composite index.

The index structure has been changed in that version to solve the
problem of slow garbage collection, so it may not even be necessary
to include the ID, unless you have lots of different records with an
identical DRUG_NAME and you want to sort by the ID.

Whether it is a bug or not depends on whether it behaves according to
the specification or not, but FB 2 is not yet release stable, so
questions about it in the release-stable support list are white noise.

Adam