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

Tue, 17 Sep 2002 14:57:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

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

Wed, 18 Sep 2002 01:01:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Numeric casting rules, take two
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom, do you want any TODO items from this?

I think we have plenty already on this general subject, no?  But you
could stick this whole thread into TODO.detail/typeconv if you like.
(It's interesting to compare these ideas to where we were 2 years
ago...)

			regards, tom lane

Wed, 18 Sep 2002 01:27:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Numeric casting rules, take two 