Thread: Breaking of existing apps with CVS version
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 usingan 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 |/
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
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 |/