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
