Here is a fairly simple test that demonstrates the problem
Bob Murdoch was having. The reason his program appeared so
flaky is that changing the where conditions affected index
usage. His application compared a number, stored in a character
field with a leading zero, to a numeric value.

This script demonstrates the problem:

create table T1 (F1 char(4), F2 char(4));
create index T1_F1 on T1 (F1);
insert into T1 (F1, F2) values ('001', '001');
insert into T1 (F1, F2) values ('002', '002');
insert into T1 (F1, F2) values ('003', '003');
insert into T1 (F1, F2) values ('004', '004');
insert into T1 (F1, F2) values ('005', '005');
insert into T1 (F1, F2) values ('006', '006');
insert into T1 (F1, F2) values ('007', '007');
insert into T1 (F1, F2) values ('008', '008');
insert into T1 (F1, F2) values ('009', '009');

commit;
select * from t1 where f1 = 3;
select * from t1 where f2 = 3;

The first query returns no results. The second returns
one row.

The problem appears on dialect 1 and dialect 2 databases
with Firebird.

If anyone out there can help, I'd really like to know if
the problem appears in IB 6.0 (open or commercial), IB 6.5,
and IB 5.6.

Thanks


Ann