At 07:52 PM 3/6/2002 -0500, Leyne, Sean wrote:

> > The problem is when a character field is compared to a number.
> > For a non-indexed field, Firebird converts both arguments to
> > a common datatype (in this case numeric) and performs the
> > comparison.
>
>Isn't this the heart of the problem.
>
>Why are the CHAR columns converted to numeric, instead of the numberic
>converted to a string?

Because converting a string to a number and comparing is faster
than converting a number to a string and comparing. It's also
somewhat more likely to get the right answer if the number is
floating point.

>Converting a value to the datatype of the table column seems to be the
>approach, which should always be used -- it's the approach used for
>date/time types. So, why is the reverse used for CHARs?

At the point where the conversions and comparisons are made, there's
not a trace left of the origin of the two values.

>If the query value had been converted to a string, then both the indexed
>and non-indexed would be returned the same result.

No, or rather, yes, if all index keys were converted to strings
before the index was built. But they aren't. The code that compares
index key values hasn't a clue about the original datatype of the
elements of the key - or their boundaries in the case of compound
keys.


Regards,

Ann
www.ibphoenix.com
We have answers.