Re: rounding problems

On May 12, 2008, at 6:37 PM, Justin wrote:
> lets take this
>    select (9/10), (9/10)::numeric, (9::numeric/10::numeric), (9./10),
> (9*.1)
>
> With the given select statement i  expected the results all to be
> same,
> especially sense it cast 4 of the 5 to numeric either with explicit
> cast
> or by containing a decimal.  Instead postgresql cast the  first 2
> calculations to integer, it then uses integer math so the result is 0.

Putting a decimal on a string of digits is the standard way to
specify that it's numeric rather than integer; see 4.1.2.4. Numeric
Constants:

    http://www.postgresql.org/docs/8.3/interactive/sql-syntax-
lexical.html#AEN1276>

In other words, 9. is equivalent to 9::numeric, though the latter
involves an operation on an integer.

If a calculation contains a numeric value, any integers involved will
be cast to a numeric value first, and then the calculation will
proceed numerically.

9/10 => 0                    (a purely integer calculation, division truncates the
fractional part)
(9/10)::numeric => 0::numeric => 0.          (using parentheses forces the
integer calculation to occur *before* the cast)
9::numeric/10::numeric => 9./10. => 0.9        (using one or two casts
forces a numeric calculation)
9./10 => 9./10. => 0.9                (specifying a numeric value forces the
integer to be cast to numeric)

> To Add further conversion to my small brain there is a specific type
> cast to the second calculation but it still returned 0.  Not what i
> would have expected.  After thinking about it for say 10 seconds, i
> see
> that Postgresql is following the order of operation in the 2nd
> calculation where it does integer math then cast the results to
> numeric.
>
> I made the incorrect assumption Postgresql would have casted all the
> arguments to numeric then done the math.

Not when you change the order of evaluation by using parentheses. See
the precedence table in 4.1.6. Lexical Precedence:

    http://www.postgresql.org/docs/8.3/interactive/sql-syntax-
lexical.html#SQL-PRECEDENCE

> After thinking this through
> for a short bit i see why postgresql is casting the arguments to
> integer
> type as numeric/floating point math can be a pretty heavy hit
> performance wise.
>
> So this prompts the question how does postgresql decide what types to
> cast arguments to.

It starts with operator precedence to determine the order of
operation, and then for each operator it decides how it will cast
arguments for the "best" results.

-- Andy