Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.4
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:Centos 6.3
Description
Something is not working correctly on handling decimals. I am still digging to the source, but this example returns NULL and should return 0.
SELECT
(ACOS(SIN(RADIANS(26.197506)) * SIN(RADIANS(26.197506)) + COS(RADIANS(26.197506)) * COS(RADIANS(26.197506)) * COS(RADIANS(-80.107953) - RADIANS(-80.107953)))*3959) AS `Distance`
However, changing the numbers to one that would not round up if rounded as below returns the proper 0
SELECT
(ACOS(SIN(RADIANS(26.197504)) * SIN(RADIANS(26.197504)) + COS(RADIANS(26.197504)) * COS(RADIANS(26.197504)) * COS(RADIANS(-80.107953) - RADIANS(-80.107953)))*3959) AS `Distance`
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
alternatively, of course, you can simply replace it with 1 and avoid the problem altogether 
The numbers are dynamically inserted when the query is generated to measure distance between two points. This example was when the primary location matches the secondary location. I narrowed down the full query to this point to discover the problem. I have solved the problem in the database by changing the type on the column to FLOAT from DECIMAL, which is the opposite as I would have expected.
However, this example shows that the floating point directly does not work when comparing two like calculations. I see many work arounds, but I think it should still calculate both sides equally since they are in fact equal.
i know it's not mariadb/mysql problem
maybe you should consider the round of 26.197506
if you use 26.197505, the result is 0
but i'm not sure if the problem is math, or round of sin/cos
i'm not sure if wolframalpha is a good source of test/result, i'm assuming that it is (i use it a lot at least)
the result is 0 with 26.197506
in other words, it's a round problem...
maybe some considerations should be done... or a more "precise" lib should be used...
don't know what is best (new functions / rewrite of current functions from float to decimal? or float to double?), since i'm not using mariadb for precise math with trigonometric functions
It's not related to decimals, but to floats. (DECIMAL is the exact numeric type, FLOAT is approximate).
ACOS(), SIN(), COS() — they use floating-point and implicitly convert their arguments to FLOAT.
You see NULL because
MariaDB [test]> SELECT SIN(RADIANS(26.197506)) * SIN(RADIANS(26.197506)) + COS(RADIANS(26.197506)) * COS(RADIANS(26.197506)) as `sin^2+cos^2`;
--------------------
--------------------
--------------------
1 row in set (0.00 sec)
Which is mathematically incorrect, but calculations with floating point numbers are approximate and that result above is not very surprising.
As a workaround, you can wrap your sin^2+cos^2 part of the formula in LEAST(1, ...).