Re: Proposal for resolving casting issues

"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>> 2. Allow implicit up-coercion int2->int4->int8->numeric->float4->float8,
>> but down-coercions aren't implicit except for assignment.

> How about int2->int4->int8->numeric->float4->float8->numeric ?
> That would also allow an upward path from float8.

Uh, what? That seems logically impossible to me ... or at least it
would reintroduce exactly the problem we need to get away from: casts
between float4, float8, numeric would be considered equally good in
either direction, creating ambiguity about which operator to use.
How are you envisioning it would work exactly?

Perhaps I should clarify what I had in mind: because the parser only
considers one level of type coercion when choosing a function or
assigning to a result column, it's actually necessary to have all thirty
cast combinations between the six numeric types available in pg_cast.
My notation "int2->int4->int8->numeric->float4->float8" is intended to
imply that of the thirty, these would be marked as implicitly coercible:

int2->int4
int2->int8
int2->numeric
int2->float4
int2->float8
int4->int8
int4->numeric
int4->float4
int4->float8
int8->numeric
int8->float4
int8->float8
numeric->float4
numeric->float8
float4->float8

while the fifteen reverse coercions would be assignment-only.

If we allow any circularity then we will have pairs of types with both
cast pathways marked as implicit, which will leave the parser unable to
choose which operator to use. This is exactly why "numeric = float8"
has failed in past versions: there are two alternatives that are equally
easy to reach.

> It would fix the constants issue, yes. How about where int2col=int4col
> and it's indexability of int2col though ?

See my other response. The current scheme of using a cross-datatype
operator isn't helpful for indexing such cases anyway...

>> Also
>> we get more nearly SQL-standard behavior in expressions that combine
>> numeric with float4/float8: the preferred type will be float, which
>> accords with the spec's notions of exact numeric vs.
>> approximate numeric.

> I do not understand the standard here.
> Especially the following would seem awkward if that would switch to
> approximate:
> set numericcol = numericcol * float4col;

Well, the spec's notion is that combining an "exact" number and an
"approximate" number must yield an "approximate" result. This logic
is hard to argue with, even though in our implementation it would
seem to make more sense for numeric to be the top of the hierarchy
on range and precision grounds.

Note that if you write, say,
set numericcol = numericcol * 3.14159;
my proposal would do the "right thing" since the constant would be typed
as numeric to start with and would stay that way. To do what you want
with a float variable, it'd be necessary to write
set numericcol = numericcol * float4col::numeric;
which is sort of ugly; but no uglier than
set float4col = float4col * numericcol::float4;
which is what you'd have to write if the system preferred numeric and
you wanted the other behavior.

I too have been thinking for a long time that I didn't like following
the spec's lead on this point; but I am now starting to think that it's
not all that bad. This approach to handling constants is *much* cleaner
than what we've done in the past, or even any of the unimplemented
proposals that I can recall. The behavior you'd get with combinations
of float and numeric variables is, well, debatable; from an
implementor's point of view preferring a numeric result makes sense,
but it's much less clear that users would automatically think the same.
Given the spec's position, I am starting to think that preferring float
is the right thing to do.

BTW, I am thinking that we don't need the notion of "preferred type" at
all in the numeric category if we use this approach. I have not worked
through the details for the other type categories, but perhaps if we
adopt similar systems of one-way implicit promotions in each category,
we could retire "preferred types" altogether --- which would let us get
rid of hardwired type categories, too.

regards, tom lane