Am Donnerstag, 30. April 2015, 17:28:32 schrieb Dmitry Yemanov
[email protected] [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
