Hi Uwe,

> In the firebird faq i read this
> Q: Why doesn't LIKE 'A%' always behave the same as STARTING WITH 'A'?
> A: This is a known problem in Firebird optimizer and has been fixed in
> Firebird 2.1.
>
> i use a table with a computed index:
> -- CREATE INDEX IDX_M_UPNNAME ON M COMPUTED BY (UPPER (NNAME))
>
> a)
> SELECT * FROM M WHERE UPPER(NNAME) STARTING WITH UPPER('z')
> -- PLAN (M INDEX (IDX_M_UPNNAME))
>
> b)
> SELECT * FROM M WHERE UPPER(NNAME) LIKE UPPER('z%')
> -- PLAN (M NATURAL)
>
> 2.1.1 optimizer problem ?

I stumbled over this a while ago as well and IMHO it is a bug. So,
either use STARTING WITH, if you know that you are doing a LIKE 'a%' or
do the upper case of the string literal in your client application and
not directly in the SQL statement. This will end up with a statement
like that:

SELECT * FROM M WHERE UPPER(NNAME) LIKE 'Z%'

This will use the expression index.

I always recommend to use STARTING WITH when you could also use a LIKE
'Z%', because with STARTING WITH, even an index can be used if it is a
parametrized query.

HTH.

--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/