Numeric overflow problem + patch
Folks,
Dennis Bj��rklund and I discovered a little problem with how CVS TIP
reports overflows on cast. Please find enclosed a patch which fixes
it.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Attachments:
correct_numeric_overflow_error.patchtext/plain; charset=us-asciiDownload
Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/numeric.c,v
retrieving revision 1.94
diff -c -r1.94 numeric.c
*** src/backend/utils/adt/numeric.c 14 Jul 2006 05:28:28 -0000 1.94
--- src/backend/utils/adt/numeric.c 28 Sep 2006 18:47:31 -0000
***************
*** 3217,3227 ****
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
! errdetail("A field with precision %d, scale %d must have an absolute value less than %s%d.",
precision, scale,
/* Display 10^0 as 1 */
maxdigits ? "10^" : "",
! maxdigits ? maxdigits : 1)));
break;
}
ddigits -= DEC_DIGITS;
--- 3217,3230 ----
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
! errdetail("A field with precision %d, scale %d must have an absolute value less than %s%d - 5 * %s%d.",
precision, scale,
/* Display 10^0 as 1 */
maxdigits ? "10^" : "",
! maxdigits ? maxdigits : 1,
! "10^-",
! scale + 1
! )));
break;
}
ddigits -= DEC_DIGITS;
Index: src/test/regress/expected/numeric.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/numeric.out,v
retrieving revision 1.18
diff -c -r1.18 numeric.out
*** src/test/regress/expected/numeric.out 25 Jan 2006 18:20:22 -0000 1.18
--- src/test/regress/expected/numeric.out 28 Sep 2006 18:47:31 -0000
***************
*** 688,699 ****
INSERT INTO fract_only VALUES (2, '0.1');
INSERT INTO fract_only VALUES (3, '1.0'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
INSERT INTO fract_only VALUES (4, '-0.9999');
INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
SELECT * FROM fract_only;
--- 688,699 ----
INSERT INTO fract_only VALUES (2, '0.1');
INSERT INTO fract_only VALUES (3, '1.0'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.
INSERT INTO fract_only VALUES (4, '-0.9999');
INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
SELECT * FROM fract_only;
David Fetter <david@fetter.org> writes:
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
[ becomes ]
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.
This strikes me as overly pedantic. The message needs to be clear,
and the proposed change will just confuse people.
regards, tom lane
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
[ becomes ]
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.This strikes me as overly pedantic. The message needs to be clear,
and the proposed change will just confuse people.
I don't know if the code can detect the difference, but a message like:
A field with precision 4, scale 4 must *round to* an absolute value less than 1
Since that more accurately describes the actual problem.
Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
[ becomes ]
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.This strikes me as overly pedantic. The message needs to be clear,
and the proposed change will just confuse people.
It might, but the error that's currently in there is wrong. With the
patch applied, you get:
postgres=# SELECT .99995::NUMERIC(4,4);
ERROR: numeric field overflow
DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.
postgres=# SELECT .9999499999999999999999999999::NUMERIC(4,4);
numeric
---------
0.9999
(1 row)
I'd thought of changing it to the corresponding numeric piece, but
this doesn't work so well for NUMERIC(16,8) and the like.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On Thu, Sep 28, 2006 at 11:16:56PM +0200, Martijn van Oosterhout wrote:
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
[ becomes ]
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.This strikes me as overly pedantic. The message needs to be
clear, and the proposed change will just confuse people.I don't know if the code can detect the difference, but a message
like:A field with precision 4, scale 4 must *round to* an absolute value
less than 1
What does .999 round to? How about .5?
Since that more accurately describes the actual problem.
I'd say it doesn't, as worded. Maybe some other wording would be
clearer.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On Thu, Sep 28, 2006 at 11:16:56PM +0200, Martijn van Oosterhout wrote:
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
[ becomes ]
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.This strikes me as overly pedantic. The message needs to be clear,
and the proposed change will just confuse people.I don't know if the code can detect the difference, but a message like:
A field with precision 4, scale 4 must *round to* an absolute value less than 1
Since that more accurately describes the actual problem.
Have a ncie day,
Per your suggestion, how about this patch?
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Attachments:
correct_numeric_overflow_error.patchtext/plain; charset=us-asciiDownload
? correct_numeric_overflow_error.patch
? contrib/plparrot
? contrib/plparrot.tar.bz2
Index: src/backend/utils/adt/numeric.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/numeric.c,v
retrieving revision 1.94
diff -c -r1.94 numeric.c
*** src/backend/utils/adt/numeric.c 14 Jul 2006 05:28:28 -0000 1.94
--- src/backend/utils/adt/numeric.c 2 Oct 2006 00:35:47 -0000
***************
*** 3217,3227 ****
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
! errdetail("A field with precision %d, scale %d must have an absolute value less than %s%d.",
precision, scale,
/* Display 10^0 as 1 */
maxdigits ? "10^" : "",
! maxdigits ? maxdigits : 1)));
break;
}
ddigits -= DEC_DIGITS;
--- 3217,3228 ----
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
! errdetail("A field with precision %d, scale %d must round to an absolute value less than %s%d.",
precision, scale,
/* Display 10^0 as 1 */
maxdigits ? "10^" : "",
! maxdigits ? maxdigits : 1
! )));
break;
}
ddigits -= DEC_DIGITS;
Index: src/test/regress/expected/numeric.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/numeric.out,v
retrieving revision 1.18
diff -c -r1.18 numeric.out
*** src/test/regress/expected/numeric.out 25 Jan 2006 18:20:22 -0000 1.18
--- src/test/regress/expected/numeric.out 2 Oct 2006 00:35:47 -0000
***************
*** 688,699 ****
INSERT INTO fract_only VALUES (2, '0.1');
INSERT INTO fract_only VALUES (3, '1.0'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
INSERT INTO fract_only VALUES (4, '-0.9999');
INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
SELECT * FROM fract_only;
--- 688,699 ----
INSERT INTO fract_only VALUES (2, '0.1');
INSERT INTO fract_only VALUES (3, '1.0'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must round to an absolute value less than 1.
INSERT INTO fract_only VALUES (4, '-0.9999');
INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail
ERROR: numeric field overflow
! DETAIL: A field with precision 4, scale 4 must round to an absolute value less than 1.
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
SELECT * FROM fract_only;
Patch applied. Thanks.
---------------------------------------------------------------------------
David Fetter wrote:
On Thu, Sep 28, 2006 at 11:16:56PM +0200, Martijn van Oosterhout wrote:
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1.
[ becomes ]
! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5.This strikes me as overly pedantic. The message needs to be clear,
and the proposed change will just confuse people.I don't know if the code can detect the difference, but a message like:
A field with precision 4, scale 4 must *round to* an absolute value less than 1
Since that more accurately describes the actual problem.
Have a ncie day,
Per your suggestion, how about this patch?
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetterRemember to vote!
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +