Commented by: @ibaseru

I suggest to disable any select statements in expression indices. So this feature request will not raise.

Commented by: Alexander Tyutik (tut)

But such bug exists in current indicies. You can run such command:

CREATE INDEX TABLE1_IDX1 ON TABLE1 COMPUTED BY ((SELECT FIRST 1 ID FROM TABLE2));

and it will be compiled without problems.

So, this problem also exists without COMPUTED BY fields

Commented by: Philip Williams (unordained)

create table zang
(
eff timestamp,
expr timestamp,
is_current computed by (iif((eff is null or eff < current_timestamp) and
(expr is null or expr > current_timestamp), 1, 0))
);
create index ix_zang_curr on zang computed by (is_current);
create index ix_zang_curr2 on zang computed by (iif((eff is null or eff < current_timestamp) and
(expr is null or expr > current_timestamp), 1, 0));

A few notes from my quick testing on FB 2.1 RC1
- index ix_zang_curr gets a selectivity of 1.0, always, even when there are more distinct values than that, and despite recalculating statistics on it; this is annoying as it acts like you've successfully indexed the computed-by column, yet the index never gets used!
- index ix_zang_curr2 gets correct selectivity and will even be automatically used when you select from zang with a where or order-by on is_current (no need to repeat the full expression in your SELECT -- repeating the expression in the index is far less of a problem than repeating it in every query, so that's a good workaround!)

However, it seems that current_timestamp is evaluating to NULL (or worse, throwing an exception silently?) when the indexer runs and you wind up not being able to find these rows when it uses the index -- the only rows that will be found are "is_current = 0" (eff was not null and expr was not null) and "is_current = 1" (eff was null and expr was null); anything else can't be found by index, even looking for "is_current is null". I had test data with eff null and expr not null, etc. and those rows would only turn up if I tricked the optimizer into not using the index. This is despite the fact that with iif(), nulls in the expression should have resulted in a computed value of zero, and been indexed as such. It almost seems like the only reason rows can be found is the short-circuit evaluation of OR, but that's not right either; "false or ..." is not obvious, yet those rows can be found.

select * from zang where is_current = 0; -- 10102 rows
select * from zang where is_current + 1 = 1; -- 10104 rows (adds two cases where eff is null but expr is not null)
select * from zang where is_current = 1 ; -- 1 row (eff is null and expr is null)
select * from zang where is_current not in (0,1); -- 0 rows
select * from zang where is_current is null; -- 0 rows
select * from zang; -- 10107 rows (incl. some where expr is null and eff is not null)
select * from zang order by is_current; -- 10107 rows, but not correctly sorted in terms of visible data (expected behavior b/c of the timestamps shown being computed on the fly vs. indexed values)

I'm not saying that current_timestamp is a good idea in a computed-by index (I really do know better, I was just testing the limits of 2.1 RC1) but it would be good for the documentation to say how this is handled and maybe why rows can't be found in my example; it may wind up being disallowed, but other cases involving custom UDFs wouldn't be so easy to disable. (I'm thinking about Oracle's "deterministic" flag on functions; you could hard-code that for current_timestamp, but *every* UDF? There are bound to be holes.)

Commented by: Timo Partanen (partim)

What makes the need for creating an index for a COMPUTED BY field more relevant is composite indexes (i.e. indexes that are made up of more than one field/column).

Consider the following table. Is there any way to create a composite index for the PropertyDef column and an expression that computes the lowercase value of the Data column?

CREATE TABLE PV_TEXT
(
PROPERTYDEF INTEGER NOT NULL,
DATA VARCHAR( 100 ) COLLATE UNICODE_FSS
)

If the table contained a computed column Data_Lower such as "DATA_LOWER COMPUTED BY ( MF_UTF8LOWER( DATA ) )" creating a composite index for PropertyDef and Data_Lower should be possible. However, this fails:

CREATE ASC INDEX IX_PV_TEXT_PD_DATA_LOWER
ON PV_TEXT ( PROPERTYDEF, DATA_LOWER )

(The error is "unsuccessful metadata update attempt to index COMPUTED BY column in INDEX IX_PV_TEXT_PD_DATA_LOWER".)

Commented by: Sean Leyne (seanleyne)

The most significant issue that I can see is that not all COMPUTED BY columns can/should be indexable.

Consider that you have 2 tables:

CREATE Product(
PRO_ID Integer,
PRO_Description VARCHAR( 80)
);

CREATE SalesDetail(
SAD_ID Integer,
SAD_PRO_ID Integer,
SAD_PRO_Description COMPUTED BY ((SELECT PRO_Description FROM Product WHERE PRO_ID = SAD_PRO_ID)),
SAD_Quantity Integer
);

In the above case an index on SAD_PRO_Description would be invalid/should not be allowed.

Commented by: Eric Bole-Feysot (qwench)

I reported an issue with index on 'computed by' field.
Have a look : http://stackoverflow.com/questions/30092909/using-computed-upper-index-in-firebird-to-compare-case-insensitive-strings

Commented by: @dyemanov

I'm resolving this ticket as a duplicate for CORE1173. If someone wants a better protection against "bad" (e.g. volatile) index expressions, please create a separate ticket.