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?
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))
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With