Numeric casting rules, take two - Mailing list pgsql-hackers

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