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

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,
Pennsylvania19073