Re: Proposal for resolving casting issues - Mailing list pgsql-hackers

> > 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
>
> > Have you seen my example ? If calculated in float4 the result of
> > 1.00000000000001*1000.0-1000.0 would be 0.0, no ?
>
> So?  If you are storing one input as float4, then you cannot rationally
> say that you know the result to better than 6 digits, because you don't
> know the input to better than 6 digits.  Claiming that 1000.00000000001
> is a more accurate answer for the product than 1000.0 is simply wishful
> thinking on your part: nothing to the right of the sixth digit actually
> means a darn thing, because you don't know whether the input was really
> exactly 1000, or should have been perhaps 1000.001.

I still see 1E-10 as a better answer to above calculation than your 0,
and my snapshot 9/11 does return that 1E-10.

For better understanding the test in pg:
create table atab (a decimal(30,20), b float4, c decimal(30,20), d float4);
insert into atab values (1.000000000000001,100000.0,0, 0);
update atab set c=a*b-b, d=a*b-b where 1=1;
create view av as select a*b-b, 1, b, c,d from atab;
\d av
View definition: SELECT ((atab.a * "numeric"(atab.b)) - "numeric"(atab.b)), atab.a, atab.b
, atab.c, atab.d FROM atab;

If I understood your proposal that would now change to:
View definition: SELECT (("float4"(atab.a) * atab.b) - atab.b), atab.a, atab.b
, atab.c, atab.d FROM atab;

>
> > Do they really mean, that an approximate calculation with one float4 must be
> > calculated in float4 arithmetic ? If you e.g. calculate in float8 it would still
> > be an approximate result and thus imho conform.
>
> And still the output would be illusory: if you think you'd get 16 digits
> of precision that way, then you are failing to grasp the problem.

I have not said 16 digits exact precision. I was saying, that an approximate
result calculated in numeric makes more sense, than your float4 calculated result,
and does the correct thing more often than not in the db centric cases I can think
of.

I do think I grasp the problem :-)

> >> (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?)
>
> > Probably because the approximate is more efficient in storage size,
> > or the designer knew he only wants to store 6 significant digits ?
>
> Seems an exceedingly uncompelling scenario.  The only values that could
> be expected to be stored exactly in a float4 (without very careful
> analysis) are integers of up to 6 digits; you might as well store the
> column as int4 if that's what you plan to keep in it.

You can store 6 significant digits and an exponent (iirc 10E+-38) !
e.g. 1.23456E-20 an int can't do that.

I give up now. I voiced my concern, and that is as far as my interest goes on this
actually. I still think fielding what other db's do in this area would be a good
thing before proceeding further.

Andreas