Thread: Re: AW: type conversion discussion

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> I think the topmost numeric-type needs to be numeric, since it is the 
> only type with arbitrary scale and precision.
> Thus I think we would need:
> int2,int4,int8,float4,float8,numeric

No, this is wrong because it contradicts SQL92: float + numeric must
yield float, not numeric.

> But the above is still not correct, in the sence that e.g. int8 cannot be
> converted to float4
> without loss. In that sense I don't think one upward promotion info is
> sufficient.

An important component of the second proposal is that the actual data
conversion is done in one step if possible.  We will *consider* using
float4 before we consider float8, but if we end up using float8 then
we try to do a direct whatever-to-float8 conversion.  So as long as the
right set of conversion operators are available, there's no unnecessary
precision loss.
        regards, tom lane

I wrote:
>> But the above is still not correct, in the sence that e.g. int8
>> cannot be converted to float4 without loss. In that sense I don't
>> think one upward promotion info is sufficient.

> An important component of the second proposal is that the actual data
> conversion is done in one step if possible.  We will *consider* using
> float4 before we consider float8, but if we end up using float8 then
> we try to do a direct whatever-to-float8 conversion.  So as long as the
> right set of conversion operators are available, there's no unnecessary
> precision loss.

After further thought I see that there is still a risk here, which
depends on the presence or absence of specific functions.  Suppose that
we offer cos(float4) and cos(float8), but not cos(numeric).  With the
proposal as given, the system would execute cos(numericVar) as
cos(float4(numericVar)) which is probably not the most desirable
choice --- but that would be the "least promoted" alternative.

Considering this example, I think that the proposed numeric hierarchy
needs to be altered.  Instead of
int2 -> int4 -> int8 -> numeric -> float4 -> float8

perhaps we want
int2 -> int4 -> int8 -> numeric -> float8float4 -> float8

That is, float4 promotes to float8 but nothing else promotes to float4.
This still satisfies the SQL92 rule that mixed exact/inexact
computations yield inexact results --- but those results will always be
done in float8 now, never in float4.  The only way to get a float4
computation is to start from float4 variables or use explicit casts.

That's still not entirely satisfactory because simple examples like
WHERE float4var < 4.4;

won't be done the way we want: the constant will promote to float8
and then you'll get float4var::float8 < 4.4::float8 which is not
able to use a float4 index.

A sneaky way around that is to make the hierarchy
int2 -> int4 -> int8 -> numeric -> float8 -> float4

which is nonintuitive as hell, but would make mixed exact/float8
calculations do the right thing.  But a mixed float8/float4
computation would be done in float4 which is not so desirable.

My inclination at this point is that we want the auto promotion
hierarchy to look like
int2 -> int4 -> int8 -> numeric -> float8float4 -> float8

but perhaps to use a different method for assigning types to numeric
literals, such that a literal can be coerced to float4 if there are
other float4s present, even though we wouldn't do that for nonliterals.
(This could maybe be done by initially assigning literals an
UNKNOWNNUMERIC data type, which then gets resolved to a specific type,
much like we do for string literals.)  A tad ugly, but I'm beginning to
doubt we can get *all* the behaviors we want without any special cases.
        regards, tom lane

Tom Lane writes:

> perhaps we want
> 
>     int2 -> int4 -> int8 -> numeric -> float8
>     float4 -> float8

In a parallel email you mentioned that your promotion tree idea will give
the system well-understood (single) inheritance semantics, with which I
agree 100%. But that is only true if the upward conversion always works,
which it won't because not every numeric "is-a" float8, and strictly
speaking, neither is int8. This is unclean at best, but might even cause
genuine failures if some promotion metric decided on a float8 function
over a numeric function because it would generate the "least casting" on
the other function attributes.

So it would have to be more like this
int2 -> int4 -> int8 -> numericfloat4 -> float8 -> numeric

This tree is "correct" in the above sense but has a number of obvious
problems.

float[x] + numeric would now yield numeric. The solution is making an
explicit float8+numeric function. Okay, so at the end it's actually more
like 8 functions, but that's a price I'd be willing to pay. (Perhaps the
commutator mechanism could be extended to cover different types as well.)

Incidentally, this would also enable some cases to work that wouldn't now,
e.g. if N is a numeric outside the range of float8 and F is some float8,
then N - F would currently fail, but it need not, depending on how it's
implemented.

The other problem is that integers would never implicitly be promoted to
floats. This is sensible behaviour from a numerical analysis point of view
but probably not acceptable for many. However, given that there is
numeric, any int/float operations would be promoted to numeric/numeric,
which is in any case the right thing to do. The only thing is to provide
numeric functions.


The alternative is to use a non-tree lattice for type promotion
      - float4 -- float8 -     /         /          \
int2 --- int4 ---- int8 ----- numeric

but that would introduce a world of problems which we probably best avoid
(as long as possible).


> That's still not entirely satisfactory because simple examples like
> 
>     WHERE float4var < 4.4;
> 
> won't be done the way we want: the constant will promote to float8
> and then you'll get float4var::float8 < 4.4::float8 which is not
> able to use a float4 index.

Could this do it?
unknownnumeric -> float4 -> float8 -> numeric

(Assuming `unknownnumeric' only represents literals with decimal points.
Others should probably be the "best fit" integer type.)


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut <peter_e@gmx.net> writes:
> So it would have to be more like this
>     int2 -> int4 -> int8 -> numeric
>     float4 -> float8 -> numeric
> This tree is "correct" in the above sense but has a number of obvious
> problems.

The most fundamental of which is that it violates SQL92: combining
exact and approximate numerics is supposed to yield approximate
(ie, float), not exact.

> float[x] + numeric would now yield numeric. The solution is making an
> explicit float8+numeric function. Okay, so at the end it's actually more
> like 8 functions, but that's a price I'd be willing to pay. (Perhaps the
> commutator mechanism could be extended to cover different types as well.)

Make that 8 functions for each and every one of the numeric operators.
I don't think that's reasonable... especially since those operators
cannot cause the overflow problem to go away.  (The SQL guys probably
did not foresee people implementing NUMERIC with wider range than FLOAT
;-) ... but the fact that we did so doesn't give us license to ignore
that aspect of the spec ...)

> numeric, any int/float operations would be promoted to numeric/numeric,
> which is in any case the right thing to do.

No it isn't.  See above.
        regards, tom lane

Tom Lane writes:

> (The SQL guys probably did not foresee people implementing NUMERIC
> with wider range than FLOAT ;-) ... but the fact that we did so
> doesn't give us license to ignore that aspect of the spec ...)

I think that must have been it, why else would they (implicitly) rank
floats above numerics. If we submit to that notion, then I agree with the
promotion tree you suggested.

The problem remains that upward casting will not be guaranteed to work all
the time, which is something that needs to be addressed; in potentially
unpretty ways, because not every casting decision is necessarily a linear
ladder-climb, it might be affected by other casting decisions going on in
parallel. (The workaround here could be to convert numerics that are wider
than floats to `infinity' :-)


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut <peter_e@gmx.net> writes:
> (The workaround here could be to convert numerics that are wider
> than floats to `infinity' :-)

Hmm, that's actually not a bad idea.  Infinity is a pretty portable
notion these days, what with nearly everyone toeing the IEEE float
line ... so we could have numeric->float generate a NaN if possible
and only resort to an elog() on machines without NaN.

OTOH, no mathematician will accept the notion that 1e1000 is the
same as infinity ;-).  Mathematical purity would probably favor
the elog.

Comments anyone?
        regards, tom lane