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.*;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
