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

Have you seen my example ? If calculated in float4 the result of
1.00000000000001*1000.0-1000.0 would be 0.0, no ? 

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

Does it actually say how approximate the result needs to be, or is it simply 
approximate by nature that one part was only approximate ?
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.

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

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

It was only an example of how someone else does it and was why I asked what 
other db's do. I would e.g. suspect Oracle does it similarily.
Please, someone check another db !

Andreas

Thu, 19 Sep 2002 10:40:46 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Proposal for resolving casting issues 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> 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.

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

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

			regards, tom lane

Thu, 19 Sep 2002 09:53:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 
Will the new casting stuff address this kind of annoyance?

usa=# select average(octet_length(val)) from users_sessions;
ERROR:  Function 'average(int4)' does not exist
        Unable to identify a function that satisfies the given argument
types
        You may need to add explicit typecasts

Chris


Fri, 20 Sep 2002 10:02:47 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Proposal for resolving casting issues 
Doh - I'm stupid.  Ignore my question :)

Helps if you spell 'average' as 'avg' :)

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher
> Kings-Lynne
> Sent: Friday, 20 September 2002 10:03 AM
> To: Tom Lane; Zeugswetter Andreas SB SD
> Cc: Bruce Momjian; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Proposal for resolving casting issues 
> 
> 
> Will the new casting stuff address this kind of annoyance?
> 
> usa=# select average(octet_length(val)) from users_sessions;
> ERROR:  Function 'average(int4)' does not exist
>         Unable to identify a function that satisfies the given argument
> types
>         You may need to add explicit typecasts
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 


Fri, 20 Sep 2002 10:40:31 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Proposal for resolving casting issues 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Will the new casting stuff address this kind of annoyance?
> usa=# select average(octet_length(val)) from users_sessions;
> ERROR:  Function 'average(int4)' does not exist

regression=# select * from pg_proc where proname = 'average';
 proname | pronamespace | proowner | prolang | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proargtypes | prosrc | probin | proacl
---------+--------------+----------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+-------------+--------+--------+--------
(0 rows)


No, I think you'll get the same error ...

			regards, tom lane

Thu, 19 Sep 2002 22:41:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Proposal for resolving casting issues 