aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/float.c121
-rw-r--r--src/backend/utils/adt/int8.c9
-rw-r--r--src/backend/utils/adt/numutils.c31
-rw-r--r--src/backend/utils/adt/oid.c30
-rw-r--r--src/test/regress/expected/float4.out43
-rw-r--r--src/test/regress/expected/float8.out52
-rw-r--r--src/test/regress/expected/int2.out20
-rw-r--r--src/test/regress/expected/int4.out24
-rw-r--r--src/test/regress/expected/int8.out19
-rw-r--r--src/test/regress/expected/numeric.out53
-rw-r--r--src/test/regress/expected/oid.out38
-rw-r--r--src/test/regress/output/misc.source3
-rw-r--r--src/test/regress/sql/float4.sql28
-rw-r--r--src/test/regress/sql/float8.sql31
-rw-r--r--src/test/regress/sql/int2.sql16
-rw-r--r--src/test/regress/sql/int4.sql20
-rw-r--r--src/test/regress/sql/int8.sql13
-rw-r--r--src/test/regress/sql/numeric.sql29
-rw-r--r--src/test/regress/sql/oid.sql15
19 files changed, 457 insertions, 138 deletions
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index f9c4894794f..2707c83fd62 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.97 2004/03/04 21:47:18 neilc Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.98 2004/03/11 02:11:13 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -185,14 +185,37 @@ float4in(PG_FUNCTION_ARGS)
errno = 0;
val = strtod(num, &endptr);
- if (*endptr != '\0')
+
+ 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)
{
/*
- * XXX we should accept "Infinity" and "-Infinity" too, but what
- * are the correct values to assign? HUGE_VAL will provoke an
- * error from CheckFloat4Val.
+ * 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 (strcasecmp(num, "NaN") == 0)
+ 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,26 +223,17 @@ float4in(PG_FUNCTION_ARGS)
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.")));
+ /* 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,9 +314,33 @@ float8in(PG_FUNCTION_ARGS)
errno = 0;
val = strtod(num, &endptr);
- if (*endptr != '\0')
+
+ 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)
{
- if (strcasecmp(num, "NaN") == 0)
+ /*
+ * 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,26 +352,17 @@ float8in(PG_FUNCTION_ARGS)
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.")));
+ /* 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);
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 200876e7989..8667e53680a 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -7,7 +7,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/utils/adt/int8.c,v 1.51 2004/02/03 08:29:56 joe Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/int8.c,v 1.52 2004/03/11 02:11:13 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -113,8 +113,11 @@ scanint8(const char *str, bool errorOK, int64 *result)
tmp = newtmp;
}
- /* trailing junk? */
- if (*ptr)
+ /* allow trailing whitespace, but not other trailing chars */
+ while (*ptr != '\0' && isspace(*ptr))
+ ptr++;
+
+ if (*ptr != '\0')
{
if (errorOK)
return false;
diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index 20227884ed1..17961017faf 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -10,7 +10,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/utils/adt/numutils.c,v 1.61 2004/02/18 00:01:33 neilc Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/numutils.c,v 1.62 2004/03/11 02:11:13 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -19,6 +19,7 @@
#include <errno.h>
#include <math.h>
#include <limits.h>
+#include <ctype.h>
#include "utils/builtins.h"
@@ -45,10 +46,12 @@
/*
* pg_atoi: convert string to integer
*
- * size is the sizeof() the desired integral result (1, 2, or 4 bytes).
+ * '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.
+ * 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,7 +60,7 @@ int32
pg_atoi(char *s, int size, int c)
{
long l;
- char *badp = NULL;
+ char *badp;
/*
* Some versions of strtol treat the empty string as an error, but
@@ -74,17 +77,21 @@ pg_atoi(char *s, int size, int c)
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)
+ /* 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)));
- if (badp && *badp && *badp != c)
+
+ /*
+ * 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\"",
diff --git a/src/backend/utils/adt/oid.c b/src/backend/utils/adt/oid.c
index 2119936d39f..7ff6c6a27c1 100644
--- a/src/backend/utils/adt/oid.c
+++ b/src/backend/utils/adt/oid.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/utils/adt/oid.c,v 1.55 2004/03/04 21:47:18 neilc Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/oid.c,v 1.56 2004/03/11 02:11:13 neilc Exp $
*
*-------------------------------------------------------------------------
*/
@@ -33,6 +33,19 @@ oidin_subr(const char *funcname, const char *s, char **endloc)
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,20 +60,7 @@ oidin_subr(const char *funcname, const char *s, char **endloc)
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)
+ if (endptr == s && *s != '\0')
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for type oid: \"%s\"",
diff --git a/src/test/regress/expected/float4.out b/src/test/regress/expected/float4.out
index 118a3fb66cc..124b7c378c6 100644
--- a/src/test/regress/expected/float4.out
+++ b/src/test/regress/expected/float4.out
@@ -2,9 +2,9 @@
-- 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 (' 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,6 +16,43 @@ INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
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
------+-------------
diff --git a/src/test/regress/expected/float8.out b/src/test/regress/expected/float8.out
index 3d8bc7710b2..89e2bbf9025 100644
--- a/src/test/regress/expected/float8.out
+++ b/src/test/regress/expected/float8.out
@@ -2,11 +2,57 @@
-- 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 (' 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
------+----------------------
diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out
index 6da403c3e7a..64e5bf58a8c 100644
--- a/src/test/regress/expected/int2.out
+++ b/src/test/regress/expected/int2.out
@@ -4,19 +4,29 @@
-- 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 ('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
+-- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
--- bad input values -- should give warnings
+-- 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
------+--------
diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out
index 633eb578d51..e5d930e31e0 100644
--- a/src/test/regress/expected/int4.out
+++ b/src/test/regress/expected/int4.out
@@ -4,19 +4,29 @@
-- 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 (' 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
+-- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
--- bad input values -- should give warnings
+-- 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,14 +127,14 @@ SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
| 2147483647
(3 rows)
--- positive odds
+-- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
one | f1
-----+------------
| 2147483647
(1 row)
--- any evens
+-- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
three | f1
-------+---------
diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out
index 931889c4ad9..7172cc1071a 100644
--- a/src/test/regress/expected/int8.out
+++ b/src/test/regress/expected/int8.out
@@ -3,11 +3,26 @@
-- 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(' 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
------------------+-------------------
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index e1c7361b64a..bf3bb441afc 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -670,6 +670,18 @@ SELECT AVG(val) FROM num_data;
-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,3 +1124,44 @@ SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S');
| -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)
+
diff --git a/src/test/regress/expected/oid.out b/src/test/regress/expected/oid.out
index 570a4c77b36..092c9b1cfaf 100644
--- a/src/test/regress/expected/oid.out
+++ b/src/test/regress/expected/oid.out
@@ -7,11 +7,29 @@ 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');
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,7 +38,10 @@ SELECT '' AS six, OID_TBL.*;
| 987
| 4294966256
| 99999999
-(5 rows)
+ | 5
+ | 10
+ | 15
+(8 rows)
SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234;
one | f1
@@ -35,20 +56,29 @@ SELECT '' AS five, o.* FROM OID_TBL o WHERE o.f1 <> '1234';
| 987
| 4294966256
| 99999999
-(4 rows)
+ | 5
+ | 10
+ | 15
+(7 rows)
SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 <= '1234';
three | f1
-------+------
| 1234
| 987
-(2 rows)
+ | 5
+ | 10
+ | 15
+(5 rows)
SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 < '1234';
two | f1
-----+-----
| 987
-(1 row)
+ | 5
+ | 10
+ | 15
+(4 rows)
SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234';
four | f1
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 557c0f5f9d9..c478dd71897 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -631,6 +631,7 @@ SELECT user_relns() AS user_relns
num_exp_power_10_ln
num_exp_sqrt
num_exp_sub
+ num_input_test
num_result
onek
onek2
@@ -660,7 +661,7 @@ SELECT user_relns() AS user_relns
toyemp
varchar_tbl
xacttest
-(96 rows)
+(97 rows)
--SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
SELECT hobbies_by_name('basketball');
diff --git a/src/test/regress/sql/float4.sql b/src/test/regress/sql/float4.sql
index 28c12c6fa45..b7b64f2e50e 100644
--- a/src/test/regress/sql/float4.sql
+++ b/src/test/regress/sql/float4.sql
@@ -4,25 +4,33 @@
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 (' 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.*;
diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql
index 2cdb64a75a9..1e5e8ad4302 100644
--- a/src/test/regress/sql/float8.sql
+++ b/src/test/regress/sql/float8.sql
@@ -4,16 +4,33 @@
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 (' 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.*;
diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql
index 56cbb4c7c9c..e42b4236fdc 100644
--- a/src/test/regress/sql/int2.sql
+++ b/src/test/regress/sql/int2.sql
@@ -6,23 +6,27 @@
CREATE TABLE INT2_TBL(f1 int2);
-INSERT INTO INT2_TBL(f1) VALUES ('0');
+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 ('-1234');
+INSERT INTO INT2_TBL(f1) VALUES (' -1234');
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
--- largest and smallest values
+-- largest and smallest values
INSERT INTO INT2_TBL(f1) VALUES ('32767');
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
--- bad input values -- should give warnings
+-- 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.*;
diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql
index 8663e284fbf..85b22ccd0ab 100644
--- a/src/test/regress/sql/int4.sql
+++ b/src/test/regress/sql/int4.sql
@@ -6,23 +6,27 @@
CREATE TABLE INT4_TBL(f1 int4);
-INSERT INTO INT4_TBL(f1) VALUES ('0');
+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 ('-123456');
+INSERT INTO INT4_TBL(f1) VALUES (' -123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
--- largest and smallest values
+-- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
--- bad input values -- should give warnings
+-- 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,10 +55,10 @@ SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0';
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
--- positive odds
+-- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
--- any evens
+-- 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;
diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql
index b5cb13480be..98b1606430d 100644
--- a/src/test/regress/sql/int8.sql
+++ b/src/test/regress/sql/int8.sql
@@ -4,12 +4,21 @@
--
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(' 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;
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 4ded6607bde..b3900344027 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -639,6 +639,8 @@ SELECT t1.id1, t1.result, t2.expected
-- ******************************
-- 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,3 +703,30 @@ SELECT '' AS to_number_10, to_number('0', '99.99');
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;
diff --git a/src/test/regress/sql/oid.sql b/src/test/regress/sql/oid.sql
index a19f8fe998f..a3ed212b6ad 100644
--- a/src/test/regress/sql/oid.sql
+++ b/src/test/regress/sql/oid.sql
@@ -5,19 +5,26 @@
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.*;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-17 04:29:22 +0000