
[
Permalink
 « Hide
]
Sergio Hernandez added a comment  13/Sep/16 08:34 AM
Added a little code that mimic the round using the even/odd rule
The SQL standard (SQL:2011 Foundation), does not describe the round function itself, however on the subject of rounding (eg when assigning a number with larger scale to smaller scale), it says (section 4.4.2 Characteristics of numbers):
"An approximation obtained by rounding of a numeric value N for an <exact numeric type> T is a value V in T such that the absolute value of the difference between N and the numeric value of V is not greater than half the absolute value of the difference between two successive numeric values in T. If there is more than one such value V, then it is implementationdefined which one is taken." In other words: the current behavior falls within the specification given for rounding: "it is implementationdefined which one is taken". In addition to Mark's comments;
The method/logic which Sergio has outlined is what is often referred to as "Banker's Rounding", this is not the most common implementation for ROUND(), but is often available as a separate function or as an optional parameter for the round function. A quick review of MS SQL and PostgreSQL finds that neither implements ROUND using the proposed logic nor do they have a builtin function which does. Hi Mark.
Falling into the SQL standard for rounding those "unclear" 0.5 cases seems just unavoidable as it is *allways* granted, so yes, FB do it ok for SQL standard, but changing the round() function with this optional flag would be easy, make FB to fall into some other standards as S.I., ANSI, IEEE, ASTM, UNE and, last but not least, make it capable of preforming unbiased rounding. The problem with actual round() can be bigger than you spected in some cases: take a numeric field with 1 decimal place, fill it wtih 1000 random numbers let say they are prices in $ and try to compute the sum rounded to rounded dollar. You hav to ways to perform the calcs: ROUND(AVG(Field)) or AVG(ROUND(Filed)) but they will tend to differ in about +5$, not much may be, but you could had avoided it easily! What actual benefit do you find or wich issue you think you can avoid by keeping actual round() from having this optional flag? 