
There is a minor breakage of existing apps that occurs with current CVS.

In 7.0 doing the following:

    create table tsc(f1 int4 , f2 int4);
    insert into tsc values(1,4);
    select sum(f1)/sum(f2) from tsc;

would actually result in zero, since it worked with integers throughout. As
a result, I adopted the following strategy:

    select cast(sum(f1) as float8)/sum(f2) from tsc;

which produced the expected results.

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?

 



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 23 Nov 2000 15:00:26 +1100
Philip Warner <pjw@rhyme.com.au>
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

Wed, 22 Nov 2000 23:27:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Breaking of existing apps with CVS version 
At 23:27 22/11/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>>
>> Is there a reason why it doesn't promote float8 to numeric?
>
>Mixing exact and inexact numerics (to use the
>spec's terminology) can hardly be expected to produce an exact result.

I suppose it's a question of working in the most accurate representation
for each number to minimize inaccuracy, then representing the result as
accurately as possible. Since numeric is more accurate for calculation, I
assumes we'd use it if we had to choose. How we represent the result may be
up to the SQL standard.

All that aside, I was more worried that when people start upgrading to 7.1
we might be a flood of "my application doesn't work any more" bug reports. 


>However, it may indeed cause
>people to run into the numeric-vs-float8 ambiguity.

It's a little more than an ambiguity; anyone that mixes floats with sums
will get a crash in their application.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 23 Nov 2000 15:51:45 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Breaking of existing apps with CVS version 