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
