Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Bug? (Trigonometry)

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?

like image 466
Dexter Avatar asked Mar 23 '11 21:03

Dexter


2 Answers

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))
like image 112
Czechnology Avatar answered Oct 22 '22 19:10

Czechnology


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

like image 31
manji Avatar answered Oct 22 '22 19:10

manji