MySQL stored function malfunctioning, but working as standalone query

Question

I have a basic query with some calculations for distance between two coordinates, based on this discussion. It seems to run fine as standalone query, but when I try to run it as a stored function, it returns weird results (usually null or 99.9999...).

The standalone query is:

SELECT lat, lng, 

  ( 6371 * acos( cos( radians(32.113277) ) * cos( radians( lat ) ) 
   * cos( radians(lng) - radians(34.799259)) + sin(radians(32.113277)) 
   * sin( radians(lat)))) AS distance_km,

   ( 6371000 * acos( cos( radians(32.113277) ) * cos( radians( lat ) ) 
   * cos( radians(lng) - radians(34.799259)) + sin(radians(32.113277)) 
   * sin( radians(lat)))) AS distance_meters

FROM (
    SELECT 32.113391 as lat , 34.801571 as lng
) a

^ This runs fine. Then I tried to change it into a stored function:

create function distance_meters(lat1 decimal(8,6), lng1 decimal(8,6), lat2 decimal(8,6), lng2 decimal(8,6) )
returns decimal(8,6) DETERMINISTIC
    return    ( 6371000 * acos( cos( radians(lat1) ) * cos( radians( lat2 ) ) 
   * cos( radians(lng2) - radians(lng1)) + sin(radians(lat2)) 
   * sin( radians(lat2))))

and executing it like this:

select distance_meters(32.113277, 34.799259, 32.113391, 34.801571)

resulted in 99.999999

I even tried to put all the input values (for the coordinates) as part of the stored function's code, like this:

create function distance_meters()
returns decimal(8,6) DETERMINISTIC
       return ( 6371000 * acos( cos( radians(32.113277) ) * cos( radians( 32.113391 ) ) 
   * cos( radians(34.801571) - radians(34.799259)) + sin(radians(32.113277)) 
   * sin( radians(32.113391))));

but it didn't change the problematic result at all!

PS: of course I dropped the function every time before re-creating it. Also tried to change the return type from decimal to numeric.

EDIT: as suggested in the comments, I've also tried this:

DELIMITER $
create function distance_meters()
returns decimal(8,6) DETERMINISTIC
begin
declare var_name decimal(8,6);
set var_name = ( 6371000 * acos( cos( radians(32.113277) ) * cos( radians( 32.113391 ) ) 
   * cos( radians(34.801571) - radians(34.799259)) + sin(radians(32.113277)) 
   * sin( radians(32.113391))));
   return var_name;
end$
DELIMITER ;
select distance_meters()

What am I missing here?

It`s a shot in the dark,but use a BEGIN END section,in it DECLARE a decimal variable, SET it to the query result and return that.Any change? — Mihai, Jan 13 '17 at 16:42
@Mihai thanks, but unfortunately that didn't help - see the updated (bottom part) version of the question — Yoav Feuerstein, Jan 13 '17 at 16:56
What happens if you increase the decimal precision. Can you try (18,6)? — Alden W., Jan 13 '17 at 18:41
@AldenW. that seems to solve it, thank you so much! Can you post it as an answer so I could accept it? I should have noticed that I need 3+6 digits for the result's value, which is (9,6) instead of (8,6). silly me — Yoav Feuerstein, Jan 13 '17 at 21:49
@YoavFeuerstein Ah, great. I've updated my answer to include the bit about the decimal precision. — Alden W., Jan 13 '17 at 23:07

Alden W. · Accepted Answer · 2017-01-13T23:07:11.770

1

It seems there were two parts to the solution. One part was a typo in the function, where lat1 and lat2 were switched.

return ( 6371000 * acos( cos( radians(lat1) ) * cos( radians( lat2 ) ) * cos( radians(lng2) - radians(lng1)) + sin(radians(lat1)) * sin( radians(lat2))));

The other bit was increase the precision of the decimal column enough. I didn't test to see what the minimum precision needed was, but when I tried with DECIMAL(18,6) it seemed to work.

edited Jan 13 '17 at 23:07

answered Jan 13 '17 at 18:54

Alden W.

1,362
12
19

You're right, I probably did mix-up that field! But that doesn't solve it - your version still returns the same wrong answer. The thing is, as I said in the original question, even when I ran it with hard-coded values, it would still return different result when it's a stored function and when it's a simple query. – Yoav Feuerstein Jan 13 '17 at 21:45
comment after the edit: (9,6) was enough for this specific case, but now I got the point :) Thanks! – Yoav Feuerstein Jan 14 '17 at 06:57

MySQL stored function malfunctioning, but working as standalone query

1 Answers1