02.05.2015 18:10, Wolfgang Rohdewald wrote:

> 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.

There may be bugs preventing some feature from working properly.

> 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)

Build number is irrelevant here, 2.5.3 is enough.

>> It should work on v2.5.4.
>
> Please specify "It". Do you mean no restrictions at all?

it = computed indices directly based on computed columns.

> 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?

Such complex index expressions are supported but you're correct that
they're practically useless. FB just provides you with a tool but it's
up to you to ensure that the tool is used properly.


Dmitry