Re: numeric precision when raising one numeric to another.

On Fri, May 20, 2005 at 01:03:08PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > Why are we allowing implicit casts from numeric to floating point?
>
> Because the SQL spec requires it.
>
> 2) If the data type of either operand of a dyadic arithmetic op-
> erator is approximate numeric, then the data type of the re-
> sult is approximate numeric.
>
> It doesn't say to throw an error for mixed-type arithmetic.
>
> Now it also says
>
> 1) If the data type of both operands of a dyadic arithmetic opera-
> tor is exact numeric, then the data type of the result is exact
> numeric, ...

But isn't NUMERIC exact numeric and not approximate?

> which you could take as requiring us to provide numeric equivalents of
> every floating-point operator, but I don't find that argument very
> convincing for operations that are inherently not going to give exact
> results. The spec demands exact results from addition, subtraction,
> and multiplication, but as soon as you get to division they punt; let
> alone transcendental functions.

ISTM what's more important than be exact is respecting precision. If I'm
remembering this correctly from high school, multiplying two numbers
each having 10 significant digits means you then have 20 significant
digits, so we should at least respect that. Which means
numeric(500)^numeric(500) should give an exact numeric(1000), which I
don't think is a given when casting to a double.

I'm not sure how this changes if you're using a fractional exponent. But
it seems like a pretty serious issue if you're doing financial
calculations and those are sometimes done in floating point under the
covers.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"