Re: Sum aggregate calculation for single precsion real

On Fri, Feb 17, 2017 at 6:51 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On 15 February 2017 at 12:52, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Personally, I find it somewhere in the middle: I think the way it
>> works now is reasonable, and I think what he wants would have been
>> reasonable as well. However, I find it hard to believe it would be
>> worth changing now on backward compatibility grounds.
>
> Honestly I was quite surprised to discover that sum(float4) worked
> this way because I assumed it followed the same pattern as integers.
>
> But I wonder what you mean by backward compatibility grounds. If
> sum(float4) returned a float8 the only compatibility issue would be
> someone who did "create table as" and then expected to get a float4
> column and instead got a float8 column. That seems like a much more
> minor corner case than most any other backward incompatible change in
> any release.

That is what I mean, yes. Plus somebody could have a view defined,
and the types would change. I think it's only possible to consider
that kind of thing as minor because most people don't use float4 for
anything. Changing the SQL types of things tends to result in fairly
wide-ranging SQL breakage.

> Moreover, it wouldn't be hard to make sum(float4) use a float8 as an
> accumulator and then cast to float4 for the final state. That would be
> 100% compatible with the existing behaviour aside from producing more
> accurate results.

Sure, but if somebody wants that, they can get it already just by a
minor change to the SQL. The point is that adding up float4 as float4
is a reasonable thing to do. Adding it as float8 might also be a
reasonable thing to do, but nobody's keeping anybody from doing that.
It's just not the behavior you get out of the box.

> (Though as an aside, I think Konstantin would be much better served by
> using integers and storing cents or whatever unit of currency is small
> enough. That would actually result in accurate results which neither
> float4 nor float8 guarantee.)

Yep. Or changing the SQL to do sum(f4::float8)::float4, if that's
what he wants.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company