
> > Note that if you write, say,
> > 	set numericcol = numericcol * 3.14159;
> > my proposal would do the "right thing" since the constant would be typed
> > as numeric to start with and would stay that way.  To do what you want
> > with a float variable, it'd be necessary to write
> > 	set numericcol = numericcol * float4col::numeric;

Yes, that is the case where the new behavior would imho not be good (but you 
say spec compliant). I loose precision even though there is room to hold it.

> > which is sort of ugly; but no uglier than
> > 	set float4col = float4col * numericcol::float4;

Informix does the calculations in numeric, and then converts the result
if no casts are supplied (would do set float4col = float4(float4col::numeric * numericcol)).

Would be interesting what others do ?

Test script:
create table atab (a decimal(30), b smallfloat, c decimal(30), d smallfloat);
insert into atab values (1.000000000000001,100000.0,0, 0);
update atab set c=a*b-b, d=a*b-b where 1=1;
select a*b-b, b, c,d from atab;

    (expression)              b                c              d

           1e-10 100000.0000000            1e-10          1e-10

I hope this test is ok ?
It still seems to me, that numeric should be the preferred type, and not float8.

Andreas

Wed, 18 Sep 2002 18:01:05 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Proposal for resolving casting issues
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> Note that if you write, say,
> set numericcol = numericcol * 3.14159;
> my proposal would do the "right thing" since the constant would be typed
> as numeric to start with and would stay that way.  To do what you want
> with a float variable, it'd be necessary to write
> set numericcol = numericcol * float4col::numeric;

> Yes, that is the case where the new behavior would imho not be good (but you 
> say spec compliant). I loose precision even though there is room to hold it.

Lose what precision?  It seems silly to imagine that the product of
a numeric and a float4 is good to more digits than there are in the
float4.  This is exactly the spec's point: combining an exact and an
approximate input will give you an approximate result.

(Unless of course the value in the float4 happens to be exact, eg,
an integer of not very many digits.  But if you are relying on that
to be true, why aren't you using an exact format for storing it?)

> Informix does the calculations in numeric, and then converts the result
> if no casts are supplied (would do set float4col = float4(float4col::numeric * numericcol)).

I am not sure what the argument is for following Informix's lead rather
than the standard's lead; especially when Informix evidently doesn't
understand numerical analysis ;-)

			regards, tom lane

Wed, 18 Sep 2002 12:26:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 