I tried setting up a Splunk alert to check for inconsistencies between a rounded total and a raw total, but the alert has been giving false positives at random intervals because Splunk's
round function doesn't seem to applying a consistent half-rounding rule. A clear example of this is if the following query is run:
* | eval rounded_amount_22=round(22.275,2) | eval rounded_amount_21=round(21.275,2) | eval rounded_amount_20=round(20.275,2) | eval rounded_amount_19=round(19.275,2) | eval rounded_amount_18=round(18.275,2) | eval rounded_amount_17=round(17.275,2) | eval rounded_amount_16=round(16.275,2) | eval rounded_amount_15=round(15.275,2) | eval rounded_amount_14=round(14.275,2) | eval rounded_amount_13=round(13.275,2) | eval rounded_amount_12=round(12.275,2) | eval rounded_amount_11=round(11.275,2) | eval rounded_amount_10=round(10.275,2) | head 1 | table rounded_amount_10, rounded_amount_11, rounded_amount_12, rounded_amount_13, rounded_amount_14, rounded_amount_15, rounded_amount_16, rounded_amount_17, rounded_amount_18, rounded_amount_19, rounded_amount_20, rounded_amount_21, rounded_amount_22
If Splunk was using half-up, half-down, or banker's rounding, I'd expect to see consistency in the results, but I just don't see a pattern in what I get back:
rounded_amount_10: 10.28 rounded_amount_11: 11.28 rounded_amount_12: 12.28 rounded_amount_13: 13.28 rounded_amount_14: 14.28 rounded_amount_15: 15.28 rounded_amount_16: 16.27 rounded_amount_17: 17.27 rounded_amount_18: 18.27 rounded_amount_19: 19.27 rounded_amount_20: 20.27 rounded_amount_21: 21.28 rounded_amount_22: 22.28
In particular, every rounding scheme I know of should treat
20.275 the same - but as shown in the above list, Splunk is rounding up to
10.28 and down to
20.27. I just don't see a pattern in it.
I've also tried wrapping the
round function in
exact, but that doesn't change the results.
What's going on here? Is it possible for this query to return consistent half-rounding results?
Just tested something similar in Python and got similar results
x = round(15.275, 2)
y = round(17.275, 2)
Is this down to Floating point inaccuracy?
Playing around with this some more the following works
| makeresults | eval rounded_amount_22=round(22.275*1000,-1)/1000 | eval rounded_amount_21=round(21.275*1000,-1)/1000 | eval rounded_amount_20=round(20.275*1000,-1)/1000 | eval rounded_amount_19=round(19.275*1000,-1)/1000 | eval rounded_amount_18=round(18.275*1000,-1)/1000 | eval rounded_amount_17=round(17.275*1000,-1)/1000 | eval rounded_amount_16=round(16.275*1000,-1)/1000 | eval rounded_amount_15=round(15.275*1000,-1)/1000 | eval rounded_amount_14=round(14.275*1000,-1)/1000 | eval rounded_amount_13=round(13.275*1000,-1)/1000 | eval rounded_amount_12=round(12.275*1000,-1)/1000 | eval rounded_amount_11=round(11.275*1000,-1)/1000 | eval rounded_amount_10=round(10.275*1000,-1)/1000
The challenge here I think is the way computers hold floating numbers. http://effbot.org/pyfaq/why-are-floating-point-calculations-so-inaccurate.htm gives some further background to the problem.
The above scales up the number to a whole number performs the round then reduces it back to a float.
Good explanation of the problem and a clear workaround based on that. I had figured this would probably be related to a floating-points somehow, but since PHP doesn't have this problem (but definitely is prone to floating-point number inaccuracies) I thought there might be another explanation... super interesting that Python gives the same results.
Regardless, using round with a negative value hadn't occurred to me, and provides a clear and relatively readable method of ensuring reliable results. Great stuff!
Here's a workaround... just add a tiny smidge to the thing being rounded.
| makeresults | eval smidge=0.000000000001 | eval rounded_amount_16R=round(16.275,2) | eval rounded_amount_15R=round(15.275,2) | eval rounded_amount_16S=round(16.275+smidge,2) | eval rounded_amount_15S=round(15.275+smidge,2) | table smidge rounded_amount_*
If you want to see output in table form, you can do this...
| untable smidge fieldname fieldvalue | rex field=fieldname "(?<test>rounded_amount_\d\d)(?<method>\w)" | chart first(fieldvalue) by test method