Re: Breaking of existing apps with CVS version

Philip Warner <pjw@rhyme.com.au> writes:
>     select cast(sum(f1) as float8)/sum(f2) from tsc;

> Now in 7.1 this breaks with:

> ERROR:  Unable to identify an operator '/' for types 'float8' and 'numeric'
>         You will have to retype this query using an explicit cast

> Is there a reason why it doesn't promote float8 to numeric?

Actually, if we were to do any automatic coercion in this case,
I think that the SQL spec requires casting in the other direction,
numeric to float8.  Mixing exact and inexact numerics (to use the
spec's terminology) can hardly be expected to produce an exact result.

The reason for the change in behavior is that sum(int4) now produces
numeric, not int4, to avoid overflow problems.  I believe this change
is for the better both in practical terms and in terms of closer
adherence to the intent of the SQL spec.  However, it may indeed cause
people to run into the numeric-vs-float8 ambiguity.

I'd prefer that we not try to solve this issue for 7.1.  We've gone
around on the question of changing the numeric-type promotion hierarchy
a couple of times, without reaching any clear resolution of what to do
--- so I doubt that a quick hack in the waning days of the 7.1 cycle
will prove satisfactory.  Let's leave it be until we have a real
solution.
        regards, tom lane