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

Thu, 19 Sep 2002 16:57:30 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Proposal for resolving casting issues 
On Thu, Sep 19, 2002 at 04:57:30PM +0200, Zeugswetter Andreas SB SD wrote:
> 
> > 
> > > 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.

Well, then you'd be wrong. Numerical analysis says you _can't_ get more
information out than went in to the _least_ precise part of a calculation.
What your suggesting is the equivalent of wanting to put up a shelf, so
you estimate the length of the wall by eyeballing it, then measure the
wood for the shelf with a micrometer, to be sure it fits exactly right.

We teach this in intro science classes all the time: if you calculate with
3.14 as an approximation to pi, you better not report the circumference
of a circle as 2.45678932 cm, I'll take off points!

> 
> I do think I grasp the problem :-)

Hmm, I'm not so sure. ;-)

> 
> 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.

Ah, sorry to drag this on, then. But this is one of those clear cases
were we must fo the right thing, not follow the crowd. PostgreSQL gets
used by a lot of scientific projects (Have you noticed all the big
bioinformatics databases being mentioned on the lists?). Partly because
we're always underfunded, partly because we're academics who like to
have the code. If we start getting basic maths wrong, that'll be a huge
balck eye for the project.

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

Thu, 19 Sep 2002 10:30:51 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Proposal for resolving casting issues
On Thu, Sep 19, 2002 at 10:30:51AM -0500, Ross J. Reedstrom wrote:
> 
> Ah, sorry to drag this on, then. But this is one of those clear cases
> were we must fo the right thing, not follow the crowd. PostgreSQL gets
               do
> used by a lot of scientific projects (Have you noticed all the big
> bioinformatics databases being mentioned on the lists?). Partly because
> we're always underfunded, partly because we're academics who like to
  ^^(scientific projects)                  ^^
> have the code. If we start getting basic maths wrong, that'll be a huge
                    ^^(PostgreSQL)
> balck eye for the project.
  black

Clearly, it's time for an early lunch for me. I need sugar for my brain.

Ross


Thu, 19 Sep 2002 10:42:23 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Proposal for resolving casting issues