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
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco