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

Search Discussions

  • Tom Lane at Nov 23, 2000 at 4:27 am

    Philip Warner 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
  • Philip Warner at Nov 23, 2000 at 4:51 am

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedNov 23, '00 at 3:59a
activeNov 23, '00 at 4:51a
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Philip Warner: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase