Thread: numeric input changes
The attached patch changes the rules for input to
int2/int4/int8/float4/float8 to consistently allow leading and
trailing whitespace in the input. This is for conformance with the
SQL standard, as discussed in an earlier -patches thread.
I also added a bunch more regression tests for the input format
accepted by these types.
I noticed one bug, or at least instance of bit rot: the float8 code
attempts to parse "Infinity" and "-Infinity" specially. Unfortunately,
this doesn't actually work:
nconway=# select 'Infinity'::float8;
ERROR: type "double precision" value out of range: overflow
nconway=# select '-Infinity'::float8;
ERROR: type "double precision" value out of range: overflow
I'm inclined to just remove this attempted functionality from the
code, rather than fix it to work properly. Any comments?
I intend to apply this patch tomorrow.
-Neil
Index: src/backend/utils/adt/float.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/float.c,v
retrieving revision 1.97
diff -c -r1.97 float.c
*** a/src/backend/utils/adt/float.c 4 Mar 2004 21:47:18 -0000 1.97
--- b/src/backend/utils/adt/float.c 10 Mar 2004 19:56:45 -0000
***************
*** 185,198 ****
errno = 0;
val = strtod(num, &endptr);
! if (*endptr != '\0')
{
/*
! * XXX we should accept "Infinity" and "-Infinity" too, but what
! * are the correct values to assign? HUGE_VAL will provoke an
! * error from CheckFloat4Val.
*/
! if (strcasecmp(num, "NaN") == 0)
val = NAN;
else
ereport(ERROR,
--- 185,221 ----
errno = 0;
val = strtod(num, &endptr);
!
! if (errno == ERANGE)
! ereport(ERROR,
! (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
! errmsg("\"%s\" is out of range for type real", num)));
!
! if (num == endptr)
{
/*
! * We didn't find anything that looks like a float in the input
! *
! * In releases prior to 7.5, we accepted an empty string as
! * valid input (yielding a float8 of 0). In 7.5, we accept
! * empty strings, but emit a warning noting that the feature
! * is deprecated. In 7.6+, the warning should be replaced by
! * an error.
! *
! * XXX we should accept "Infinity" and "-Infinity" too, but
! * what are the correct values to assign? HUGE_VAL will
! * provoke an error from CheckFloat4Val.
*/
! if (*num == '\0')
! {
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type real: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
! Assert(val == 0.0);
! }
! else if (strcasecmp(num, "NaN") == 0)
val = NAN;
else
ereport(ERROR,
***************
*** 200,225 ****
errmsg("invalid input syntax for type real: \"%s\"",
num)));
}
- else
- {
- if (errno == ERANGE)
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("\"%s\" is out of range for type real", num)));
- }
! /*
! * In releases prior to 7.5, we accepted an empty string as valid
! * input (yielding a float4 of 0). In 7.5, we accept empty
! * strings, but emit a warning noting that the feature is
! * deprecated. In 7.6+, the warning should be replaced by an error.
! */
! if (num == endptr)
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type real: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
/*
* if we get here, we have a legal double, still need to check to see
--- 223,239 ----
errmsg("invalid input syntax for type real: \"%s\"",
num)));
}
! /* skip trailing whitespace */
! while (*endptr != '\0' && isspace(*endptr))
! endptr++;
!
! /* if there is any junk left at the end of the string, bail out */
! if (*endptr != '\0')
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! errmsg("invalid input syntax for type real: \"%s\"",
! num)));
/*
* if we get here, we have a legal double, still need to check to see
***************
*** 300,308 ****
errno = 0;
val = strtod(num, &endptr);
! if (*endptr != '\0')
{
! if (strcasecmp(num, "NaN") == 0)
val = NAN;
else if (strcasecmp(num, "Infinity") == 0)
val = HUGE_VAL;
--- 314,346 ----
errno = 0;
val = strtod(num, &endptr);
!
! if (errno == ERANGE)
! ereport(ERROR,
! (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
! errmsg("\"%s\" is out of range for type double precision", num)));
!
! if (num == endptr)
{
! /*
! * We didn't find anything that looks like a float in the input
! *
! * In releases prior to 7.5, we accepted an empty string as
! * valid input (yielding a float8 of 0). In 7.5, we accept
! * empty strings, but emit a warning noting that the feature
! * is deprecated. In 7.6+, the warning should be replaced by
! * an error.
! */
! if (*num == '\0')
! {
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type double precision: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
! Assert(val == 0.0);
! }
! else if (strcasecmp(num, "NaN") == 0)
val = NAN;
else if (strcasecmp(num, "Infinity") == 0)
val = HUGE_VAL;
***************
*** 314,339 ****
errmsg("invalid input syntax for type double precision: \"%s\"",
num)));
}
- else
- {
- if (errno == ERANGE)
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("\"%s\" is out of range for type double precision", num)));
- }
! /*
! * In releases prior to 7.5, we accepted an empty string as valid
! * input (yielding a float8 of 0). In 7.5, we accept empty
! * strings, but emit a warning noting that the feature is
! * deprecated. In 7.6+, the warning should be replaced by an error.
! */
! if (num == endptr)
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type double precision: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
CheckFloat8Val(val);
--- 352,368 ----
errmsg("invalid input syntax for type double precision: \"%s\"",
num)));
}
! /* skip trailing whitespace */
! while (*endptr != '\0' && isspace(*endptr))
! endptr++;
!
! /* if there is any junk left at the end of the string, bail out */
! if (*endptr != '\0')
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! errmsg("invalid input syntax for type double precision: \"%s\"",
! num)));
CheckFloat8Val(val);
Index: src/backend/utils/adt/int8.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/int8.c,v
retrieving revision 1.51
diff -c -r1.51 int8.c
*** a/src/backend/utils/adt/int8.c 3 Feb 2004 08:29:56 -0000 1.51
--- b/src/backend/utils/adt/int8.c 10 Mar 2004 19:13:52 -0000
***************
*** 113,120 ****
tmp = newtmp;
}
! /* trailing junk? */
! if (*ptr)
{
if (errorOK)
return false;
--- 113,123 ----
tmp = newtmp;
}
! /* allow trailing whitespace, but not other trailing chars */
! while (*ptr != '\0' && isspace(*ptr))
! ptr++;
!
! if (*ptr != '\0')
{
if (errorOK)
return false;
Index: src/backend/utils/adt/numutils.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/numutils.c,v
retrieving revision 1.61
diff -c -r1.61 numutils.c
*** a/src/backend/utils/adt/numutils.c 18 Feb 2004 00:01:33 -0000 1.61
--- b/src/backend/utils/adt/numutils.c 10 Mar 2004 19:13:52 -0000
***************
*** 19,24 ****
--- 19,25 ----
#include <errno.h>
#include <math.h>
#include <limits.h>
+ #include <ctype.h>
#include "utils/builtins.h"
***************
*** 45,54 ****
/*
* pg_atoi: convert string to integer
*
! * size is the sizeof() the desired integral result (1, 2, or 4 bytes).
*
! * c, if not 0, is the terminator character that may appear after the
! * integer. If 0, the string must end after the integer.
*
* Unlike plain atoi(), this will throw ereport() upon bad input format or
* overflow.
--- 46,57 ----
/*
* pg_atoi: convert string to integer
*
! * 'size' is the sizeof() the desired integral result (1, 2, or 4 bytes).
*
! * allows any number of leading or trailing whitespace characters.
! *
! * 'c' is the character that terminates the input string (after any
! * number of whitespace characters).
*
* Unlike plain atoi(), this will throw ereport() upon bad input format or
* overflow.
***************
*** 84,95 ****
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
s)));
! if (badp && *badp && *badp != c)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
s)));
switch (size)
{
case sizeof(int32):
--- 87,126 ----
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
s)));
!
! /*
! * Skip any trailing whitespace; if anything but whitespace
! * remains at the end of the string, bail out
! */
! while (*badp != c && isspace(*badp))
! badp++;
!
! if (*badp != c)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
s)));
+ /*
+ * We need to explicitely check for and reject an input string
+ * consisting *only* of whitespace characters. When this occurs,
+ * we know that strtol() has returned 0, so we can simply rescan
+ * the string in that case.
+ */
+ if (l == 0)
+ {
+ char *tmp = s;
+
+ while (*tmp != c && isspace(*tmp))
+ tmp++;
+
+ if (*tmp == c)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for integer: \"%s\"",
+ s)));
+ }
+
switch (size)
{
case sizeof(int32):
Index: src/backend/utils/adt/oid.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/oid.c,v
retrieving revision 1.55
diff -c -r1.55 oid.c
*** a/src/backend/utils/adt/oid.c 4 Mar 2004 21:47:18 -0000 1.55
--- b/src/backend/utils/adt/oid.c 10 Mar 2004 19:13:52 -0000
***************
*** 33,38 ****
--- 33,51 ----
char *endptr;
Oid result;
+ /*
+ * In releases prior to 7.5, we accepted an empty string as valid
+ * input (yielding an OID of 0). In 7.5, we accept empty strings,
+ * but emit a warning noting that the feature is deprecated. In
+ * 7.6+, the warning should be replaced by an error.
+ */
+ if (*s == '\0')
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
+ errmsg("deprecated input syntax for type oid: \"\""),
+ errdetail("This input will be rejected in "
+ "a future release of PostgreSQL.")));
+
errno = 0;
cvt = strtoul(s, &endptr, 10);
***************
*** 47,66 ****
errmsg("invalid input syntax for type oid: \"%s\"",
s)));
! /*
! * In releases prior to 7.5, we accepted an empty string as valid
! * input (yielding an OID of 0). In 7.5, we accept empty strings,
! * but emit a warning noting that the feature is deprecated. In
! * 7.6+, the warning should be replaced by an error.
! */
! if (*s == '\0')
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type oid: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
!
! if (endptr == s && *s)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type oid: \"%s\"",
--- 60,66 ----
errmsg("invalid input syntax for type oid: \"%s\"",
s)));
! if (endptr == s && *s != '\0')
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type oid: \"%s\"",
Index: src/test/regress/expected/float4.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/float4.out,v
retrieving revision 1.9
diff -c -r1.9 float4.out
*** a/src/test/regress/expected/float4.out 25 Sep 2003 06:58:06 -0000 1.9
--- b/src/test/regress/expected/float4.out 10 Mar 2004 20:24:44 -0000
***************
*** 2,10 ****
-- FLOAT4
--
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
--- 2,10 ----
-- FLOAT4
--
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 ');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
***************
*** 16,21 ****
--- 16,58 ----
ERROR: type "real" value out of range: underflow
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
ERROR: type "real" value out of range: underflow
+ -- bad input
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for type real: " "
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
+ ERROR: invalid input syntax for type real: "xyz"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
+ ERROR: invalid input syntax for type real: "5.0.0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
+ ERROR: invalid input syntax for type real: "5 . 0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0');
+ ERROR: invalid input syntax for type real: "5. 0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0');
+ ERROR: invalid input syntax for type real: " - 3.0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5');
+ ERROR: invalid input syntax for type real: "123 5"
+ -- special inputs
+ SELECT 'NaN'::float4;
+ float4
+ --------
+ NaN
+ (1 row)
+
+ SELECT 'nan'::float4;
+ float4
+ --------
+ NaN
+ (1 row)
+
+ SELECT ' NAN '::float4;
+ float4
+ --------
+ NaN
+ (1 row)
+
+ -- bad special inputs
+ SELECT 'N A N'::float4;
+ ERROR: invalid input syntax for type real: "N A N"
SELECT '' AS five, FLOAT4_TBL.*;
five | f1
------+-------------
Index: src/test/regress/expected/float8.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/float8.out,v
retrieving revision 1.17
diff -c -r1.17 float8.out
*** a/src/test/regress/expected/float8.out 25 Sep 2003 06:58:06 -0000 1.17
--- b/src/test/regress/expected/float8.out 10 Mar 2004 20:24:48 -0000
***************
*** 2,12 ****
-- FLOAT8
--
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
SELECT '' AS five, FLOAT8_TBL.*;
five | f1
------+----------------------
--- 2,58 ----
-- FLOAT8
--
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
+ -- test for underflow and overflow
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+ ERROR: "10e400" is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+ ERROR: "-10e400" is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+ ERROR: "10e-400" is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+ ERROR: "-10e-400" is out of range for type double precision
+ -- bad input
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for type double precision: " "
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
+ ERROR: invalid input syntax for type double precision: "xyz"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
+ ERROR: invalid input syntax for type double precision: "5.0.0"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
+ ERROR: invalid input syntax for type double precision: "5 . 0"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0');
+ ERROR: invalid input syntax for type double precision: "5. 0"
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3');
+ ERROR: invalid input syntax for type double precision: " - 3"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5');
+ ERROR: invalid input syntax for type double precision: "123 5"
+ -- special inputs
+ SELECT 'NaN'::float8;
+ float8
+ --------
+ NaN
+ (1 row)
+
+ SELECT 'nan'::float8;
+ float8
+ --------
+ NaN
+ (1 row)
+
+ SELECT ' NAN '::float8;
+ float8
+ --------
+ NaN
+ (1 row)
+
+ -- bad special inputs
+ SELECT 'N A N'::float8;
+ ERROR: invalid input syntax for type double precision: "N A N"
SELECT '' AS five, FLOAT8_TBL.*;
five | f1
------+----------------------
Index: src/test/regress/expected/int2.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/int2.out,v
retrieving revision 1.10
diff -c -r1.10 int2.out
*** a/src/test/regress/expected/int2.out 25 Sep 2003 06:58:06 -0000 1.10
--- b/src/test/regress/expected/int2.out 10 Mar 2004 20:25:06 -0000
***************
*** 4,22 ****
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0');
! INSERT INTO INT2_TBL(f1) VALUES ('1234');
! INSERT INTO INT2_TBL(f1) VALUES ('-1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give warnings
INSERT INTO INT2_TBL(f1) VALUES ('100000');
ERROR: value "100000" is out of range for type shortint
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
SELECT '' AS five, INT2_TBL.*;
five | f1
------+--------
--- 4,32 ----
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0 ');
! INSERT INTO INT2_TBL(f1) VALUES (' 1234 ');
! INSERT INTO INT2_TBL(f1) VALUES (' -1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give errors
INSERT INTO INT2_TBL(f1) VALUES ('100000');
ERROR: value "100000" is out of range for type shortint
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
+ INSERT INTO INT2_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for integer: " "
+ INSERT INTO INT2_TBL(f1) VALUES ('- 1234');
+ ERROR: invalid input syntax for integer: "- 1234"
+ INSERT INTO INT2_TBL(f1) VALUES ('4 444');
+ ERROR: invalid input syntax for integer: "4 444"
+ INSERT INTO INT2_TBL(f1) VALUES ('123 dt');
+ ERROR: invalid input syntax for integer: "123 dt"
+ INSERT INTO INT2_TBL(f1) VALUES ('');
+ ERROR: invalid input syntax for integer: ""
SELECT '' AS five, INT2_TBL.*;
five | f1
------+--------
Index: src/test/regress/expected/int4.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/int4.out,v
retrieving revision 1.12
diff -c -r1.12 int4.out
*** a/src/test/regress/expected/int4.out 25 Sep 2003 06:58:06 -0000 1.12
--- b/src/test/regress/expected/int4.out 10 Mar 2004 20:25:06 -0000
***************
*** 4,22 ****
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES ('0');
! INSERT INTO INT4_TBL(f1) VALUES ('123456');
! INSERT INTO INT4_TBL(f1) VALUES ('-123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give warnings
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
ERROR: value "1000000000000" is out of range for type integer
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
SELECT '' AS five, INT4_TBL.*;
five | f1
------+-------------
--- 4,32 ----
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES (' 0 ');
! INSERT INTO INT4_TBL(f1) VALUES ('123456 ');
! INSERT INTO INT4_TBL(f1) VALUES (' -123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give errors
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
ERROR: value "1000000000000" is out of range for type integer
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
+ INSERT INTO INT4_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for integer: " "
+ INSERT INTO INT4_TBL(f1) VALUES (' asdf ');
+ ERROR: invalid input syntax for integer: " asdf "
+ INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
+ ERROR: invalid input syntax for integer: "- 1234"
+ INSERT INTO INT4_TBL(f1) VALUES ('123 5');
+ ERROR: invalid input syntax for integer: "123 5"
+ INSERT INTO INT4_TBL(f1) VALUES ('');
+ ERROR: invalid input syntax for integer: ""
SELECT '' AS five, INT4_TBL.*;
five | f1
------+-------------
***************
*** 117,130 ****
| 2147483647
(3 rows)
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
one | f1
-----+------------
| 2147483647
(1 row)
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
three | f1
-------+---------
--- 127,140 ----
| 2147483647
(3 rows)
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
one | f1
-----+------------
| 2147483647
(1 row)
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
three | f1
-------+---------
Index: src/test/regress/expected/int8.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/int8.out,v
retrieving revision 1.7
diff -c -r1.7 int8.out
*** a/src/test/regress/expected/int8.out 27 Mar 2003 16:35:31 -0000 1.7
--- b/src/test/regress/expected/int8.out 10 Mar 2004 20:25:06 -0000
***************
*** 3,13 ****
-- Test int8 64-bit integers.
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES('123','456');
! INSERT INTO INT8_TBL VALUES('123','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
SELECT * FROM INT8_TBL;
q1 | q2
------------------+-------------------
--- 3,28 ----
-- Test int8 64-bit integers.
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES(' 123 ',' 456');
! INSERT INTO INT8_TBL VALUES('123 ','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
+ -- bad inputs
+ INSERT INTO INT8_TBL(q1) VALUES (' ');
+ ERROR: invalid input syntax for type bigint: " "
+ INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+ ERROR: invalid input syntax for type bigint: "xxx"
+ INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
+ ERROR: integer out of range
+ INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
+ ERROR: integer out of range
+ INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+ ERROR: invalid input syntax for type bigint: "- 123"
+ INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
+ ERROR: invalid input syntax for type bigint: " 345 5"
+ INSERT INTO INT8_TBL(q1) VALUES ('');
+ ERROR: invalid input syntax for type bigint: ""
SELECT * FROM INT8_TBL;
q1 | q2
------------------+-------------------
Index: src/test/regress/expected/numeric.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/numeric.out,v
retrieving revision 1.12
diff -c -r1.12 numeric.out
*** a/src/test/regress/expected/numeric.out 25 Sep 2003 06:58:06 -0000 1.12
--- b/src/test/regress/expected/numeric.out 10 Mar 2004 20:24:37 -0000
***************
*** 670,675 ****
--- 670,687 ----
-13430913.592242320700
(1 row)
+ SELECT STDDEV(val) FROM num_data;
+ stddev
+ -------------------------------
+ 27791203.28758835329805617386
+ (1 row)
+
+ SELECT VARIANCE(val) FROM num_data;
+ variance
+ --------------------------------------
+ 772350980172061.69659105821915863601
+ (1 row)
+
-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));
INSERT INTO fract_only VALUES (1, '0.0');
***************
*** 1112,1114 ****
--- 1124,1167 ----
| -0.01
(1 row)
+ --
+ -- Input syntax
+ --
+ CREATE TABLE num_input_test (n1 numeric);
+ -- good inputs
+ INSERT INTO num_input_test(n1) VALUES (' 123');
+ INSERT INTO num_input_test(n1) VALUES (' 3245874 ');
+ INSERT INTO num_input_test(n1) VALUES (' -93853');
+ INSERT INTO num_input_test(n1) VALUES ('555.50');
+ INSERT INTO num_input_test(n1) VALUES ('-555.50');
+ INSERT INTO num_input_test(n1) VALUES ('NaN ');
+ ERROR: invalid input syntax for type numeric: "NaN "
+ INSERT INTO num_input_test(n1) VALUES (' nan');
+ ERROR: invalid input syntax for type numeric: " nan"
+ -- bad inputs
+ INSERT INTO num_input_test(n1) VALUES (' ');
+ ERROR: invalid input syntax for type numeric: " "
+ INSERT INTO num_input_test(n1) VALUES (' 1234 %');
+ ERROR: invalid input syntax for type numeric: " 1234 %"
+ INSERT INTO num_input_test(n1) VALUES ('xyz');
+ ERROR: invalid input syntax for type numeric: "xyz"
+ INSERT INTO num_input_test(n1) VALUES ('- 1234');
+ ERROR: invalid input syntax for type numeric: "- 1234"
+ INSERT INTO num_input_test(n1) VALUES ('5 . 0');
+ ERROR: invalid input syntax for type numeric: "5 . 0"
+ INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
+ ERROR: invalid input syntax for type numeric: "5. 0 "
+ INSERT INTO num_input_test(n1) VALUES ('');
+ ERROR: invalid input syntax for type numeric: ""
+ INSERT INTO num_input_test(n1) VALUES (' N aN ');
+ ERROR: invalid input syntax for type numeric: " N aN "
+ SELECT * FROM num_input_test;
+ n1
+ ---------
+ 123
+ 3245874
+ -93853
+ 555.50
+ -555.50
+ (5 rows)
+
Index: src/test/regress/expected/oid.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/oid.out,v
retrieving revision 1.9
diff -c -r1.9 oid.out
*** a/src/test/regress/expected/oid.out 4 Mar 2004 21:47:18 -0000 1.9
--- b/src/test/regress/expected/oid.out 10 Mar 2004 20:28:06 -0000
***************
*** 7,17 ****
--- 7,35 ----
INSERT INTO OID_TBL(f1) VALUES ('987');
INSERT INTO OID_TBL(f1) VALUES ('-1040');
INSERT INTO OID_TBL(f1) VALUES ('99999999');
+ INSERT INTO OID_TBL(f1) VALUES ('5 ');
+ INSERT INTO OID_TBL(f1) VALUES (' 10 ');
+ -- leading/trailing hard tab is also allowed
+ INSERT INTO OID_TBL(f1) VALUES (' 15 ');
-- bad inputs
INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
ERROR: invalid input syntax for type oid: "asdfasd"
INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
ERROR: invalid input syntax for type oid: "99asdfasd"
+ INSERT INTO OID_TBL(f1) VALUES ('5 d');
+ ERROR: invalid input syntax for type oid: "5 d"
+ INSERT INTO OID_TBL(f1) VALUES (' 5d');
+ ERROR: invalid input syntax for type oid: " 5d"
+ INSERT INTO OID_TBL(f1) VALUES ('5 5');
+ ERROR: invalid input syntax for type oid: "5 5"
+ INSERT INTO OID_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for type oid: " "
+ INSERT INTO OID_TBL(f1) VALUES (' - 500');
+ ERROR: invalid input syntax for type oid: " - 500"
+ INSERT INTO OID_TBL(f1) VALUES ('32958209582039852935');
+ ERROR: value "32958209582039852935" is out of range for type oid
+ INSERT INTO OID_TBL(f1) VALUES ('-23582358720398502385');
+ ERROR: value "-23582358720398502385" is out of range for type oid
SELECT '' AS six, OID_TBL.*;
six | f1
-----+------------
***************
*** 20,26 ****
| 987
| 4294966256
| 99999999
! (5 rows)
SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234;
one | f1
--- 38,47 ----
| 987
| 4294966256
| 99999999
! | 5
! | 10
! | 15
! (8 rows)
SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234;
one | f1
***************
*** 35,54 ****
| 987
| 4294966256
| 99999999
! (4 rows)
SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 <= '1234';
three | f1
-------+------
| 1234
| 987
! (2 rows)
SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 < '1234';
two | f1
-----+-----
| 987
! (1 row)
SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234';
four | f1
--- 56,84 ----
| 987
| 4294966256
| 99999999
! | 5
! | 10
! | 15
! (7 rows)
SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 <= '1234';
three | f1
-------+------
| 1234
| 987
! | 5
! | 10
! | 15
! (5 rows)
SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 < '1234';
two | f1
-----+-----
| 987
! | 5
! | 10
! | 15
! (4 rows)
SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234';
four | f1
Index: src/test/regress/output/misc.source
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/output/misc.source,v
retrieving revision 1.39
diff -c -r1.39 misc.source
*** a/src/test/regress/output/misc.source 20 Jul 2003 21:56:35 -0000 1.39
--- b/src/test/regress/output/misc.source 10 Mar 2004 20:22:54 -0000
***************
*** 631,636 ****
--- 631,637 ----
num_exp_power_10_ln
num_exp_sqrt
num_exp_sub
+ num_input_test
num_result
onek
onek2
***************
*** 660,666 ****
toyemp
varchar_tbl
xacttest
! (96 rows)
--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
SELECT hobbies_by_name('basketball');
--- 661,667 ----
toyemp
varchar_tbl
xacttest
! (97 rows)
--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
SELECT hobbies_by_name('basketball');
Index: src/test/regress/sql/float4.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/float4.sql,v
retrieving revision 1.4
diff -c -r1.4 float4.sql
*** a/src/test/regress/sql/float4.sql 4 Jan 2000 16:21:02 -0000 1.4
--- b/src/test/regress/sql/float4.sql 10 Mar 2004 19:52:57 -0000
***************
*** 4,28 ****
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0');
!
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30');
!
! INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84');
!
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
SELECT '' AS five, FLOAT4_TBL.*;
--- 4,36 ----
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 ');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40');
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40');
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
+ -- bad input
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' ');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5');
+
+ -- special inputs
+ SELECT 'NaN'::float4;
+ SELECT 'nan'::float4;
+ SELECT ' NAN '::float4;
+ -- bad special inputs
+ SELECT 'N A N'::float4;
SELECT '' AS five, FLOAT4_TBL.*;
Index: src/test/regress/sql/float8.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/float8.sql,v
retrieving revision 1.8
diff -c -r1.8 float8.sql
*** a/src/test/regress/sql/float8.sql 19 Oct 2002 02:08:19 -0000 1.8
--- b/src/test/regress/sql/float8.sql 10 Mar 2004 20:11:14 -0000
***************
*** 4,19 ****
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
!
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
!
! INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
!
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
-
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
SELECT '' AS five, FLOAT8_TBL.*;
--- 4,36 ----
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
+ -- test for underflow and overflow
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+
+ -- bad input
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' ');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0');
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5');
+
+ -- special inputs
+ SELECT 'NaN'::float8;
+ SELECT 'nan'::float8;
+ SELECT ' NAN '::float8;
+ -- bad special inputs
+ SELECT 'N A N'::float8;
SELECT '' AS five, FLOAT8_TBL.*;
Index: src/test/regress/sql/int2.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/int2.sql,v
retrieving revision 1.4
diff -c -r1.4 int2.sql
*** a/src/test/regress/sql/int2.sql 4 Jan 2000 16:21:02 -0000 1.4
--- b/src/test/regress/sql/int2.sql 10 Mar 2004 19:13:53 -0000
***************
*** 6,28 ****
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0');
! INSERT INTO INT2_TBL(f1) VALUES ('1234');
! INSERT INTO INT2_TBL(f1) VALUES ('-1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give warnings
INSERT INTO INT2_TBL(f1) VALUES ('100000');
-
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
SELECT '' AS five, INT2_TBL.*;
--- 6,32 ----
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0 ');
! INSERT INTO INT2_TBL(f1) VALUES (' 1234 ');
! INSERT INTO INT2_TBL(f1) VALUES (' -1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give errors
INSERT INTO INT2_TBL(f1) VALUES ('100000');
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
+ INSERT INTO INT2_TBL(f1) VALUES (' ');
+ INSERT INTO INT2_TBL(f1) VALUES ('- 1234');
+ INSERT INTO INT2_TBL(f1) VALUES ('4 444');
+ INSERT INTO INT2_TBL(f1) VALUES ('123 dt');
+ INSERT INTO INT2_TBL(f1) VALUES ('');
SELECT '' AS five, INT2_TBL.*;
Index: src/test/regress/sql/int4.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/int4.sql,v
retrieving revision 1.6
diff -c -r1.6 int4.sql
*** a/src/test/regress/sql/int4.sql 14 Mar 2000 23:06:58 -0000 1.6
--- b/src/test/regress/sql/int4.sql 10 Mar 2004 19:13:53 -0000
***************
*** 6,28 ****
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES ('0');
! INSERT INTO INT4_TBL(f1) VALUES ('123456');
! INSERT INTO INT4_TBL(f1) VALUES ('-123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give warnings
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
-
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
SELECT '' AS five, INT4_TBL.*;
--- 6,32 ----
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES (' 0 ');
! INSERT INTO INT4_TBL(f1) VALUES ('123456 ');
! INSERT INTO INT4_TBL(f1) VALUES (' -123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give errors
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
+ INSERT INTO INT4_TBL(f1) VALUES (' ');
+ INSERT INTO INT4_TBL(f1) VALUES (' asdf ');
+ INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
+ INSERT INTO INT4_TBL(f1) VALUES ('123 5');
+ INSERT INTO INT4_TBL(f1) VALUES ('');
SELECT '' AS five, INT4_TBL.*;
***************
*** 51,60 ****
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i;
--- 55,64 ----
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i;
Index: src/test/regress/sql/int8.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/int8.sql,v
retrieving revision 1.5
diff -c -r1.5 int8.sql
*** a/src/test/regress/sql/int8.sql 26 Jan 2001 22:50:26 -0000 1.5
--- b/src/test/regress/sql/int8.sql 10 Mar 2004 19:13:53 -0000
***************
*** 4,15 ****
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES('123','456');
! INSERT INTO INT8_TBL VALUES('123','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
SELECT * FROM INT8_TBL;
SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;
--- 4,24 ----
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES(' 123 ',' 456');
! INSERT INTO INT8_TBL VALUES('123 ','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
+ -- bad inputs
+ INSERT INTO INT8_TBL(q1) VALUES (' ');
+ INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+ INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
+ INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
+ INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+ INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
+ INSERT INTO INT8_TBL(q1) VALUES ('');
+
SELECT * FROM INT8_TBL;
SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;
Index: src/test/regress/sql/numeric.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/numeric.sql,v
retrieving revision 1.7
diff -c -r1.7 numeric.sql
*** a/src/test/regress/sql/numeric.sql 7 Apr 2000 19:17:50 -0000 1.7
--- b/src/test/regress/sql/numeric.sql 10 Mar 2004 20:07:51 -0000
***************
*** 639,644 ****
--- 639,646 ----
-- ******************************
-- numeric AVG used to fail on some platforms
SELECT AVG(val) FROM num_data;
+ SELECT STDDEV(val) FROM num_data;
+ SELECT VARIANCE(val) FROM num_data;
-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));
***************
*** 701,703 ****
--- 703,732 ----
SELECT '' AS to_number_11, to_number('.-01', 'S99.99');
SELECT '' AS to_number_12, to_number('.01-', '99.99S');
SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S');
+
+ --
+ -- Input syntax
+ --
+
+ CREATE TABLE num_input_test (n1 numeric);
+
+ -- good inputs
+ INSERT INTO num_input_test(n1) VALUES (' 123');
+ INSERT INTO num_input_test(n1) VALUES (' 3245874 ');
+ INSERT INTO num_input_test(n1) VALUES (' -93853');
+ INSERT INTO num_input_test(n1) VALUES ('555.50');
+ INSERT INTO num_input_test(n1) VALUES ('-555.50');
+ INSERT INTO num_input_test(n1) VALUES ('NaN ');
+ INSERT INTO num_input_test(n1) VALUES (' nan');
+
+ -- bad inputs
+ INSERT INTO num_input_test(n1) VALUES (' ');
+ INSERT INTO num_input_test(n1) VALUES (' 1234 %');
+ INSERT INTO num_input_test(n1) VALUES ('xyz');
+ INSERT INTO num_input_test(n1) VALUES ('- 1234');
+ INSERT INTO num_input_test(n1) VALUES ('5 . 0');
+ INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
+ INSERT INTO num_input_test(n1) VALUES ('');
+ INSERT INTO num_input_test(n1) VALUES (' N aN ');
+
+ SELECT * FROM num_input_test;
Index: src/test/regress/sql/oid.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/oid.sql,v
retrieving revision 1.5
diff -c -r1.5 oid.sql
*** a/src/test/regress/sql/oid.sql 4 Mar 2004 21:47:18 -0000 1.5
--- b/src/test/regress/sql/oid.sql 10 Mar 2004 19:13:53 -0000
***************
*** 5,23 ****
CREATE TABLE OID_TBL(f1 oid);
INSERT INTO OID_TBL(f1) VALUES ('1234');
-
INSERT INTO OID_TBL(f1) VALUES ('1235');
-
INSERT INTO OID_TBL(f1) VALUES ('987');
-
INSERT INTO OID_TBL(f1) VALUES ('-1040');
-
INSERT INTO OID_TBL(f1) VALUES ('99999999');
-- bad inputs
INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
SELECT '' AS six, OID_TBL.*;
--- 5,30 ----
CREATE TABLE OID_TBL(f1 oid);
INSERT INTO OID_TBL(f1) VALUES ('1234');
INSERT INTO OID_TBL(f1) VALUES ('1235');
INSERT INTO OID_TBL(f1) VALUES ('987');
INSERT INTO OID_TBL(f1) VALUES ('-1040');
INSERT INTO OID_TBL(f1) VALUES ('99999999');
+ INSERT INTO OID_TBL(f1) VALUES ('5 ');
+ INSERT INTO OID_TBL(f1) VALUES (' 10 ');
+ -- leading/trailing hard tab is also allowed
+ INSERT INTO OID_TBL(f1) VALUES (' 15 ');
-- bad inputs
INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
+ INSERT INTO OID_TBL(f1) VALUES ('5 d');
+ INSERT INTO OID_TBL(f1) VALUES (' 5d');
+ INSERT INTO OID_TBL(f1) VALUES ('5 5');
+ INSERT INTO OID_TBL(f1) VALUES (' ');
+ INSERT INTO OID_TBL(f1) VALUES (' - 500');
+ INSERT INTO OID_TBL(f1) VALUES ('32958209582039852935');
+ INSERT INTO OID_TBL(f1) VALUES ('-23582358720398502385');
SELECT '' AS six, OID_TBL.*;
> I noticed one bug, or at least instance of bit rot: the float8 code > attempts to parse "Infinity" and "-Infinity" specially. Unfortunately, > this doesn't actually work: Infinity doesn't work for float4 either.
Neil Conway <neilc@samurai.com> writes:
> nconway=# select 'Infinity'::float8;
> ERROR: type "double precision" value out of range: overflow
> nconway=# select '-Infinity'::float8;
> ERROR: type "double precision" value out of range: overflow
> I'm inclined to just remove this attempted functionality from the
> code, rather than fix it to work properly. Any comments?
This works in 7.4 and older releases. Sounds to me like you broke it.
regards, tom lane
On Wed, 10 Mar 2004, Tom Lane wrote: > > nconway=# select 'Infinity'::float8; > > ERROR: type "double precision" value out of range: overflow > > This works in 7.4 and older releases. Sounds to me like you broke it. It does not work on 7.3 and 7.4 when I try. Looking at the currect cvs code it's not just parsing 'Infinity' as a value, there is also an overflow check after float operations that will exclude Infinity and Nan. By the way, this thing was discussed on irc and I just sent a mail on -hackers about it, not knowing about this thread here. There are too many lists :-) -- /Dennis Björklund
Dennis Bjorklund <db@zigo.dhs.org> writes:
> By the way, this thing was discussed on irc and I just sent a mail on
> -hackers about it, not knowing about this thread here.
Yeah, let's take the discussion to -hackers using your message as a
base.
regards, tom lane
Neil Conway <neilc@samurai.com> writes: > The attached patch changes the rules for input to > int2/int4/int8/float4/float8 to consistently allow leading and > trailing whitespace in the input. I've attached a slightly revised version of the patch, which I've applied to CVS HEAD. I realized that pg_atoi() can be implemented more simply -- that's the only change. Also, I decided to apply the patch tonight, so that I can get started on the float4/float8 Infinity stuff more easily. -Neil
Neil Conway <neilc@samurai.com> writes:
> I've attached a slightly revised version of the patch, which I've
> applied to CVS HEAD.
*sigh*, actually attached this time.
-Neil
Index: src/backend/utils/adt/float.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/float.c,v
retrieving revision 1.97
diff -c -r1.97 float.c
*** a/src/backend/utils/adt/float.c 4 Mar 2004 21:47:18 -0000 1.97
--- b/src/backend/utils/adt/float.c 11 Mar 2004 01:54:13 -0000
***************
*** 185,198 ****
errno = 0;
val = strtod(num, &endptr);
! if (*endptr != '\0')
{
/*
! * XXX we should accept "Infinity" and "-Infinity" too, but what
! * are the correct values to assign? HUGE_VAL will provoke an
! * error from CheckFloat4Val.
*/
! if (strcasecmp(num, "NaN") == 0)
val = NAN;
else
ereport(ERROR,
--- 185,221 ----
errno = 0;
val = strtod(num, &endptr);
!
! if (errno == ERANGE)
! ereport(ERROR,
! (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
! errmsg("\"%s\" is out of range for type real", num)));
!
! if (num == endptr)
{
/*
! * We didn't find anything that looks like a float in the input
! *
! * In releases prior to 7.5, we accepted an empty string as
! * valid input (yielding a float8 of 0). In 7.5, we accept
! * empty strings, but emit a warning noting that the feature
! * is deprecated. In 7.6+, the warning should be replaced by
! * an error.
! *
! * XXX we should accept "Infinity" and "-Infinity" too, but
! * what are the correct values to assign? HUGE_VAL will
! * provoke an error from CheckFloat4Val.
*/
! if (*num == '\0')
! {
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type real: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
! Assert(val == 0.0);
! }
! else if (strcasecmp(num, "NaN") == 0)
val = NAN;
else
ereport(ERROR,
***************
*** 200,225 ****
errmsg("invalid input syntax for type real: \"%s\"",
num)));
}
- else
- {
- if (errno == ERANGE)
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("\"%s\" is out of range for type real", num)));
- }
! /*
! * In releases prior to 7.5, we accepted an empty string as valid
! * input (yielding a float4 of 0). In 7.5, we accept empty
! * strings, but emit a warning noting that the feature is
! * deprecated. In 7.6+, the warning should be replaced by an error.
! */
! if (num == endptr)
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type real: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
/*
* if we get here, we have a legal double, still need to check to see
--- 223,239 ----
errmsg("invalid input syntax for type real: \"%s\"",
num)));
}
! /* skip trailing whitespace */
! while (*endptr != '\0' && isspace(*endptr))
! endptr++;
!
! /* if there is any junk left at the end of the string, bail out */
! if (*endptr != '\0')
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! errmsg("invalid input syntax for type real: \"%s\"",
! num)));
/*
* if we get here, we have a legal double, still need to check to see
***************
*** 300,308 ****
errno = 0;
val = strtod(num, &endptr);
! if (*endptr != '\0')
{
! if (strcasecmp(num, "NaN") == 0)
val = NAN;
else if (strcasecmp(num, "Infinity") == 0)
val = HUGE_VAL;
--- 314,346 ----
errno = 0;
val = strtod(num, &endptr);
!
! if (errno == ERANGE)
! ereport(ERROR,
! (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
! errmsg("\"%s\" is out of range for type double precision", num)));
!
! if (num == endptr)
{
! /*
! * We didn't find anything that looks like a float in the input
! *
! * In releases prior to 7.5, we accepted an empty string as
! * valid input (yielding a float8 of 0). In 7.5, we accept
! * empty strings, but emit a warning noting that the feature
! * is deprecated. In 7.6+, the warning should be replaced by
! * an error.
! */
! if (*num == '\0')
! {
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type double precision: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
! Assert(val == 0.0);
! }
! else if (strcasecmp(num, "NaN") == 0)
val = NAN;
else if (strcasecmp(num, "Infinity") == 0)
val = HUGE_VAL;
***************
*** 314,339 ****
errmsg("invalid input syntax for type double precision: \"%s\"",
num)));
}
- else
- {
- if (errno == ERANGE)
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("\"%s\" is out of range for type double precision", num)));
- }
! /*
! * In releases prior to 7.5, we accepted an empty string as valid
! * input (yielding a float8 of 0). In 7.5, we accept empty
! * strings, but emit a warning noting that the feature is
! * deprecated. In 7.6+, the warning should be replaced by an error.
! */
! if (num == endptr)
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type double precision: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
CheckFloat8Val(val);
--- 352,368 ----
errmsg("invalid input syntax for type double precision: \"%s\"",
num)));
}
! /* skip trailing whitespace */
! while (*endptr != '\0' && isspace(*endptr))
! endptr++;
!
! /* if there is any junk left at the end of the string, bail out */
! if (*endptr != '\0')
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! errmsg("invalid input syntax for type double precision: \"%s\"",
! num)));
CheckFloat8Val(val);
Index: src/backend/utils/adt/int8.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/int8.c,v
retrieving revision 1.51
diff -c -r1.51 int8.c
*** a/src/backend/utils/adt/int8.c 3 Feb 2004 08:29:56 -0000 1.51
--- b/src/backend/utils/adt/int8.c 11 Mar 2004 01:54:13 -0000
***************
*** 113,120 ****
tmp = newtmp;
}
! /* trailing junk? */
! if (*ptr)
{
if (errorOK)
return false;
--- 113,123 ----
tmp = newtmp;
}
! /* allow trailing whitespace, but not other trailing chars */
! while (*ptr != '\0' && isspace(*ptr))
! ptr++;
!
! if (*ptr != '\0')
{
if (errorOK)
return false;
Index: src/backend/utils/adt/numutils.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/numutils.c,v
retrieving revision 1.61
diff -c -r1.61 numutils.c
*** a/src/backend/utils/adt/numutils.c 18 Feb 2004 00:01:33 -0000 1.61
--- b/src/backend/utils/adt/numutils.c 11 Mar 2004 01:58:50 -0000
***************
*** 19,24 ****
--- 19,25 ----
#include <errno.h>
#include <math.h>
#include <limits.h>
+ #include <ctype.h>
#include "utils/builtins.h"
***************
*** 45,54 ****
/*
* pg_atoi: convert string to integer
*
! * size is the sizeof() the desired integral result (1, 2, or 4 bytes).
*
! * c, if not 0, is the terminator character that may appear after the
! * integer. If 0, the string must end after the integer.
*
* Unlike plain atoi(), this will throw ereport() upon bad input format or
* overflow.
--- 46,57 ----
/*
* pg_atoi: convert string to integer
*
! * 'size' is the sizeof() the desired integral result (1, 2, or 4 bytes).
*
! * allows any number of leading or trailing whitespace characters.
! *
! * 'c' is the character that terminates the input string (after any
! * number of whitespace characters).
*
* Unlike plain atoi(), this will throw ereport() upon bad input format or
* overflow.
***************
*** 57,63 ****
pg_atoi(char *s, int size, int c)
{
long l;
! char *badp = NULL;
/*
* Some versions of strtol treat the empty string as an error, but
--- 60,66 ----
pg_atoi(char *s, int size, int c)
{
long l;
! char *badp;
/*
* Some versions of strtol treat the empty string as an error, but
***************
*** 74,90 ****
errno = 0;
l = strtol(s, &badp, 10);
! /*
! * strtol() normally only sets ERANGE. On some systems it also may
! * set EINVAL, which simply means it couldn't parse the input string.
! * This is handled by the second "if" consistent across platforms.
! */
! if (errno && errno != ERANGE && errno != EINVAL)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
s)));
! if (badp && *badp && *badp != c)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
--- 77,97 ----
errno = 0;
l = strtol(s, &badp, 10);
! /* We made no progress parsing the string, so bail out */
! if (s == badp)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
s)));
!
! /*
! * Skip any trailing whitespace; if anything but whitespace
! * remains before the terminating character, bail out
! */
! while (*badp != c && isspace(*badp))
! badp++;
!
! if (*badp != c)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
Index: src/backend/utils/adt/oid.c
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/backend/utils/adt/oid.c,v
retrieving revision 1.55
diff -c -r1.55 oid.c
*** a/src/backend/utils/adt/oid.c 4 Mar 2004 21:47:18 -0000 1.55
--- b/src/backend/utils/adt/oid.c 11 Mar 2004 01:54:13 -0000
***************
*** 33,38 ****
--- 33,51 ----
char *endptr;
Oid result;
+ /*
+ * In releases prior to 7.5, we accepted an empty string as valid
+ * input (yielding an OID of 0). In 7.5, we accept empty strings,
+ * but emit a warning noting that the feature is deprecated. In
+ * 7.6+, the warning should be replaced by an error.
+ */
+ if (*s == '\0')
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
+ errmsg("deprecated input syntax for type oid: \"\""),
+ errdetail("This input will be rejected in "
+ "a future release of PostgreSQL.")));
+
errno = 0;
cvt = strtoul(s, &endptr, 10);
***************
*** 47,66 ****
errmsg("invalid input syntax for type oid: \"%s\"",
s)));
! /*
! * In releases prior to 7.5, we accepted an empty string as valid
! * input (yielding an OID of 0). In 7.5, we accept empty strings,
! * but emit a warning noting that the feature is deprecated. In
! * 7.6+, the warning should be replaced by an error.
! */
! if (*s == '\0')
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg("deprecated input syntax for type oid: \"\""),
! errdetail("This input will be rejected in "
! "a future release of PostgreSQL.")));
!
! if (endptr == s && *s)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type oid: \"%s\"",
--- 60,66 ----
errmsg("invalid input syntax for type oid: \"%s\"",
s)));
! if (endptr == s && *s != '\0')
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type oid: \"%s\"",
Index: src/test/regress/expected/float4.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/float4.out,v
retrieving revision 1.9
diff -c -r1.9 float4.out
*** a/src/test/regress/expected/float4.out 25 Sep 2003 06:58:06 -0000 1.9
--- b/src/test/regress/expected/float4.out 11 Mar 2004 01:54:13 -0000
***************
*** 2,10 ****
-- FLOAT4
--
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
--- 2,10 ----
-- FLOAT4
--
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 ');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
***************
*** 16,21 ****
--- 16,58 ----
ERROR: type "real" value out of range: underflow
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
ERROR: type "real" value out of range: underflow
+ -- bad input
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for type real: " "
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
+ ERROR: invalid input syntax for type real: "xyz"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
+ ERROR: invalid input syntax for type real: "5.0.0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
+ ERROR: invalid input syntax for type real: "5 . 0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0');
+ ERROR: invalid input syntax for type real: "5. 0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0');
+ ERROR: invalid input syntax for type real: " - 3.0"
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5');
+ ERROR: invalid input syntax for type real: "123 5"
+ -- special inputs
+ SELECT 'NaN'::float4;
+ float4
+ --------
+ NaN
+ (1 row)
+
+ SELECT 'nan'::float4;
+ float4
+ --------
+ NaN
+ (1 row)
+
+ SELECT ' NAN '::float4;
+ float4
+ --------
+ NaN
+ (1 row)
+
+ -- bad special inputs
+ SELECT 'N A N'::float4;
+ ERROR: invalid input syntax for type real: "N A N"
SELECT '' AS five, FLOAT4_TBL.*;
five | f1
------+-------------
Index: src/test/regress/expected/float8.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/float8.out,v
retrieving revision 1.17
diff -c -r1.17 float8.out
*** a/src/test/regress/expected/float8.out 25 Sep 2003 06:58:06 -0000 1.17
--- b/src/test/regress/expected/float8.out 11 Mar 2004 01:54:13 -0000
***************
*** 2,12 ****
-- FLOAT8
--
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
SELECT '' AS five, FLOAT8_TBL.*;
five | f1
------+----------------------
--- 2,58 ----
-- FLOAT8
--
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
+ -- test for underflow and overflow
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+ ERROR: "10e400" is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+ ERROR: "-10e400" is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+ ERROR: "10e-400" is out of range for type double precision
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+ ERROR: "-10e-400" is out of range for type double precision
+ -- bad input
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for type double precision: " "
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
+ ERROR: invalid input syntax for type double precision: "xyz"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
+ ERROR: invalid input syntax for type double precision: "5.0.0"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
+ ERROR: invalid input syntax for type double precision: "5 . 0"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0');
+ ERROR: invalid input syntax for type double precision: "5. 0"
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3');
+ ERROR: invalid input syntax for type double precision: " - 3"
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5');
+ ERROR: invalid input syntax for type double precision: "123 5"
+ -- special inputs
+ SELECT 'NaN'::float8;
+ float8
+ --------
+ NaN
+ (1 row)
+
+ SELECT 'nan'::float8;
+ float8
+ --------
+ NaN
+ (1 row)
+
+ SELECT ' NAN '::float8;
+ float8
+ --------
+ NaN
+ (1 row)
+
+ -- bad special inputs
+ SELECT 'N A N'::float8;
+ ERROR: invalid input syntax for type double precision: "N A N"
SELECT '' AS five, FLOAT8_TBL.*;
five | f1
------+----------------------
Index: src/test/regress/expected/int2.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/int2.out,v
retrieving revision 1.10
diff -c -r1.10 int2.out
*** a/src/test/regress/expected/int2.out 25 Sep 2003 06:58:06 -0000 1.10
--- b/src/test/regress/expected/int2.out 11 Mar 2004 01:54:13 -0000
***************
*** 4,22 ****
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0');
! INSERT INTO INT2_TBL(f1) VALUES ('1234');
! INSERT INTO INT2_TBL(f1) VALUES ('-1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give warnings
INSERT INTO INT2_TBL(f1) VALUES ('100000');
ERROR: value "100000" is out of range for type shortint
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
SELECT '' AS five, INT2_TBL.*;
five | f1
------+--------
--- 4,32 ----
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0 ');
! INSERT INTO INT2_TBL(f1) VALUES (' 1234 ');
! INSERT INTO INT2_TBL(f1) VALUES (' -1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give errors
INSERT INTO INT2_TBL(f1) VALUES ('100000');
ERROR: value "100000" is out of range for type shortint
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
+ INSERT INTO INT2_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for integer: " "
+ INSERT INTO INT2_TBL(f1) VALUES ('- 1234');
+ ERROR: invalid input syntax for integer: "- 1234"
+ INSERT INTO INT2_TBL(f1) VALUES ('4 444');
+ ERROR: invalid input syntax for integer: "4 444"
+ INSERT INTO INT2_TBL(f1) VALUES ('123 dt');
+ ERROR: invalid input syntax for integer: "123 dt"
+ INSERT INTO INT2_TBL(f1) VALUES ('');
+ ERROR: invalid input syntax for integer: ""
SELECT '' AS five, INT2_TBL.*;
five | f1
------+--------
Index: src/test/regress/expected/int4.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/int4.out,v
retrieving revision 1.12
diff -c -r1.12 int4.out
*** a/src/test/regress/expected/int4.out 25 Sep 2003 06:58:06 -0000 1.12
--- b/src/test/regress/expected/int4.out 11 Mar 2004 01:54:13 -0000
***************
*** 4,22 ****
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES ('0');
! INSERT INTO INT4_TBL(f1) VALUES ('123456');
! INSERT INTO INT4_TBL(f1) VALUES ('-123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give warnings
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
ERROR: value "1000000000000" is out of range for type integer
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
SELECT '' AS five, INT4_TBL.*;
five | f1
------+-------------
--- 4,32 ----
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES (' 0 ');
! INSERT INTO INT4_TBL(f1) VALUES ('123456 ');
! INSERT INTO INT4_TBL(f1) VALUES (' -123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
ERROR: invalid input syntax for integer: "34.5"
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give errors
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
ERROR: value "1000000000000" is out of range for type integer
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
ERROR: invalid input syntax for integer: "asdf"
+ INSERT INTO INT4_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for integer: " "
+ INSERT INTO INT4_TBL(f1) VALUES (' asdf ');
+ ERROR: invalid input syntax for integer: " asdf "
+ INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
+ ERROR: invalid input syntax for integer: "- 1234"
+ INSERT INTO INT4_TBL(f1) VALUES ('123 5');
+ ERROR: invalid input syntax for integer: "123 5"
+ INSERT INTO INT4_TBL(f1) VALUES ('');
+ ERROR: invalid input syntax for integer: ""
SELECT '' AS five, INT4_TBL.*;
five | f1
------+-------------
***************
*** 117,130 ****
| 2147483647
(3 rows)
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
one | f1
-----+------------
| 2147483647
(1 row)
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
three | f1
-------+---------
--- 127,140 ----
| 2147483647
(3 rows)
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
one | f1
-----+------------
| 2147483647
(1 row)
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
three | f1
-------+---------
Index: src/test/regress/expected/int8.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/int8.out,v
retrieving revision 1.7
diff -c -r1.7 int8.out
*** a/src/test/regress/expected/int8.out 27 Mar 2003 16:35:31 -0000 1.7
--- b/src/test/regress/expected/int8.out 11 Mar 2004 01:54:13 -0000
***************
*** 3,13 ****
-- Test int8 64-bit integers.
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES('123','456');
! INSERT INTO INT8_TBL VALUES('123','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
SELECT * FROM INT8_TBL;
q1 | q2
------------------+-------------------
--- 3,28 ----
-- Test int8 64-bit integers.
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES(' 123 ',' 456');
! INSERT INTO INT8_TBL VALUES('123 ','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
+ -- bad inputs
+ INSERT INTO INT8_TBL(q1) VALUES (' ');
+ ERROR: invalid input syntax for type bigint: " "
+ INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+ ERROR: invalid input syntax for type bigint: "xxx"
+ INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
+ ERROR: integer out of range
+ INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
+ ERROR: integer out of range
+ INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+ ERROR: invalid input syntax for type bigint: "- 123"
+ INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
+ ERROR: invalid input syntax for type bigint: " 345 5"
+ INSERT INTO INT8_TBL(q1) VALUES ('');
+ ERROR: invalid input syntax for type bigint: ""
SELECT * FROM INT8_TBL;
q1 | q2
------------------+-------------------
Index: src/test/regress/expected/numeric.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/numeric.out,v
retrieving revision 1.12
diff -c -r1.12 numeric.out
*** a/src/test/regress/expected/numeric.out 25 Sep 2003 06:58:06 -0000 1.12
--- b/src/test/regress/expected/numeric.out 11 Mar 2004 01:54:13 -0000
***************
*** 670,675 ****
--- 670,687 ----
-13430913.592242320700
(1 row)
+ SELECT STDDEV(val) FROM num_data;
+ stddev
+ -------------------------------
+ 27791203.28758835329805617386
+ (1 row)
+
+ SELECT VARIANCE(val) FROM num_data;
+ variance
+ --------------------------------------
+ 772350980172061.69659105821915863601
+ (1 row)
+
-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));
INSERT INTO fract_only VALUES (1, '0.0');
***************
*** 1112,1114 ****
--- 1124,1167 ----
| -0.01
(1 row)
+ --
+ -- Input syntax
+ --
+ CREATE TABLE num_input_test (n1 numeric);
+ -- good inputs
+ INSERT INTO num_input_test(n1) VALUES (' 123');
+ INSERT INTO num_input_test(n1) VALUES (' 3245874 ');
+ INSERT INTO num_input_test(n1) VALUES (' -93853');
+ INSERT INTO num_input_test(n1) VALUES ('555.50');
+ INSERT INTO num_input_test(n1) VALUES ('-555.50');
+ INSERT INTO num_input_test(n1) VALUES ('NaN ');
+ ERROR: invalid input syntax for type numeric: "NaN "
+ INSERT INTO num_input_test(n1) VALUES (' nan');
+ ERROR: invalid input syntax for type numeric: " nan"
+ -- bad inputs
+ INSERT INTO num_input_test(n1) VALUES (' ');
+ ERROR: invalid input syntax for type numeric: " "
+ INSERT INTO num_input_test(n1) VALUES (' 1234 %');
+ ERROR: invalid input syntax for type numeric: " 1234 %"
+ INSERT INTO num_input_test(n1) VALUES ('xyz');
+ ERROR: invalid input syntax for type numeric: "xyz"
+ INSERT INTO num_input_test(n1) VALUES ('- 1234');
+ ERROR: invalid input syntax for type numeric: "- 1234"
+ INSERT INTO num_input_test(n1) VALUES ('5 . 0');
+ ERROR: invalid input syntax for type numeric: "5 . 0"
+ INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
+ ERROR: invalid input syntax for type numeric: "5. 0 "
+ INSERT INTO num_input_test(n1) VALUES ('');
+ ERROR: invalid input syntax for type numeric: ""
+ INSERT INTO num_input_test(n1) VALUES (' N aN ');
+ ERROR: invalid input syntax for type numeric: " N aN "
+ SELECT * FROM num_input_test;
+ n1
+ ---------
+ 123
+ 3245874
+ -93853
+ 555.50
+ -555.50
+ (5 rows)
+
Index: src/test/regress/expected/oid.out
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/oid.out,v
retrieving revision 1.9
diff -c -r1.9 oid.out
*** a/src/test/regress/expected/oid.out 4 Mar 2004 21:47:18 -0000 1.9
--- b/src/test/regress/expected/oid.out 11 Mar 2004 01:54:13 -0000
***************
*** 7,17 ****
--- 7,35 ----
INSERT INTO OID_TBL(f1) VALUES ('987');
INSERT INTO OID_TBL(f1) VALUES ('-1040');
INSERT INTO OID_TBL(f1) VALUES ('99999999');
+ INSERT INTO OID_TBL(f1) VALUES ('5 ');
+ INSERT INTO OID_TBL(f1) VALUES (' 10 ');
+ -- leading/trailing hard tab is also allowed
+ INSERT INTO OID_TBL(f1) VALUES (' 15 ');
-- bad inputs
INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
ERROR: invalid input syntax for type oid: "asdfasd"
INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
ERROR: invalid input syntax for type oid: "99asdfasd"
+ INSERT INTO OID_TBL(f1) VALUES ('5 d');
+ ERROR: invalid input syntax for type oid: "5 d"
+ INSERT INTO OID_TBL(f1) VALUES (' 5d');
+ ERROR: invalid input syntax for type oid: " 5d"
+ INSERT INTO OID_TBL(f1) VALUES ('5 5');
+ ERROR: invalid input syntax for type oid: "5 5"
+ INSERT INTO OID_TBL(f1) VALUES (' ');
+ ERROR: invalid input syntax for type oid: " "
+ INSERT INTO OID_TBL(f1) VALUES (' - 500');
+ ERROR: invalid input syntax for type oid: " - 500"
+ INSERT INTO OID_TBL(f1) VALUES ('32958209582039852935');
+ ERROR: value "32958209582039852935" is out of range for type oid
+ INSERT INTO OID_TBL(f1) VALUES ('-23582358720398502385');
+ ERROR: value "-23582358720398502385" is out of range for type oid
SELECT '' AS six, OID_TBL.*;
six | f1
-----+------------
***************
*** 20,26 ****
| 987
| 4294966256
| 99999999
! (5 rows)
SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234;
one | f1
--- 38,47 ----
| 987
| 4294966256
| 99999999
! | 5
! | 10
! | 15
! (8 rows)
SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234;
one | f1
***************
*** 35,54 ****
| 987
| 4294966256
| 99999999
! (4 rows)
SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 <= '1234';
three | f1
-------+------
| 1234
| 987
! (2 rows)
SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 < '1234';
two | f1
-----+-----
| 987
! (1 row)
SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234';
four | f1
--- 56,84 ----
| 987
| 4294966256
| 99999999
! | 5
! | 10
! | 15
! (7 rows)
SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 <= '1234';
three | f1
-------+------
| 1234
| 987
! | 5
! | 10
! | 15
! (5 rows)
SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 < '1234';
two | f1
-----+-----
| 987
! | 5
! | 10
! | 15
! (4 rows)
SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234';
four | f1
Index: src/test/regress/output/misc.source
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/output/misc.source,v
retrieving revision 1.39
diff -c -r1.39 misc.source
*** a/src/test/regress/output/misc.source 20 Jul 2003 21:56:35 -0000 1.39
--- b/src/test/regress/output/misc.source 11 Mar 2004 01:54:13 -0000
***************
*** 631,636 ****
--- 631,637 ----
num_exp_power_10_ln
num_exp_sqrt
num_exp_sub
+ num_input_test
num_result
onek
onek2
***************
*** 660,666 ****
toyemp
varchar_tbl
xacttest
! (96 rows)
--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
SELECT hobbies_by_name('basketball');
--- 661,667 ----
toyemp
varchar_tbl
xacttest
! (97 rows)
--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
SELECT hobbies_by_name('basketball');
Index: src/test/regress/sql/float4.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/float4.sql,v
retrieving revision 1.4
diff -c -r1.4 float4.sql
*** a/src/test/regress/sql/float4.sql 4 Jan 2000 16:21:02 -0000 1.4
--- b/src/test/regress/sql/float4.sql 11 Mar 2004 01:54:13 -0000
***************
*** 4,28 ****
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0');
!
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30');
!
! INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84');
!
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
-
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
SELECT '' AS five, FLOAT4_TBL.*;
--- 4,36 ----
CREATE TABLE FLOAT4_TBL (f1 float4);
! INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0');
! INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 ');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
-- test for over and under flow
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40');
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40');
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
+ -- bad input
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' ');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0');
+ INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5');
+
+ -- special inputs
+ SELECT 'NaN'::float4;
+ SELECT 'nan'::float4;
+ SELECT ' NAN '::float4;
+ -- bad special inputs
+ SELECT 'N A N'::float4;
SELECT '' AS five, FLOAT4_TBL.*;
Index: src/test/regress/sql/float8.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/float8.sql,v
retrieving revision 1.8
diff -c -r1.8 float8.sql
*** a/src/test/regress/sql/float8.sql 19 Oct 2002 02:08:19 -0000 1.8
--- b/src/test/regress/sql/float8.sql 11 Mar 2004 01:54:13 -0000
***************
*** 4,19 ****
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
!
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
!
! INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
!
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
-
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
SELECT '' AS five, FLOAT8_TBL.*;
--- 4,36 ----
CREATE TABLE FLOAT8_TBL(f1 float8);
! INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 ');
! INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
+ -- test for underflow and overflow
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
+
+ -- bad input
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' ');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0');
+ INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3');
+ INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5');
+
+ -- special inputs
+ SELECT 'NaN'::float8;
+ SELECT 'nan'::float8;
+ SELECT ' NAN '::float8;
+ -- bad special inputs
+ SELECT 'N A N'::float8;
SELECT '' AS five, FLOAT8_TBL.*;
Index: src/test/regress/sql/int2.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/int2.sql,v
retrieving revision 1.4
diff -c -r1.4 int2.sql
*** a/src/test/regress/sql/int2.sql 4 Jan 2000 16:21:02 -0000 1.4
--- b/src/test/regress/sql/int2.sql 11 Mar 2004 01:54:13 -0000
***************
*** 6,28 ****
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0');
! INSERT INTO INT2_TBL(f1) VALUES ('1234');
! INSERT INTO INT2_TBL(f1) VALUES ('-1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give warnings
INSERT INTO INT2_TBL(f1) VALUES ('100000');
-
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
SELECT '' AS five, INT2_TBL.*;
--- 6,32 ----
CREATE TABLE INT2_TBL(f1 int2);
! INSERT INTO INT2_TBL(f1) VALUES ('0 ');
! INSERT INTO INT2_TBL(f1) VALUES (' 1234 ');
! INSERT INTO INT2_TBL(f1) VALUES (' -1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
! -- bad input values -- should give errors
INSERT INTO INT2_TBL(f1) VALUES ('100000');
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
+ INSERT INTO INT2_TBL(f1) VALUES (' ');
+ INSERT INTO INT2_TBL(f1) VALUES ('- 1234');
+ INSERT INTO INT2_TBL(f1) VALUES ('4 444');
+ INSERT INTO INT2_TBL(f1) VALUES ('123 dt');
+ INSERT INTO INT2_TBL(f1) VALUES ('');
SELECT '' AS five, INT2_TBL.*;
Index: src/test/regress/sql/int4.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/int4.sql,v
retrieving revision 1.6
diff -c -r1.6 int4.sql
*** a/src/test/regress/sql/int4.sql 14 Mar 2000 23:06:58 -0000 1.6
--- b/src/test/regress/sql/int4.sql 11 Mar 2004 01:54:13 -0000
***************
*** 6,28 ****
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES ('0');
! INSERT INTO INT4_TBL(f1) VALUES ('123456');
! INSERT INTO INT4_TBL(f1) VALUES ('-123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give warnings
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
-
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
SELECT '' AS five, INT4_TBL.*;
--- 6,32 ----
CREATE TABLE INT4_TBL(f1 int4);
! INSERT INTO INT4_TBL(f1) VALUES (' 0 ');
! INSERT INTO INT4_TBL(f1) VALUES ('123456 ');
! INSERT INTO INT4_TBL(f1) VALUES (' -123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
! -- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
! -- bad input values -- should give errors
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
+ INSERT INTO INT4_TBL(f1) VALUES (' ');
+ INSERT INTO INT4_TBL(f1) VALUES (' asdf ');
+ INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
+ INSERT INTO INT4_TBL(f1) VALUES ('123 5');
+ INSERT INTO INT4_TBL(f1) VALUES ('');
SELECT '' AS five, INT4_TBL.*;
***************
*** 51,60 ****
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i;
--- 55,64 ----
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
! -- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
! -- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i;
Index: src/test/regress/sql/int8.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/int8.sql,v
retrieving revision 1.5
diff -c -r1.5 int8.sql
*** a/src/test/regress/sql/int8.sql 26 Jan 2001 22:50:26 -0000 1.5
--- b/src/test/regress/sql/int8.sql 11 Mar 2004 01:54:13 -0000
***************
*** 4,15 ****
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES('123','456');
! INSERT INTO INT8_TBL VALUES('123','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
SELECT * FROM INT8_TBL;
SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;
--- 4,24 ----
--
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
! INSERT INTO INT8_TBL VALUES(' 123 ',' 456');
! INSERT INTO INT8_TBL VALUES('123 ','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
+ -- bad inputs
+ INSERT INTO INT8_TBL(q1) VALUES (' ');
+ INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+ INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
+ INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
+ INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+ INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
+ INSERT INTO INT8_TBL(q1) VALUES ('');
+
SELECT * FROM INT8_TBL;
SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;
Index: src/test/regress/sql/numeric.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/numeric.sql,v
retrieving revision 1.7
diff -c -r1.7 numeric.sql
*** a/src/test/regress/sql/numeric.sql 7 Apr 2000 19:17:50 -0000 1.7
--- b/src/test/regress/sql/numeric.sql 11 Mar 2004 01:54:13 -0000
***************
*** 639,644 ****
--- 639,646 ----
-- ******************************
-- numeric AVG used to fail on some platforms
SELECT AVG(val) FROM num_data;
+ SELECT STDDEV(val) FROM num_data;
+ SELECT VARIANCE(val) FROM num_data;
-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));
***************
*** 701,703 ****
--- 703,732 ----
SELECT '' AS to_number_11, to_number('.-01', 'S99.99');
SELECT '' AS to_number_12, to_number('.01-', '99.99S');
SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S');
+
+ --
+ -- Input syntax
+ --
+
+ CREATE TABLE num_input_test (n1 numeric);
+
+ -- good inputs
+ INSERT INTO num_input_test(n1) VALUES (' 123');
+ INSERT INTO num_input_test(n1) VALUES (' 3245874 ');
+ INSERT INTO num_input_test(n1) VALUES (' -93853');
+ INSERT INTO num_input_test(n1) VALUES ('555.50');
+ INSERT INTO num_input_test(n1) VALUES ('-555.50');
+ INSERT INTO num_input_test(n1) VALUES ('NaN ');
+ INSERT INTO num_input_test(n1) VALUES (' nan');
+
+ -- bad inputs
+ INSERT INTO num_input_test(n1) VALUES (' ');
+ INSERT INTO num_input_test(n1) VALUES (' 1234 %');
+ INSERT INTO num_input_test(n1) VALUES ('xyz');
+ INSERT INTO num_input_test(n1) VALUES ('- 1234');
+ INSERT INTO num_input_test(n1) VALUES ('5 . 0');
+ INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
+ INSERT INTO num_input_test(n1) VALUES ('');
+ INSERT INTO num_input_test(n1) VALUES (' N aN ');
+
+ SELECT * FROM num_input_test;
Index: src/test/regress/sql/oid.sql
===================================================================
RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/oid.sql,v
retrieving revision 1.5
diff -c -r1.5 oid.sql
*** a/src/test/regress/sql/oid.sql 4 Mar 2004 21:47:18 -0000 1.5
--- b/src/test/regress/sql/oid.sql 11 Mar 2004 01:54:13 -0000
***************
*** 5,23 ****
CREATE TABLE OID_TBL(f1 oid);
INSERT INTO OID_TBL(f1) VALUES ('1234');
-
INSERT INTO OID_TBL(f1) VALUES ('1235');
-
INSERT INTO OID_TBL(f1) VALUES ('987');
-
INSERT INTO OID_TBL(f1) VALUES ('-1040');
-
INSERT INTO OID_TBL(f1) VALUES ('99999999');
-- bad inputs
INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
SELECT '' AS six, OID_TBL.*;
--- 5,30 ----
CREATE TABLE OID_TBL(f1 oid);
INSERT INTO OID_TBL(f1) VALUES ('1234');
INSERT INTO OID_TBL(f1) VALUES ('1235');
INSERT INTO OID_TBL(f1) VALUES ('987');
INSERT INTO OID_TBL(f1) VALUES ('-1040');
INSERT INTO OID_TBL(f1) VALUES ('99999999');
+ INSERT INTO OID_TBL(f1) VALUES ('5 ');
+ INSERT INTO OID_TBL(f1) VALUES (' 10 ');
+ -- leading/trailing hard tab is also allowed
+ INSERT INTO OID_TBL(f1) VALUES (' 15 ');
-- bad inputs
INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
INSERT INTO OID_TBL(f1) VALUES ('99asdfasd');
+ INSERT INTO OID_TBL(f1) VALUES ('5 d');
+ INSERT INTO OID_TBL(f1) VALUES (' 5d');
+ INSERT INTO OID_TBL(f1) VALUES ('5 5');
+ INSERT INTO OID_TBL(f1) VALUES (' ');
+ INSERT INTO OID_TBL(f1) VALUES (' - 500');
+ INSERT INTO OID_TBL(f1) VALUES ('32958209582039852935');
+ INSERT INTO OID_TBL(f1) VALUES ('-23582358720398502385');
SELECT '' AS six, OID_TBL.*;
Neil Conway <neilc@samurai.com> writes:
> The attached patch changes the rules for input to
> int2/int4/int8/float4/float8 to consistently allow leading and
> trailing whitespace in the input.
BTW, did you look at type "numeric"? Or is that okay already?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > BTW, did you look at type "numeric"? Or is that okay already? That was already okay AFAIK. I also added some regression tests to verify that numeric accepts leading and trailing whitespace properly. -Neil