Perhaps the engine can detect whether we are comparing a number to a
string, and if so, rather than raise an error, just suppress any use
of an index for those fields.

--- In ib-support@y..., "Ann W. Harrison" <aharrison@i...> wrote:
> 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