Re: casts and conversions

Tom, Chris,

> What we need is some logic that decides on a common datatype to
> promote
> the two inputs to and then apply a single-data-type operator. The
> mechanics are there to do this, what we haven't got is the rule that
> allows a unique choice to be made when there are several
> possibilities.
> For example, in your float8 * numeric case, the system hasn't got a
> way
> to decide between using float8 multiply or numeric multiply, although
> it
> can do either one if you coax it by supplying a cast. Interestingly,
> it can do all the other cases, such as int4 * float8, just fine ---
> the
> problem is that both float8 and numeric are marked as "preferred
> types",
> leaving the poor thing with no way to make a choice. The real issue
> here is that the "preferred type" heuristic doesn't encode enough
> knowledge to deal with all the numeric datatypes. We need a more
> general approach.

And, just to throw a banana peel onto the sidewalk of argument, there is
even a reason to keep things the way they are.

If I:
numeric * float8

And the database automatically decides that:

numeric * float8 = float8

This can get me into trouble thanks to the .9999999999 's if I'm doing
currency calculations,

Likewise, if the database decides that:

numeric * float8 = numeric

This can get me into trouble due to rounding if I'm doing scientific
calculations.

Thus there's a good reason for the database to be indecisive about
float/numeric decisions, and good reason for you to explicitly CAST.
Or, as any VB programmer can tell you, sometimes implicit casts give you
just enough rope to hang yourself.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco