7

I was optimizing a query by precalculating some trigonometry-funnctions for the fields in a table, when I stumbled on this:

SELECT 6371 * acos( 0.793521289617132 * 0.793521289617132 + 0.608542490648241 * 0.608542490648241 * cos( 0.235244203230056 - 0.235244203230056 ) )

returns null

the query with non-precalculated values:

SELECT 6371 * acos( sin( radians( 52.51581 ) ) * sin( radians( 52.51581 ) ) + cos( radians( 52.51581 ) ) * cos( radians( g.lat ) ) * cos( radians( 13.4785 ) - radians( 13.4785 ) ) )

returns 0 (which is the correct result)

Is this a bug? or is it expected?

Dexter
  • 3,072
  • 5
  • 31
  • 32

2 Answers2

6

You have some rounding errors in your query which result from the float arithmetic.

If you try this query

SELECT -1 + ( 0.793521289617132 * 0.793521289617132 + 0.608542490648241 * 0.608542490648241 * cos( 0.235244203230056 - 0.235244203230056 ) )

you'll get 6.66133814775094e-016. So what you're trying to do is

SELECT 6371 * acos( 1 + 6.66133814775094e-016 )

which obviously won't work because acos is only defined on [-1,1] domain.

I don't know what exactly you're trying to accomplish but you have to rework you calculations, e.g. check if the parameter for acos is out of bounds and then set the value accordingly, maybe like this:

ACOS( IF(val BETWEEN -1 AND 1, val, SIGN(val))
Czechnology
  • 14,832
  • 10
  • 62
  • 88
4

ACOS returns NULL if X is not in the range -1 to 1

may be in the case with non-precalculated values, mysql is doing some simplification before applying ACOS

manji
  • 47,442
  • 5
  • 96
  • 103