Issue Details (XML | Word | Printable)

Key: CORE-5349
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Sergio Hernandez
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

ROUND() without bias by "properly" handling xxx.5 case (round to nearest even number)

Created: 12/Sep/16 01:16 PM   Updated: 14/Sep/16 10:11 AM
Component/s: ISQL
Affects Version/s: None
Fix Version/s: None

Environment: All

QA Status: No test


 Description  « Hide
Rounding always up (or based on sign) is statically biased, as the average of a list of numbers will allways tend to increase after you round them.

You are NOT allowed to use this "always up" method it in the S.I. (International System of Units, refer to section B7.1, read it here: http://www.hcsoft.net/lab/comohacer/redondeos/SI.png), nor the ASTM standards in USA (stated on the D29 standard, a spanish explanation is here: http://www.astm.org/SNEWS/SPANISH/SPSO08/datapoints_spso08.html) or in any ISO or UNE standard, as they all refer to the S.I. for rounding.

So, in lots of technical environments, actual rounding schema is not usable.

In maths and the alike, included most SQL engines (http://www.w3schools.com/sql/sql_func_round.asp) you always use the "even rule" (ANSI/IEEE Std 754-1985 standard for floating point artihmetic): in case of ending in 0.5, always round to the nearest EVEN number. 13.5 would round to 14 and -13.5 will round to -14 too.

Will it be possible to have a flag -or an optional third parameter- so the round() funtion uses the nicer "even rounding rule, IEEE 754 standard" instead of actual "positives up, negatives down"?

UPDATE: I forged a round procedure that works like I spected.

CREATE OR ALTER PROCEDURE ROUND_EVEN (
    IMPORTE double precision,
    DECI integer )
RETURNS (
    VALOR double precision )
AS
declare variable Multi integer;
declare variable IntVal bigint;
begin
  Multi = Power(10, Deci);
  Valor = Importe * Multi;
  if ((Valor - Floor(Valor)) = 0.5) then begin
    if (Mod (Ceil(Valor), 2) = 0) then
      IntVal = Ceil(Valor);
    else
      IntVal = Floor(Valor);
  end else begin
    IntVal = round(Valor);
  end
  Valor = cast (IntVal as double precision)/Multi;
  suspend;
end

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sergio Hernandez added a comment - 13/Sep/16 08:34 AM
Added a little code that mimic the round using the even/odd rule

Mark Rotteveel added a comment - 13/Sep/16 06:03 PM - edited
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 implementation-defined which one is taken."

In other words: the current behavior falls within the specification given for rounding: "it is implementation-defined which one is taken".

Sean Leyne added a comment - 13/Sep/16 07:10 PM
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 built-in function which does.

Sergio Hernandez added a comment - 14/Sep/16 10:11 AM
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?