BETWEEN query strangeness...

TruckStuff

I'm running a query on the following data set with the following query:

FROM   TO     BASE
10.51  11.48  9.99
11.49  27.86  19.99
27.87  36.94  29.99
36.95  47.24  39.99

$q = "select BASE from TABLE where $number BETWEEN `FROM` and `TO`";
$base = mysql_query ($q);

The problem is that this query doesn't always return a result if $number is equal to FROM BUT sometimes it does. 😕 TFM for mysql indicates that between is inclusive (i.e. 'x between y and z' == 'x >= y AND x <= z'), so it should always return a result set (as long as I pass it float with 2 decimal places). So what am I missing?

Sxooter

Are you storing these numbers in floats or numerics???

floats, by definition aren't necessarily exact numbers, while numeric(x.y) are.

TruckStuff

They are stored in the DB as float(10,2)s. They are loaded in as 2 decimal digits and tested against 2 decimal digits.

nemonoman

Ah, the inexact representation of floats problem.

Try something like:

SELECT BASE from TABLE where round($number, 2) BETWEEN round(FROM, 2) and round(TO,2) ";

Alternatively, try this:

STORE your search number in another table with the same column def as your other table (2 decimal digits ).

Then do the test against the number as stored.

Sxooter

But, adding (x,y) to a float doesn't make it an exact type, it's still a float, so it still is possible to compare two floats that LOOK like the same value but in fact, are slightly different from each other.

i.e. if you want to do an exact comparison, you need an exact type (numeric, int, decimal).

Sxooter

To expand on nemonoman's point, you may be able to cast these (are you using mysql 4.x? If not, then you can't use this trick, because 3.x doesn't have a cast operator.)

select 
  BASE 
from 
  TABLE 
where 
  $number
BETWEEN
  cast (FROM as numeric(10,2))
and 
  cast (TO as numeric(10,2))
;

TruckStuff

Isn't it funny how the power of modern computing and be a real pain in the butt sometimes? 😉

Anywho, I changed the FROM and TO to type DECIMAL (essentially character representations of numbers) and that seemed to fix it. This is thread is a good reference, however, for future problems of this nature.