Am Donnerstag, 30. April 2015, 17:28:32 schrieb Dmitry Yemanov dimitr@... [firebird-support]:

> > Reading the mailing list I just learned that this is possible.
> >
> > Experimenting shows that (using FB 2.5) this does have limitations.
>
> There's no such FB version.

Sorry for being inexact. I meant the 2.5.x series. Since the documentation only describes features and since
there hopefully is no change of features between any 2.5.x version and since changing index expressions to
accept arbitrarily complex expressions certainly qualifies as a new feature I thought this is irrelevant.

According to the Windows 7 software manager (leaving out the exact windows version) I have this version:

Firebird 2.5.3.26780 (Win32).

Does that syntax mean there could also be 2.5.3.34567 or is simply inconsistent to include the build number
in the version number only sometimes?
(Sorry for nitpicking but you asked for it)

> It should work on v2.5.4.

Please specify "It". Do you mean no restrictions at all?

I see the fix for CORE-4673 since 2.5.3 but it only partially addresses my concerns.

Can you please explain how the following index is implemented?

SQL> create table test (id integer);
SQL> create index idxa on test computed by (case when extract(day from cast('TODAY' as date))=1 or (select id from othertable)=5 then 0 else 1 end);
SQL> show index idxa;
IDXA INDEX ON TEST COMPUTED BY (case when extract(day from cast('TODAY' as date))=1 or (select id from othertable)=5 then 0 else 1 end)


I would think this index must be recreated for each and every single use, even within the same transaction, unless
firebird analyzes its dependencies on the current date and buchung.id and traces changes to those values. But then – how useful can
such an index be? Also, I would be a bit surprised if firebird really supports parsing arbitrary complex dependency
structures for the index expression.

What about this? (where v2_min is a UDF using arbitrarily complex rules to generate its output)

SQL> create index idxc on test computed by (case when v2_min(current_transaction,0)>1000 then 0 else 1 end);
SQL> show index idxc;
IDXC INDEX ON TEST COMPUTED BY (case when v2_min(current_transaction,0)>1000 then 0 else 1 end)

Firebird sure cannot analyze the C code of v2_min which might change with every invocation even within
the same transaction. So how can it make use of such an index? If not at all, why does FB allow creation
of such an obviously useless index?

--
Wolfgang