Hi Bob,

I am not certain of the answer to your problem (its not something I've
played with before now).

Try compiling the SearchingLinks app with the UPPER( LAST_NAME )
changed to just LAST_NAME. If my system (Delphi5, FB RC2 on Win2KPro)
I get similar symptoms to what you describe. And yet I dont see the
problem with FIRST_NAME.

In my employee.gdb I had an index for
LAST_NAME, FIRST_NAME
(but not vise versa).

When I created a FIRST_NAME,LAST_NAME index I started to get the same
symptoms when searching via first_name - BUT ONLY when doing it
manually, when doing it inside the SearchingLinks app I did not see
the problem but I noticed that for some reason I it was not using the
new first_name index in the plan (it was using natural).

So it is starting to look to me like there is a problem/difference
between using STARTING WITH on an indexed field versus STARTING WITH
on a non-indexed field. You can avoid the issue by using something
like UPPER( field_name ) or some UDF to force IB/FB to not use an
index - but then you dont get the performance advantages.


So then I decided to try and simplify but it seems that IB/FB is too
smart...

A query like this...
SELECT *
FROM EMPLOYEE
WHERE FIRST_NAME STARTING WITH ''
results in a plan "natural" and the result is all records.

A query like this
SELECT *
FROM EMPLOYEE
WHERE FIRST_NAME STARTING WITH :A
results in a plan on my first_name index and setting :A to a blank
string results in no records - while the index exists. Delete the
index and you go back to plan natural and you get all the records.


I suggest this needs testing under the latest FB release and reporting
to the firebird developers. Something is wrong - the inconsistency
should not exist. Either STARTING WITH '' gives all records even when
'' is a parameter with that value or neither does, regardless of
whether an index exists or not.


--
Geoff Worboys
Telesis Computing