Subject Re: [firebird-support] Usage of expression index - Known limitation?
Author Thomas Steinmaurer
> Leyne, Sean escreveu:
>> This is a known limitation of the *LIKE operator*.
>> {though I'm not sure this particular twist has been logged in the
>> tracker}
>> I tried the same statements with a "standard" index and they exhibited
>> the same modes.
> LIKE <constant not starting with wildcard> injects a STARTING <constant
> substring before the wildcard> that cause the index to be used.

I know. ;-)

As the
> PLAN is choose at prepare time

I know, that's the reason why a parametrized query can never use an
index with LIKE, even if the parameter value would have something like

, I see no good way to LIKE <non constant>
> to use indexes.

For a human being UPPER('somestring') is still a constant in respect of
if an index can be used depending on the result string, but probably
this one is hard to implement, because Firebird would have to know which
functions like UPPER won't change the result in a way which won't make
use of an index.

In all this cases, I'm talking about a non-parametrized query.

I thought Firebird might be clever enough to know that UPPER results in
another string, but I realize this one doesn't seem to be too easy.

Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions