Re: Proposal for fixing numeric type-resolution issues

Again, anything to add to the TODO here?

> We've got a collection of problems that are related to the parser's
> inability to make good type-resolution choices for numeric constants.
> In some cases you get a hard error; for example "NumericVar + 4.4"
> yields
> ERROR:  Unable to identify an operator '+' for types 'numeric' and 'float8'
>         You will have to retype this query using an explicit cast
> because "4.4" is initially typed as float8 and the system can't figure
> out whether to use numeric or float8 addition.  A more subtle problem
> is that a query like "... WHERE Int2Var < 42" is unable to make use of
> an index on the int2 column: 42 is resolved as int4, so the operator
> is int24lt, which works but is not in the opclass of an int2 index.
> 
> Here is a proposal for fixing these problems.  I think we could get this
> done for 7.1 if people like it.
> 
> The basic problem is that there's not enough smarts in the type resolver
> about the interrelationships of the numeric datatypes.  All it has is
> a concept of a most-preferred type within the category of numeric types.
> (We are abusing the most-preferred-type mechanism, BTW, because both
> FLOAT8 and NUMERIC claim to be the most-preferred type in the numeric
> category!  This is in fact why the resolver can't make a choice for
> "numeric+float8".)  We need more intelligence than that.
> 
> I propose that we set up a strictly-ordered hierarchy of numeric
> datatypes, running from least preferred to most preferred:
>     int2, int4, int8, numeric, float4, float8.
> Rather than simply considering coercions to the most-preferred type,
> the type resolver should use the following rules:
> 
> 1. No value will be down-converted (eg int4 to int2) except by an
> explicit conversion.
> 
> 2. If there is not an exact matching operator, numeric values will be
> up-converted to the highest numeric datatype present among the operator
> or function's arguments.  For example, given "int2 + int8" we'd up-
> convert the int2 to int8 and apply int8 addition.
> 
> The final piece of the puzzle is that the type initially assigned to
> an undecorated numeric constant should be NUMERIC if it contains a
> decimal point or exponent, and otherwise the smallest of int2, int4,
> int8, NUMERIC that will represent it.  This is a considerable change
> from the current lexer behavior, where you get either int4 or float8.
> 
> For example, given "NumericVar + 4.4", the constant 4.4 will initially
> be assigned type NUMERIC, we will resolve the operator as numeric plus,
> and everything's fine.  Given "Float8Var + 4.4", the constant is still
> initially numeric, but will be up-converted to float8 so that float8
> addition can be used.  The end result is the same as in traditional
> Postgres: you get float8 addition.  Given "Int2Var < 42", the constant
> is initially typed as int2, since it fits, and we end up selecting
> int2lt, thereby allowing use of an int2 index.  (On the other hand,
> given "Int2Var < 100000", we'd end up using int4lt, which is correct
> to avoid overflow.)
> 
> A couple of crucial subtleties here:
> 
> 1. We are assuming that the parser or optimizer will constant-fold
> any conversion functions that are introduced.  Thus, in the
> "Float8Var + 4.4" case, the 4.4 is represented as a float8 4.4 by the
> time execution begins, so there's no performance loss.
> 
> 2. We cannot lose precision by initially representing a constant as
> numeric and later converting it to float.  Nor can we exceed NUMERIC's
> range (the default 1000-digit limit is more than the range of IEEE
> float8 data).  It would not work as well to start out by representing
> a constant as float and then converting it to numeric.
> 
> Presently, the pg_proc and pg_operator tables contain a pretty fair
> collection of cross-datatype numeric operators, such as int24lt,
> float48pl, etc.  We could perhaps leave these in, but I believe that
> it is better to remove them.  For example, if int42lt is left in place,
> then it would capture cases like "Int4Var < 42", whereas we need that
> to be translated to int4lt so that an int4 index can be used.  Removing
> these operators will eliminate some code bloat and system-catalog bloat
> to boot.
> 
> As far as I can tell, this proposal is almost compatible with the rules
> given in SQL92: in particular, SQL92 specifies that an operator having
> both "approximate numeric" (float) and "exact numeric" (int or numeric)
> inputs should deliver an approximate-numeric result.  I propose
> deviating from SQL92 in a single respect: SQL92 specifies that a
> constant containing an exponent (eg 1.2E34) is approximate numeric,
> which implies that the result of an operator using it is approximate
> even if the other operand is exact.  I believe it's better to treat
> such a constant as exact (ie, type NUMERIC) and only convert it to
> float if the other operand is float.  Without doing that, an assignment
> like
>     UPDATE tab SET NumericVar = 1.234567890123456789012345E34;
> will not work as desired because the constant will be prematurely
> coerced to float, causing precision loss.
> 
> Comments?
> 
>             regards, tom lane
> 


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026