Re: AW: type conversion discussion - Mailing list pgsql-hackers

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