At 02:08 PM 3/4/2002 +0000, Nick Upson wrote:

>same results on IB4 even, no rows and 1 row

Thanks. I checked with the designer and he says that
every database he's ever written has that problem, so
it goes back well before V1.0.

For those who haven't followed this closely, given
that f1 is a character field with a value of '0617',
f1 = 617 is true if f1 is not indexed (or if the
index is not used) and false when the query is
evaluated through an index. The 617 part is not
significant - the problem is the leading zero in
the character string.

How should this be fixed? For me, having queries
return different answers based on the existence of
an index is not acceptable, so I would like to
find a solution.

We could raise an error when a character string is
compared to a number. That will break lots of existing
applications.

Changing the comparison in the index to make '0617'
equal 617 is possible, but ugly, and gives the wrong
answer, in my opinion. I don't have any problem with
saying that '617' equals 617.

One other idea is to change the normal comparison to
be sensitive to numbers stored in strings with leading
zeros. That's a mess because the comparison logic is
very removed from any kind of context and fixing this
case may cause problems with others. Besides, making
comparisons depend on leading zeros in numbers represented
as strings doesn't seem right either.

So, should '617' = 617?
How about '0617' = 617?
Or '0617' = 0617?

Hmmm... breaking thousands of existing applications begins
to seem like a better idea.

Regards,


Ann