Thread: numeric input changes

numeric input changes

From
Neil Conway
Date:
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.*;


Re: numeric input changes

From
Rod Taylor
Date:
> 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.



Re: numeric input changes

From
Tom Lane
Date:
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

Re: numeric input changes

From
Dennis Bjorklund
Date:
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


Re: numeric input changes

From
Tom Lane
Date:
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

Re: numeric input changes

From
Neil Conway
Date:
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


Re: numeric input changes

From
Neil Conway
Date:
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.*;


Re: numeric input changes

From
Tom Lane
Date:
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

Re: numeric input changes

From
Neil Conway
Date:
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