Re: Numeric casting rules, take two

Tom, do you want any TODO items from this?

---------------------------------------------------------------------------

Tom Lane wrote:
> I started by saying
> > * Within a category, "up" (lossless) conversions are implicit, "down"
> > (potentially lossy) conversions should be assignment-only.
> but as always the devil is in the details.
>
> After further thought, and the thread with Andreas about where we might go
> with this in 7.4, I have developed a two-stage plan for dealing with
> numeric casts. We can make some progress in 7.3 but there is more work
> that will have to be postponed. Here's my current thoughts (plan first,
> then discussion):
>
> Do for 7.3:
>
> * Set up pg_cast so that up-coercions in the series
> int2->int4->int8->numeric->float4->float8 are implicit, while
> down-coercions (the reverse direction of each of these fifteen casts)
> are marked assignment-only.
>
> * Modify make_const so that numeric literals are typed as the smallest
> type that will hold them in the series int4, int8, numeric (as opposed
> to the former behavior, which was int4, float8, numeric).
>
> * Make only float8, not numeric, be a preferred type for category NUMERIC.
>
> Do for 7.4:
>
> * Change make_const so that numeric literals are typed as the smallest
> type that will hold them in the series int2, int4, int8, numeric (ie,
> add int2 to the possible set of initial datatypes for constants).
>
> * Remove most cross-datatype operators (int2+int4, etc), expecting such
> operations to be handled by an implicit cast and a single-datatype
> operator instead. This is necessary for comparison operators, because
> we want operations like "int4var = 42" to be coerced to int4-only
> operations so that they are indexable. It's optional for operators that
> are never associated with indexes (like +), but I'm inclined to reduce
> the code bulk and size of pg_proc (and pg_operator) by getting rid of as
> much as we can.
>
> * Fix planner to cope with merge and hash joins wherein the arguments
> aren't plain Var nodes (must cope with Var + type promotion, and might
> as well just take any expression).
>
> * Develop similar promotion hierarchies for the other type categories.
> See if we can't retire the notion of "preferred type" entirely.
>
> Discussion:
>
> The main point of the 7.3 changes is to create a consistent promotion scheme
> for the numeric hierarchy. By twiddling make_const, we can improve the
> behavior for large integers and float-format constants: these will be
> typed as int8 or numeric and then if necessary up-converted to numeric,
> float4, or float8. It happens that there are no cross-datatype operators
> at present between int8 and numeric/float4/float8 nor between numeric and
> float4/float8, so we will get the desired up-conversion and not selection
> of a cross-datatype operator when such a constant is used with a numeric
> or float variable. In the existing code, an integer too large for int4
> (but not too large for int8) would be initially typed as float8, thus
> forcing us to allow float8->int8 as an implicit coercion to ensure
> reasonable behavior for int8 constants. So we must introduce int8 as
> an allowed initial type for constants if we want to remove float8->int8
> as an implicit coercion. But we can get rid of float8 as an initial type,
> which simplifies matters.
>
> With these changes we can expect reasonable behavior for cases like
> "where numericvar = float-style-constant". The behavior will not get
> better for cases involving int2 or int8 variables compared to int-size
> constants, but it won't get worse either. These changes will also bring
> us into line with the SQL spec concerning mixed float/numeric operations
> (the result should be approximate, ie float).
>
> With the additional changes for 7.4 we can expect to finally fix the
> behavior for int2 and int8 variables as well: cases like "where int2var =
> 42" will be indexable without having to explicitly cast the constant.
>
>
> Comments?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073