Re: Infinities in type numeric - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: Infinities in type numeric |
| Date | |
| Msg-id | 946739.1592080406@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: Infinities in type numeric (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
Here's a v2 patch:
* Rebased over today's nearby commits
* Documentation changes added
* Sort abbrev support improved per Andrew's suggestions
* Infinities now considered to fail any typmod precision limit,
per discussion with Robert.
regards, tom lane
diff --git a/contrib/jsonb_plperl/jsonb_plperl.c b/contrib/jsonb_plperl/jsonb_plperl.c
index ed361efbe2..b81ba54b80 100644
--- a/contrib/jsonb_plperl/jsonb_plperl.c
+++ b/contrib/jsonb_plperl/jsonb_plperl.c
@@ -227,10 +227,8 @@ SV_to_JsonbValue(SV *in, JsonbParseState **jsonb_state, bool is_elem)
/*
* jsonb doesn't allow infinity or NaN (per JSON
* specification), but the numeric type that is used for the
- * storage accepts NaN, so we have to prevent it here
- * explicitly. We don't really have to check for isinf()
- * here, as numeric doesn't allow it and it would be caught
- * later, but it makes for a nicer error message.
+ * storage accepts those, so we have to reject them here
+ * explicitly.
*/
if (isinf(nval))
ereport(ERROR,
diff --git a/contrib/jsonb_plpython/jsonb_plpython.c b/contrib/jsonb_plpython/jsonb_plpython.c
index e09308daf0..836c178770 100644
--- a/contrib/jsonb_plpython/jsonb_plpython.c
+++ b/contrib/jsonb_plpython/jsonb_plpython.c
@@ -387,14 +387,17 @@ PLyNumber_ToJsonbValue(PyObject *obj, JsonbValue *jbvNum)
pfree(str);
/*
- * jsonb doesn't allow NaN (per JSON specification), so we have to prevent
- * it here explicitly. (Infinity is also not allowed in jsonb, but
- * numeric_in above already catches that.)
+ * jsonb doesn't allow NaN or infinity (per JSON specification), so we
+ * have to reject those here explicitly.
*/
if (numeric_is_nan(num))
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("cannot convert NaN to jsonb")));
+ if (numeric_is_inf(num))
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("cannot convert infinity to jsonb")));
jbvNum->type = jbvNumeric;
jbvNum->val.numeric = num;
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 3df189ad85..a9ed269e15 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -554,9 +554,9 @@ NUMERIC(<replaceable>precision</replaceable>)
<programlisting>
NUMERIC
</programlisting>
- without any precision or scale creates a column in which numeric
- values of any precision and scale can be stored, up to the
- implementation limit on precision. A column of this kind will
+ without any precision or scale creates an <quote>unconstrained
+ numeric</quote> column in which numeric values of any length can be
+ stored, up to the implementation limits. A column of this kind will
not coerce input values to any particular scale, whereas
<type>numeric</type> columns with a declared scale will coerce
input values to that scale. (The <acronym>SQL</acronym> standard
@@ -568,10 +568,10 @@ NUMERIC
<note>
<para>
- The maximum allowed precision when explicitly specified in the
- type declaration is 1000; <type>NUMERIC</type> without a specified
- precision is subject to the limits described in <xref
- linkend="datatype-numeric-table"/>.
+ The maximum precision that can be explicitly specified in
+ a <type>NUMERIC</type> type declaration is 1000. An
+ unconstrained <type>NUMERIC</type> column is subject to the limits
+ described in <xref linkend="datatype-numeric-table"/>.
</para>
</note>
@@ -593,6 +593,11 @@ NUMERIC
plus three to eight bytes overhead.
</para>
+ <indexterm>
+ <primary>infinity</primary>
+ <secondary>numeric (data type)</secondary>
+ </indexterm>
+
<indexterm>
<primary>NaN</primary>
<see>not a number</see>
@@ -604,13 +609,39 @@ NUMERIC
</indexterm>
<para>
- In addition to ordinary numeric values, the <type>numeric</type>
- type allows the special value <literal>NaN</literal>, meaning
- <quote>not-a-number</quote>. Any operation on <literal>NaN</literal>
- yields another <literal>NaN</literal>. When writing this value
- as a constant in an SQL command, you must put quotes around it,
- for example <literal>UPDATE table SET x = 'NaN'</literal>. On input,
- the string <literal>NaN</literal> is recognized in a case-insensitive manner.
+ In addition to ordinary numeric values, the <type>numeric</type> type
+ has several special values:
+<literallayout>
+<literal>Infinity</literal>
+<literal>-Infinity</literal>
+<literal>NaN</literal>
+</literallayout>
+ These are adapted from the IEEE 754 standard, and represent
+ <quote>infinity</quote>, <quote>negative infinity</quote>, and
+ <quote>not-a-number</quote>, respectively. When writing these values
+ as constants in an SQL command, you must put quotes around them,
+ for example <literal>UPDATE table SET x = '-Infinity'</literal>.
+ On input, these strings are recognized in a case-insensitive manner.
+ The infinity values can alternatively be spelled <literal>inf</literal>
+ and <literal>-inf</literal>.
+ </para>
+
+ <para>
+ The infinity values behave as per mathematical expectations. For
+ example, <literal>Infinity</literal> plus any finite value equals
+ <literal>Infinity</literal>, as does <literal>Infinity</literal>
+ plus <literal>Infinity</literal>; but <literal>Infinity</literal>
+ minus <literal>Infinity</literal> yields <literal>NaN</literal> (not a
+ number), because it has no well-defined interpretation. Note that an
+ infinity can only be stored in an unconstrained <type>numeric</type>
+ column, because it notionally exceeds any finite precision limit.
+ </para>
+
+ <para>
+ The <literal>NaN</literal> (not a number) value is generally used to
+ represent undefined calculational results. With very few exceptions,
+ any operation with a <literal>NaN</literal> input yields
+ another <literal>NaN</literal>.
</para>
<note>
@@ -781,9 +812,14 @@ FROM generate_series(-3.5, 3.5, 1) as x;
</para>
</note>
+ <indexterm>
+ <primary>infinity</primary>
+ <secondary>floating point</secondary>
+ </indexterm>
+
<indexterm>
<primary>not a number</primary>
- <secondary>double precision</secondary>
+ <secondary>floating point</secondary>
</indexterm>
<para>
@@ -800,11 +836,13 @@ FROM generate_series(-3.5, 3.5, 1) as x;
as constants in an SQL command, you must put quotes around them,
for example <literal>UPDATE table SET x = '-Infinity'</literal>. On input,
these strings are recognized in a case-insensitive manner.
+ The infinity values can alternatively be spelled <literal>inf</literal>
+ and <literal>-inf</literal>.
</para>
<note>
<para>
- IEEE754 specifies that <literal>NaN</literal> should not compare equal
+ IEEE 754 specifies that <literal>NaN</literal> should not compare equal
to any other floating-point value (including <literal>NaN</literal>).
In order to allow floating-point values to be sorted and used
in tree-based indexes, <productname>PostgreSQL</productname> treats
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 16768b28c3..6626438136 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -6129,9 +6129,12 @@ numeric_to_char(PG_FUNCTION_ARGS)
/*
* numeric_out_sci() does not emit a sign for positive numbers. We
* need to add a space in this case so that positive and negative
- * numbers are aligned. We also have to do the right thing for NaN.
+ * numbers are aligned. Also must check for NaN/infinity cases, which
+ * we handle the same way as in float8_to_char.
*/
- if (strcmp(orgnum, "NaN") == 0)
+ if (strcmp(orgnum, "NaN") == 0 ||
+ strcmp(orgnum, "Infinity") == 0 ||
+ strcmp(orgnum, "-Infinity") == 0)
{
/*
* Allow 6 characters for the leading sign, the decimal point,
@@ -6346,7 +6349,7 @@ int8_to_char(PG_FUNCTION_ARGS)
/*
* numeric_out_sci() does not emit a sign for positive numbers. We
* need to add a space in this case so that positive and negative
- * numbers are aligned. We don't have to worry about NaN here.
+ * numbers are aligned. We don't have to worry about NaN/inf here.
*/
if (*orgnum != '-')
{
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index eea4239854..011b6b13a6 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -108,14 +108,13 @@ typedef int16 NumericDigit;
* If the high bits of the first word of a NumericChoice (n_header, or
* n_short.n_header, or n_long.n_sign_dscale) are NUMERIC_SHORT, then the
* numeric follows the NumericShort format; if they are NUMERIC_POS or
- * NUMERIC_NEG, it follows the NumericLong format. If they are NUMERIC_NAN,
- * it is a NaN. We currently always store a NaN using just two bytes (i.e.
- * only n_header), but previous releases used only the NumericLong format,
- * so we might find 4-byte NaNs on disk if a database has been migrated using
- * pg_upgrade. In either case, when the high bits indicate a NaN, the
- * remaining bits are never examined. Currently, we always initialize these
- * to zero, but it might be possible to use them for some other purpose in
- * the future.
+ * NUMERIC_NEG, it follows the NumericLong format. If they are NUMERIC_SPECIAL,
+ * the value is a NaN or Infinity. We currently always store SPECIAL values
+ * using just two bytes (i.e. only n_header), but previous releases used only
+ * the NumericLong format, so we might find 4-byte NaNs (though not infinities)
+ * on disk if a database has been migrated using pg_upgrade. In either case,
+ * the low-order bits of a special value's header are reserved and currently
+ * should always be set to zero.
*
* In the NumericShort format, the remaining 14 bits of the header word
* (n_short.n_header) are allocated as follows: 1 for sign (positive or
@@ -167,25 +166,47 @@ struct NumericData
#define NUMERIC_POS 0x0000
#define NUMERIC_NEG 0x4000
#define NUMERIC_SHORT 0x8000
-#define NUMERIC_NAN 0xC000
+#define NUMERIC_SPECIAL 0xC000
#define NUMERIC_FLAGBITS(n) ((n)->choice.n_header & NUMERIC_SIGN_MASK)
-#define NUMERIC_IS_NAN(n) (NUMERIC_FLAGBITS(n) == NUMERIC_NAN)
#define NUMERIC_IS_SHORT(n) (NUMERIC_FLAGBITS(n) == NUMERIC_SHORT)
+#define NUMERIC_IS_SPECIAL(n) (NUMERIC_FLAGBITS(n) == NUMERIC_SPECIAL)
#define NUMERIC_HDRSZ (VARHDRSZ + sizeof(uint16) + sizeof(int16))
#define NUMERIC_HDRSZ_SHORT (VARHDRSZ + sizeof(uint16))
/*
- * If the flag bits are NUMERIC_SHORT or NUMERIC_NAN, we want the short header;
- * otherwise, we want the long one. Instead of testing against each value, we
- * can just look at the high bit, for a slight efficiency gain.
+ * If the flag bits are NUMERIC_SHORT or NUMERIC_SPECIAL, we want the short
+ * header; otherwise, we want the long one. Instead of testing against each
+ * value, we can just look at the high bit, for a slight efficiency gain.
*/
#define NUMERIC_HEADER_IS_SHORT(n) (((n)->choice.n_header & 0x8000) != 0)
#define NUMERIC_HEADER_SIZE(n) \
(VARHDRSZ + sizeof(uint16) + \
(NUMERIC_HEADER_IS_SHORT(n) ? 0 : sizeof(int16)))
+/*
+ * Definitions for special values (NaN, positive infinity, negative infinity).
+ *
+ * The two bits after the NUMERIC_SPECIAL bits are 00 for NaN, 01 for positive
+ * infinity, 11 for negative infinity. (This makes the sign bit match where
+ * it is in a short-format value, though we make no use of that at present.)
+ * We could mask off the remaining bits before testing the active bits, but
+ * currently those bits must be zeroes, so masking would just add cycles.
+ */
+#define NUMERIC_EXT_SIGN_MASK 0xF000 /* high bits plus NaN/Inf flag bits */
+#define NUMERIC_NAN 0xC000
+#define NUMERIC_PINF 0xD000
+#define NUMERIC_NINF 0xF000
+#define NUMERIC_INF_SIGN_MASK 0x2000
+
+#define NUMERIC_EXT_FLAGBITS(n) ((n)->choice.n_header & NUMERIC_EXT_SIGN_MASK)
+#define NUMERIC_IS_NAN(n) ((n)->choice.n_header == NUMERIC_NAN)
+#define NUMERIC_IS_PINF(n) ((n)->choice.n_header == NUMERIC_PINF)
+#define NUMERIC_IS_NINF(n) ((n)->choice.n_header == NUMERIC_NINF)
+#define NUMERIC_IS_INF(n) \
+ (((n)->choice.n_header & ~NUMERIC_INF_SIGN_MASK) == NUMERIC_PINF)
+
/*
* Short format definitions.
*/
@@ -201,7 +222,13 @@ struct NumericData
#define NUMERIC_SHORT_WEIGHT_MIN (-(NUMERIC_SHORT_WEIGHT_MASK+1))
/*
- * Extract sign, display scale, weight.
+ * Extract sign, display scale, weight. These macros extract field values
+ * suitable for the NumericVar format from the Numeric (on-disk) format.
+ *
+ * Note that we don't trouble to ensure that dscale and weight read as zero
+ * for an infinity; however, that doesn't matter since we never convert
+ * "special" numerics to NumericVar form. Only the constants defined below
+ * (const_nan, etc) ever represent a non-finite value as a NumericVar.
*/
#define NUMERIC_DSCALE_MASK 0x3FFF
@@ -209,7 +236,9 @@ struct NumericData
#define NUMERIC_SIGN(n) \
(NUMERIC_IS_SHORT(n) ? \
(((n)->choice.n_short.n_header & NUMERIC_SHORT_SIGN_MASK) ? \
- NUMERIC_NEG : NUMERIC_POS) : NUMERIC_FLAGBITS(n))
+ NUMERIC_NEG : NUMERIC_POS) : \
+ (NUMERIC_IS_SPECIAL(n) ? \
+ NUMERIC_EXT_FLAGBITS(n) : NUMERIC_FLAGBITS(n)))
#define NUMERIC_DSCALE(n) (NUMERIC_HEADER_IS_SHORT((n)) ? \
((n)->choice.n_short.n_header & NUMERIC_SHORT_DSCALE_MASK) \
>> NUMERIC_SHORT_DSCALE_SHIFT \
@@ -226,7 +255,9 @@ struct NumericData
* complex.
*
* The value represented by a NumericVar is determined by the sign, weight,
- * ndigits, and digits[] array.
+ * ndigits, and digits[] array. If it is a "special" value (NaN or Inf)
+ * then only the sign field matters; ndigits should be zero, and the weight
+ * and dscale fields are ignored.
*
* Note: the first digit of a NumericVar's value is assumed to be multiplied
* by NBASE ** weight. Another way to say it is that there are weight+1
@@ -273,7 +304,7 @@ typedef struct NumericVar
{
int ndigits; /* # of digits in digits[] - can be 0! */
int weight; /* weight of first digit */
- int sign; /* NUMERIC_POS, NUMERIC_NEG, or NUMERIC_NAN */
+ int sign; /* NUMERIC_POS, _NEG, _NAN, _PINF, or _NINF */
int dscale; /* display scale */
NumericDigit *buf; /* start of palloc'd space for digits[] */
NumericDigit *digits; /* base-NBASE digits */
@@ -353,16 +384,26 @@ typedef struct NumericSumAccum
* representations for numeric values in order to avoid depending on
* USE_FLOAT8_BYVAL. The type of abbreviation we use is based only on
* the size of a datum, not the argument-passing convention for float8.
+ *
+ * The range of abbreviations for finite values is from +PG_INT64/32_MAX
+ * to -PG_INT64/32_MAX. NaN has the abbreviation PG_INT64/32_MIN, and we
+ * define the sort ordering to make that work out properly (see further
+ * comments below). PINF and NINF share the abbreviations of the largest
+ * and smallest finite abbreviation classes.
*/
#define NUMERIC_ABBREV_BITS (SIZEOF_DATUM * BITS_PER_BYTE)
#if SIZEOF_DATUM == 8
#define NumericAbbrevGetDatum(X) ((Datum) (X))
#define DatumGetNumericAbbrev(X) ((int64) (X))
#define NUMERIC_ABBREV_NAN NumericAbbrevGetDatum(PG_INT64_MIN)
+#define NUMERIC_ABBREV_PINF NumericAbbrevGetDatum(-PG_INT64_MAX)
+#define NUMERIC_ABBREV_NINF NumericAbbrevGetDatum(PG_INT64_MAX)
#else
#define NumericAbbrevGetDatum(X) ((Datum) (X))
#define DatumGetNumericAbbrev(X) ((int32) (X))
#define NUMERIC_ABBREV_NAN NumericAbbrevGetDatum(PG_INT32_MIN)
+#define NUMERIC_ABBREV_PINF NumericAbbrevGetDatum(-PG_INT32_MAX)
+#define NUMERIC_ABBREV_NINF NumericAbbrevGetDatum(PG_INT32_MAX)
#endif
@@ -378,6 +419,9 @@ static const NumericDigit const_one_data[1] = {1};
static const NumericVar const_one =
{1, 0, NUMERIC_POS, 0, NULL, (NumericDigit *) const_one_data};
+static const NumericVar const_minus_one =
+{1, 0, NUMERIC_NEG, 0, NULL, (NumericDigit *) const_one_data};
+
static const NumericDigit const_two_data[1] = {2};
static const NumericVar const_two =
{1, 0, NUMERIC_POS, 0, NULL, (NumericDigit *) const_two_data};
@@ -415,6 +459,12 @@ static const NumericVar const_one_point_one =
static const NumericVar const_nan =
{0, 0, NUMERIC_NAN, 0, NULL, NULL};
+static const NumericVar const_pinf =
+{0, 0, NUMERIC_PINF, 0, NULL, NULL};
+
+static const NumericVar const_ninf =
+{0, 0, NUMERIC_NINF, 0, NULL, NULL};
+
#if DEC_DIGITS == 4
static const int round_powers[4] = {0, 1000, 100, 10};
#endif
@@ -464,6 +514,7 @@ static void set_var_from_var(const NumericVar *value, NumericVar *dest);
static char *get_str_from_var(const NumericVar *var);
static char *get_str_from_var_sci(const NumericVar *var, int rscale);
+static Numeric duplicate_numeric(Numeric num);
static Numeric make_result(const NumericVar *var);
static Numeric make_result_opt_error(const NumericVar *var, bool *error);
@@ -493,6 +544,7 @@ static int cmp_var_common(const NumericDigit *var1digits, int var1ndigits,
int var1weight, int var1sign,
const NumericDigit *var2digits, int var2ndigits,
int var2weight, int var2sign);
+static bool numeric_is_integral(Numeric num);
static void add_var(const NumericVar *var1, const NumericVar *var2,
NumericVar *result);
static void sub_var(const NumericVar *var1, const NumericVar *var2,
@@ -585,23 +637,43 @@ numeric_in(PG_FUNCTION_ARGS)
}
/*
- * Check for NaN
+ * Check for NaN and infinities. We recognize the same strings allowed by
+ * float8in().
*/
if (pg_strncasecmp(cp, "NaN", 3) == 0)
{
res = make_result(&const_nan);
-
- /* Should be nothing left but spaces */
cp += 3;
- while (*cp)
- {
- if (!isspace((unsigned char) *cp))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
- cp++;
- }
+ }
+ else if (pg_strncasecmp(cp, "Infinity", 8) == 0)
+ {
+ res = make_result(&const_pinf);
+ cp += 8;
+ }
+ else if (pg_strncasecmp(cp, "+Infinity", 9) == 0)
+ {
+ res = make_result(&const_pinf);
+ cp += 9;
+ }
+ else if (pg_strncasecmp(cp, "-Infinity", 9) == 0)
+ {
+ res = make_result(&const_ninf);
+ cp += 9;
+ }
+ else if (pg_strncasecmp(cp, "inf", 3) == 0)
+ {
+ res = make_result(&const_pinf);
+ cp += 3;
+ }
+ else if (pg_strncasecmp(cp, "+inf", 4) == 0)
+ {
+ res = make_result(&const_pinf);
+ cp += 4;
+ }
+ else if (pg_strncasecmp(cp, "-inf", 4) == 0)
+ {
+ res = make_result(&const_ninf);
+ cp += 4;
}
else
{
@@ -618,7 +690,7 @@ numeric_in(PG_FUNCTION_ARGS)
* We duplicate a few lines of code here because we would like to
* throw any trailing-junk syntax error before any semantic error
* resulting from apply_typmod. We can't easily fold the two cases
- * together because we mustn't apply apply_typmod to a NaN.
+ * together because we mustn't apply apply_typmod to a NaN/Inf.
*/
while (*cp)
{
@@ -634,6 +706,19 @@ numeric_in(PG_FUNCTION_ARGS)
res = make_result(&value);
free_var(&value);
+
+ PG_RETURN_NUMERIC(res);
+ }
+
+ /* Should be nothing left but spaces */
+ while (*cp)
+ {
+ if (!isspace((unsigned char) *cp))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str)));
+ cp++;
}
PG_RETURN_NUMERIC(res);
@@ -653,10 +738,17 @@ numeric_out(PG_FUNCTION_ARGS)
char *str;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_CSTRING(pstrdup("NaN"));
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_PINF(num))
+ PG_RETURN_CSTRING(pstrdup("Infinity"));
+ else if (NUMERIC_IS_NINF(num))
+ PG_RETURN_CSTRING(pstrdup("-Infinity"));
+ else
+ PG_RETURN_CSTRING(pstrdup("NaN"));
+ }
/*
* Get the number in the variable format.
@@ -679,6 +771,17 @@ numeric_is_nan(Numeric num)
return NUMERIC_IS_NAN(num);
}
+/*
+ * numeric_is_inf() -
+ *
+ * Is Numeric value an infinity?
+ */
+bool
+numeric_is_inf(Numeric num)
+{
+ return NUMERIC_IS_INF(num);
+}
+
/*
* numeric_maximum_size() -
*
@@ -730,10 +833,17 @@ numeric_out_sci(Numeric num, int scale)
char *str;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- return pstrdup("NaN");
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_PINF(num))
+ return pstrdup("Infinity");
+ else if (NUMERIC_IS_NINF(num))
+ return pstrdup("-Infinity");
+ else
+ return pstrdup("NaN");
+ }
init_var_from_num(num, &x);
@@ -758,10 +868,17 @@ numeric_normalize(Numeric num)
int last;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- return pstrdup("NaN");
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_PINF(num))
+ return pstrdup("Infinity");
+ else if (NUMERIC_IS_NINF(num))
+ return pstrdup("-Infinity");
+ else
+ return pstrdup("NaN");
+ }
init_var_from_num(num, &x);
@@ -821,7 +938,9 @@ numeric_recv(PG_FUNCTION_ARGS)
value.sign = (uint16) pq_getmsgint(buf, sizeof(uint16));
if (!(value.sign == NUMERIC_POS ||
value.sign == NUMERIC_NEG ||
- value.sign == NUMERIC_NAN))
+ value.sign == NUMERIC_NAN ||
+ value.sign == NUMERIC_PINF ||
+ value.sign == NUMERIC_NINF))
ereport(ERROR,
(errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
errmsg("invalid sign in external \"numeric\" value")));
@@ -847,11 +966,17 @@ numeric_recv(PG_FUNCTION_ARGS)
* If the given dscale would hide any digits, truncate those digits away.
* We could alternatively throw an error, but that would take a bunch of
* extra code (about as much as trunc_var involves), and it might cause
- * client compatibility issues.
+ * client compatibility issues. Be careful not to apply trunc_var to
+ * special values, as it could do the wrong thing; we don't need it
+ * anyway, since make_result will ignore all but the sign field.
*/
- trunc_var(&value, value.dscale);
+ if (value.sign == NUMERIC_POS ||
+ value.sign == NUMERIC_NEG)
+ {
+ trunc_var(&value, value.dscale);
- apply_typmod(&value, typmod);
+ apply_typmod(&value, typmod);
+ }
res = make_result(&value);
free_var(&value);
@@ -958,22 +1083,12 @@ numeric (PG_FUNCTION_ARGS)
int maxdigits;
NumericVar var;
- /*
- * Handle NaN
- */
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
-
/*
* If the value isn't a valid type modifier, simply return a copy of the
* input value
*/
if (typmod < (int32) (VARHDRSZ))
- {
- new = (Numeric) palloc(VARSIZE(num));
- memcpy(new, num, VARSIZE(num));
- PG_RETURN_NUMERIC(new);
- }
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
/*
* Get the precision and scale out of the typmod value
@@ -983,6 +1098,23 @@ numeric (PG_FUNCTION_ARGS)
scale = tmp_typmod & 0xffff;
maxdigits = precision - scale;
+ /*
+ * Handle NaN and infinities. NaN is allowed regardless of the typmod;
+ * that's rather dubious perhaps, but it's a longstanding behavior. Inf
+ * is rejected if we have any typmod restriction, since an infinity
+ * shouldn't be claimed to fit in any finite number of digits.
+ */
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_NAN(num))
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("numeric field overflow"),
+ errdetail("A field with precision %d, scale %d cannot hold an infinite value.",
+ precision, scale)));
+ }
+
/*
* If the number is certainly in bounds and due to the target scale no
* rounding could be necessary, just make a copy of the input and modify
@@ -995,8 +1127,7 @@ numeric (PG_FUNCTION_ARGS)
&& (NUMERIC_CAN_BE_SHORT(scale, NUMERIC_WEIGHT(num))
|| !NUMERIC_IS_SHORT(num)))
{
- new = (Numeric) palloc(VARSIZE(num));
- memcpy(new, num, VARSIZE(num));
+ new = duplicate_numeric(num);
if (NUMERIC_IS_SHORT(num))
new->choice.n_short.n_header =
(num->choice.n_short.n_header & ~NUMERIC_SHORT_DSCALE_MASK)
@@ -1097,21 +1228,20 @@ numeric_abs(PG_FUNCTION_ARGS)
Numeric num = PG_GETARG_NUMERIC(0);
Numeric res;
- /*
- * Handle NaN
- */
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
-
/*
* Do it the easy way directly on the packed format
*/
- res = (Numeric) palloc(VARSIZE(num));
- memcpy(res, num, VARSIZE(num));
+ res = duplicate_numeric(num);
if (NUMERIC_IS_SHORT(num))
res->choice.n_short.n_header =
num->choice.n_short.n_header & ~NUMERIC_SHORT_SIGN_MASK;
+ else if (NUMERIC_IS_SPECIAL(num))
+ {
+ /* This changes -Inf to Inf, and doesn't affect NaN */
+ res->choice.n_short.n_header =
+ num->choice.n_short.n_header & ~NUMERIC_INF_SIGN_MASK;
+ }
else
res->choice.n_long.n_sign_dscale = NUMERIC_POS | NUMERIC_DSCALE(num);
@@ -1125,24 +1255,25 @@ numeric_uminus(PG_FUNCTION_ARGS)
Numeric num = PG_GETARG_NUMERIC(0);
Numeric res;
- /*
- * Handle NaN
- */
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
-
/*
* Do it the easy way directly on the packed format
*/
- res = (Numeric) palloc(VARSIZE(num));
- memcpy(res, num, VARSIZE(num));
+ res = duplicate_numeric(num);
+
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ /* Flip the sign, if it's Inf or -Inf */
+ if (!NUMERIC_IS_NAN(num))
+ res->choice.n_short.n_header =
+ num->choice.n_short.n_header ^ NUMERIC_INF_SIGN_MASK;
+ }
/*
* The packed format is known to be totally zero digit trimmed always. So
- * we can identify a ZERO by the fact that there are no digits at all. Do
- * nothing to a zero.
+ * once we've eliminated specials, we can identify a zero by the fact that
+ * there are no digits at all. Do nothing to a zero.
*/
- if (NUMERIC_NDIGITS(num) != 0)
+ else if (NUMERIC_NDIGITS(num) != 0)
{
/* Else, flip the sign */
if (NUMERIC_IS_SHORT(num))
@@ -1164,12 +1295,42 @@ Datum
numeric_uplus(PG_FUNCTION_ARGS)
{
Numeric num = PG_GETARG_NUMERIC(0);
- Numeric res;
- res = (Numeric) palloc(VARSIZE(num));
- memcpy(res, num, VARSIZE(num));
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
+}
- PG_RETURN_NUMERIC(res);
+
+/*
+ * numeric_sign_internal() -
+ *
+ * Returns -1 if the argument is less than 0, 0 if the argument is equal
+ * to 0, and 1 if the argument is greater than zero. Caller must have
+ * taken care of the NaN case, but we can handle infinities here.
+ */
+static int
+numeric_sign_internal(Numeric num)
+{
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ Assert(!NUMERIC_IS_NAN(num));
+ /* Must be Inf or -Inf */
+ if (NUMERIC_IS_PINF(num))
+ return 1;
+ else
+ return -1;
+ }
+
+ /*
+ * The packed format is known to be totally zero digit trimmed always. So
+ * once we've eliminated specials, we can identify a zero by the fact that
+ * there are no digits at all.
+ */
+ else if (NUMERIC_NDIGITS(num) == 0)
+ return 0;
+ else if (NUMERIC_SIGN(num) == NUMERIC_NEG)
+ return -1;
+ else
+ return 1;
}
/*
@@ -1182,37 +1343,25 @@ Datum
numeric_sign(PG_FUNCTION_ARGS)
{
Numeric num = PG_GETARG_NUMERIC(0);
- Numeric res;
- NumericVar result;
/*
- * Handle NaN
+ * Handle NaN (infinities can be handled normally)
*/
if (NUMERIC_IS_NAN(num))
PG_RETURN_NUMERIC(make_result(&const_nan));
- init_var(&result);
-
- /*
- * The packed format is known to be totally zero digit trimmed always. So
- * we can identify a ZERO by the fact that there are no digits at all.
- */
- if (NUMERIC_NDIGITS(num) == 0)
- set_var_from_var(&const_zero, &result);
- else
+ switch (numeric_sign_internal(num))
{
- /*
- * And if there are some, we return a copy of ONE with the sign of our
- * argument
- */
- set_var_from_var(&const_one, &result);
- result.sign = NUMERIC_SIGN(num);
+ case 0:
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+ case 1:
+ PG_RETURN_NUMERIC(make_result(&const_one));
+ case -1:
+ PG_RETURN_NUMERIC(make_result(&const_minus_one));
}
- res = make_result(&result);
- free_var(&result);
-
- PG_RETURN_NUMERIC(res);
+ Assert(false);
+ return (Datum) 0;
}
@@ -1232,10 +1381,10 @@ numeric_round(PG_FUNCTION_ARGS)
NumericVar arg;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num))
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
/*
* Limit the scale value to avoid possible overflow in calculations
@@ -1281,10 +1430,10 @@ numeric_trunc(PG_FUNCTION_ARGS)
NumericVar arg;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num))
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
/*
* Limit the scale value to avoid possible overflow in calculations
@@ -1326,8 +1475,11 @@ numeric_ceil(PG_FUNCTION_ARGS)
Numeric res;
NumericVar result;
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ /*
+ * Handle NaN and infinities
+ */
+ if (NUMERIC_IS_SPECIAL(num))
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
init_var_from_num(num, &result);
ceil_var(&result, &result);
@@ -1351,8 +1503,11 @@ numeric_floor(PG_FUNCTION_ARGS)
Numeric res;
NumericVar result;
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ /*
+ * Handle NaN and infinities
+ */
+ if (NUMERIC_IS_SPECIAL(num))
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
init_var_from_num(num, &result);
floor_var(&result, &result);
@@ -1388,26 +1543,46 @@ generate_series_step_numeric(PG_FUNCTION_ARGS)
Numeric stop_num = PG_GETARG_NUMERIC(1);
NumericVar steploc = const_one;
- /* handle NaN in start and stop values */
- if (NUMERIC_IS_NAN(start_num))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("start value cannot be NaN")));
-
- if (NUMERIC_IS_NAN(stop_num))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("stop value cannot be NaN")));
+ /* Reject NaN and infinities in start and stop values */
+ if (NUMERIC_IS_SPECIAL(start_num))
+ {
+ if (NUMERIC_IS_NAN(start_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start value cannot be NaN")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("start value cannot be infinity")));
+ }
+ if (NUMERIC_IS_SPECIAL(stop_num))
+ {
+ if (NUMERIC_IS_NAN(stop_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stop value cannot be NaN")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stop value cannot be infinity")));
+ }
/* see if we were given an explicit step size */
if (PG_NARGS() == 3)
{
Numeric step_num = PG_GETARG_NUMERIC(2);
- if (NUMERIC_IS_NAN(step_num))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("step size cannot be NaN")));
+ if (NUMERIC_IS_SPECIAL(step_num))
+ {
+ if (NUMERIC_IS_NAN(step_num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be NaN")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("step size cannot be infinity")));
+ }
init_var_from_num(step_num, &steploc);
@@ -1508,12 +1683,21 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
errmsg("count must be greater than zero")));
- if (NUMERIC_IS_NAN(operand) ||
- NUMERIC_IS_NAN(bound1) ||
- NUMERIC_IS_NAN(bound2))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
- errmsg("operand, lower bound, and upper bound cannot be NaN")));
+ if (NUMERIC_IS_SPECIAL(operand) ||
+ NUMERIC_IS_SPECIAL(bound1) ||
+ NUMERIC_IS_SPECIAL(bound2))
+ {
+ if (NUMERIC_IS_NAN(operand) ||
+ NUMERIC_IS_NAN(bound1) ||
+ NUMERIC_IS_NAN(bound2))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
+ errmsg("operand, lower bound, and upper bound cannot be NaN")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
+ errmsg("operand, lower bound, and upper bound cannot be infinity")));
+ }
init_var(&result_var);
init_var(&count_var);
@@ -1717,9 +1901,14 @@ numeric_abbrev_convert(Datum original_datum, SortSupport ssup)
else
value = (Numeric) original_varatt;
- if (NUMERIC_IS_NAN(value))
+ if (NUMERIC_IS_SPECIAL(value))
{
- result = NUMERIC_ABBREV_NAN;
+ if (NUMERIC_IS_PINF(value))
+ result = NUMERIC_ABBREV_PINF;
+ else if (NUMERIC_IS_NINF(value))
+ result = NUMERIC_ABBREV_NINF;
+ else
+ result = NUMERIC_ABBREV_NAN;
}
else
{
@@ -1845,7 +2034,7 @@ numeric_cmp_abbrev(Datum x, Datum y, SortSupport ssup)
{
/*
* NOTE WELL: this is intentionally backwards, because the abbreviation is
- * negated relative to the original value, to handle NaN.
+ * negated relative to the original value, to handle NaN/infinity cases.
*/
if (DatumGetNumericAbbrev(x) < DatumGetNumericAbbrev(y))
return 1;
@@ -2148,20 +2337,42 @@ cmp_numerics(Numeric num1, Numeric num2)
int result;
/*
- * We consider all NANs to be equal and larger than any non-NAN. This is
- * somewhat arbitrary; the important thing is to have a consistent sort
- * order.
+ * We consider all NANs to be equal and larger than any non-NAN (including
+ * Infinity). This is somewhat arbitrary; the important thing is to have
+ * a consistent sort order.
*/
- if (NUMERIC_IS_NAN(num1))
+ if (NUMERIC_IS_SPECIAL(num1))
{
- if (NUMERIC_IS_NAN(num2))
- result = 0; /* NAN = NAN */
- else
- result = 1; /* NAN > non-NAN */
+ if (NUMERIC_IS_NAN(num1))
+ {
+ if (NUMERIC_IS_NAN(num2))
+ result = 0; /* NAN = NAN */
+ else
+ result = 1; /* NAN > non-NAN */
+ }
+ else if (NUMERIC_IS_PINF(num1))
+ {
+ if (NUMERIC_IS_NAN(num2))
+ result = -1; /* PINF < NAN */
+ else if (NUMERIC_IS_PINF(num2))
+ result = 0; /* PINF = PINF */
+ else
+ result = 1; /* PINF > anything else */
+ }
+ else /* num1 must be NINF */
+ {
+ if (NUMERIC_IS_NINF(num2))
+ result = 0; /* NINF = NINF */
+ else
+ result = -1; /* NINF < anything else */
+ }
}
- else if (NUMERIC_IS_NAN(num2))
+ else if (NUMERIC_IS_SPECIAL(num2))
{
- result = -1; /* non-NAN < NAN */
+ if (NUMERIC_IS_NINF(num2))
+ result = 1; /* normal > NINF */
+ else
+ result = -1; /* normal < NAN or PINF */
}
else
{
@@ -2188,10 +2399,12 @@ in_range_numeric_numeric(PG_FUNCTION_ARGS)
bool result;
/*
- * Reject negative or NaN offset. Negative is per spec, and NaN is
- * because appropriate semantics for that seem non-obvious.
+ * Reject negative (including -Inf) or NaN offset. Negative is per spec,
+ * and NaN is because appropriate semantics for that seem non-obvious.
*/
- if (NUMERIC_IS_NAN(offset) || NUMERIC_SIGN(offset) == NUMERIC_NEG)
+ if (NUMERIC_IS_NAN(offset) ||
+ NUMERIC_IS_NINF(offset) ||
+ NUMERIC_SIGN(offset) == NUMERIC_NEG)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
errmsg("invalid preceding or following size in window function")));
@@ -2212,6 +2425,45 @@ in_range_numeric_numeric(PG_FUNCTION_ARGS)
{
result = less; /* non-NAN < NAN */
}
+
+ /*
+ * Deal with infinite offset (necessarily +Inf, at this point). Infinite
+ * val and/or base cannot affect the conclusion.
+ */
+ else if (NUMERIC_IS_SPECIAL(offset))
+ {
+ Assert(NUMERIC_IS_PINF(offset));
+ result = (sub ? !less : less);
+ }
+
+ /*
+ * Deal with cases where val and/or base is infinite. The offset, being
+ * now known finite, cannot affect the conclusion.
+ */
+ else if (NUMERIC_IS_SPECIAL(val))
+ {
+ if (NUMERIC_IS_PINF(val))
+ {
+ if (NUMERIC_IS_PINF(base))
+ result = true; /* PINF = PINF */
+ else
+ result = !less; /* PINF > any other non-NAN */
+ }
+ else /* val must be NINF */
+ {
+ if (NUMERIC_IS_NINF(base))
+ result = true; /* NINF = NINF */
+ else
+ result = less; /* NINF < anything else */
+ }
+ }
+ else if (NUMERIC_IS_SPECIAL(base))
+ {
+ if (NUMERIC_IS_NINF(base))
+ result = !less; /* normal > NINF */
+ else
+ result = less; /* normal < PINF */
+ }
else
{
/*
@@ -2262,8 +2514,8 @@ hash_numeric(PG_FUNCTION_ARGS)
int hash_len;
NumericDigit *digits;
- /* If it's NaN, don't try to hash the rest of the fields */
- if (NUMERIC_IS_NAN(key))
+ /* If it's NaN or infinity, don't try to hash the rest of the fields */
+ if (NUMERIC_IS_SPECIAL(key))
PG_RETURN_UINT32(0);
weight = NUMERIC_WEIGHT(key);
@@ -2343,7 +2595,8 @@ hash_numeric_extended(PG_FUNCTION_ARGS)
int hash_len;
NumericDigit *digits;
- if (NUMERIC_IS_NAN(key))
+ /* If it's NaN or infinity, don't try to hash the rest of the fields */
+ if (NUMERIC_IS_SPECIAL(key))
PG_RETURN_UINT64(seed);
weight = NUMERIC_WEIGHT(key);
@@ -2427,10 +2680,32 @@ numeric_add_opt_error(Numeric num1, Numeric num2, bool *have_error)
Numeric res;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- return make_result(&const_nan);
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ return make_result(&const_nan);
+ if (NUMERIC_IS_PINF(num1))
+ {
+ if (NUMERIC_IS_NINF(num2))
+ return make_result(&const_nan); /* Inf + -Inf */
+ else
+ return make_result(&const_pinf);
+ }
+ if (NUMERIC_IS_NINF(num1))
+ {
+ if (NUMERIC_IS_PINF(num2))
+ return make_result(&const_nan); /* -Inf + Inf */
+ else
+ return make_result(&const_ninf);
+ }
+ /* by here, num1 must be finite, so num2 is not */
+ if (NUMERIC_IS_PINF(num2))
+ return make_result(&const_pinf);
+ Assert(NUMERIC_IS_NINF(num2));
+ return make_result(&const_ninf);
+ }
/*
* Unpack the values, let add_var() compute the result and return it.
@@ -2483,10 +2758,32 @@ numeric_sub_opt_error(Numeric num1, Numeric num2, bool *have_error)
Numeric res;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- return make_result(&const_nan);
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ return make_result(&const_nan);
+ if (NUMERIC_IS_PINF(num1))
+ {
+ if (NUMERIC_IS_PINF(num2))
+ return make_result(&const_nan); /* Inf - Inf */
+ else
+ return make_result(&const_pinf);
+ }
+ if (NUMERIC_IS_NINF(num1))
+ {
+ if (NUMERIC_IS_NINF(num2))
+ return make_result(&const_nan); /* -Inf - -Inf */
+ else
+ return make_result(&const_ninf);
+ }
+ /* by here, num1 must be finite, so num2 is not */
+ if (NUMERIC_IS_PINF(num2))
+ return make_result(&const_ninf);
+ Assert(NUMERIC_IS_NINF(num2));
+ return make_result(&const_pinf);
+ }
/*
* Unpack the values, let sub_var() compute the result and return it.
@@ -2539,10 +2836,64 @@ numeric_mul_opt_error(Numeric num1, Numeric num2, bool *have_error)
Numeric res;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- return make_result(&const_nan);
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ return make_result(&const_nan);
+ if (NUMERIC_IS_PINF(num1))
+ {
+ switch (numeric_sign_internal(num2))
+ {
+ case 0:
+ return make_result(&const_nan); /* Inf * 0 */
+ case 1:
+ return make_result(&const_pinf);
+ case -1:
+ return make_result(&const_ninf);
+ }
+ Assert(false);
+ }
+ if (NUMERIC_IS_NINF(num1))
+ {
+ switch (numeric_sign_internal(num2))
+ {
+ case 0:
+ return make_result(&const_nan); /* -Inf * 0 */
+ case 1:
+ return make_result(&const_ninf);
+ case -1:
+ return make_result(&const_pinf);
+ }
+ Assert(false);
+ }
+ /* by here, num1 must be finite, so num2 is not */
+ if (NUMERIC_IS_PINF(num2))
+ {
+ switch (numeric_sign_internal(num1))
+ {
+ case 0:
+ return make_result(&const_nan); /* 0 * Inf */
+ case 1:
+ return make_result(&const_pinf);
+ case -1:
+ return make_result(&const_ninf);
+ }
+ Assert(false);
+ }
+ Assert(NUMERIC_IS_NINF(num2));
+ switch (numeric_sign_internal(num1))
+ {
+ case 0:
+ return make_result(&const_nan); /* 0 * -Inf */
+ case 1:
+ return make_result(&const_ninf);
+ case -1:
+ return make_result(&const_pinf);
+ }
+ Assert(false);
+ }
/*
* Unpack the values, let mul_var() compute the result and return it.
@@ -2602,11 +2953,68 @@ numeric_div_opt_error(Numeric num1, Numeric num2, bool *have_error)
if (have_error)
*have_error = false;
- /*
- * Handle NaN
- */
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- return make_result(&const_nan);
+ /*
+ * Handle NaN and infinities
+ */
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ return make_result(&const_nan);
+ if (NUMERIC_IS_PINF(num1))
+ {
+ if (NUMERIC_IS_SPECIAL(num2))
+ return make_result(&const_nan); /* Inf / [-]Inf */
+ switch (numeric_sign_internal(num2))
+ {
+ case 0:
+ if (have_error)
+ {
+ *have_error = true;
+ return NULL;
+ }
+ ereport(ERROR,
+ (errcode(ERRCODE_DIVISION_BY_ZERO),
+ errmsg("division by zero")));
+ break;
+ case 1:
+ return make_result(&const_pinf);
+ case -1:
+ return make_result(&const_ninf);
+ }
+ Assert(false);
+ }
+ if (NUMERIC_IS_NINF(num1))
+ {
+ if (NUMERIC_IS_SPECIAL(num2))
+ return make_result(&const_nan); /* -Inf / [-]Inf */
+ switch (numeric_sign_internal(num2))
+ {
+ case 0:
+ if (have_error)
+ {
+ *have_error = true;
+ return NULL;
+ }
+ ereport(ERROR,
+ (errcode(ERRCODE_DIVISION_BY_ZERO),
+ errmsg("division by zero")));
+ break;
+ case 1:
+ return make_result(&const_ninf);
+ case -1:
+ return make_result(&const_pinf);
+ }
+ Assert(false);
+ }
+ /* by here, num1 must be finite, so num2 is not */
+
+ /*
+ * POSIX would have us return zero or minus zero if num1 is zero, and
+ * otherwise throw an underflow error. But the numeric type doesn't
+ * really do underflow, so let's just return zero.
+ */
+ return make_result(&const_zero);
+ }
/*
* Unpack the arguments
@@ -2659,10 +3067,57 @@ numeric_div_trunc(PG_FUNCTION_ARGS)
Numeric res;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_PINF(num1))
+ {
+ if (NUMERIC_IS_SPECIAL(num2))
+ PG_RETURN_NUMERIC(make_result(&const_nan)); /* Inf / [-]Inf */
+ switch (numeric_sign_internal(num2))
+ {
+ case 0:
+ ereport(ERROR,
+ (errcode(ERRCODE_DIVISION_BY_ZERO),
+ errmsg("division by zero")));
+ break;
+ case 1:
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ case -1:
+ PG_RETURN_NUMERIC(make_result(&const_ninf));
+ }
+ Assert(false);
+ }
+ if (NUMERIC_IS_NINF(num1))
+ {
+ if (NUMERIC_IS_SPECIAL(num2))
+ PG_RETURN_NUMERIC(make_result(&const_nan)); /* -Inf / [-]Inf */
+ switch (numeric_sign_internal(num2))
+ {
+ case 0:
+ ereport(ERROR,
+ (errcode(ERRCODE_DIVISION_BY_ZERO),
+ errmsg("division by zero")));
+ break;
+ case 1:
+ PG_RETURN_NUMERIC(make_result(&const_ninf));
+ case -1:
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ }
+ Assert(false);
+ }
+ /* by here, num1 must be finite, so num2 is not */
+
+ /*
+ * POSIX would have us return zero or minus zero if num1 is zero, and
+ * otherwise throw an underflow error. But the numeric type doesn't
+ * really do underflow, so let's just return zero.
+ */
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+ }
/*
* Unpack the arguments
@@ -2721,8 +3176,32 @@ numeric_mod_opt_error(Numeric num1, Numeric num2, bool *have_error)
if (have_error)
*have_error = false;
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- return make_result(&const_nan);
+ /*
+ * Handle NaN and infinities
+ */
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ return make_result(&const_nan);
+ if (NUMERIC_IS_INF(num1))
+ {
+ if (numeric_sign_internal(num2) == 0)
+ {
+ if (have_error)
+ {
+ *have_error = true;
+ return NULL;
+ }
+ ereport(ERROR,
+ (errcode(ERRCODE_DIVISION_BY_ZERO),
+ errmsg("division by zero")));
+ }
+ /* Inf % any nonzero = NaN */
+ return make_result(&const_nan);
+ }
+ /* num2 must be [-]Inf; result is num1 regardless of sign of num2 */
+ return duplicate_numeric(num1);
+ }
init_var_from_num(num1, &arg1);
init_var_from_num(num2, &arg2);
@@ -2761,10 +3240,10 @@ numeric_inc(PG_FUNCTION_ARGS)
Numeric res;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num))
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
/*
* Compute the result and return it
@@ -2848,10 +3327,30 @@ numeric_gcd(PG_FUNCTION_ARGS)
Numeric res;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ /*----------
+ * We have these cases involving infinities:
+ * gcd([-]Inf, 0) = Inf
+ * gcd([-]Inf, [-]Inf) = Inf
+ * gcd([-]Inf, x) = abs(x) for any finite nonzero x
+ *----------
+ */
+ if (NUMERIC_IS_INF(num1))
+ {
+ if (numeric_sign_internal(num2) == 0)
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ return DirectFunctionCall1(numeric_abs, NumericGetDatum(num2));
+ }
+ Assert(NUMERIC_IS_INF(num2));
+ if (numeric_sign_internal(num1) == 0)
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ return DirectFunctionCall1(numeric_abs, NumericGetDatum(num1));
+ }
/*
* Unpack the arguments
@@ -2890,10 +3389,23 @@ numeric_lcm(PG_FUNCTION_ARGS)
Numeric res;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+
+ /*
+ * The lcm for any case involving an infinity is +Inf ... except when
+ * the other argument is zero.
+ */
+ if (numeric_sign_internal(num1) == 0 ||
+ numeric_sign_internal(num2) == 0)
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+ else
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ }
/*
* Unpack the arguments
@@ -2997,10 +3509,18 @@ numeric_sqrt(PG_FUNCTION_ARGS)
int rscale;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ /* error should match that in sqrt_var() */
+ if (NUMERIC_IS_NINF(num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_POWER_FUNCTION),
+ errmsg("cannot take square root of a negative number")));
+ /* For NAN or PINF, just duplicate the input */
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
+ }
/*
* Unpack the argument and determine the result scale. We choose a scale
@@ -3048,10 +3568,16 @@ numeric_exp(PG_FUNCTION_ARGS)
double val;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ /* Per POSIX, exp(-Inf) is zero */
+ if (NUMERIC_IS_NINF(num))
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+ /* For NAN or PINF, just duplicate the input */
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
+ }
/*
* Unpack the argument and determine the result scale. We choose a scale
@@ -3109,10 +3635,17 @@ numeric_ln(PG_FUNCTION_ARGS)
int rscale;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_NINF(num))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_LOG),
+ errmsg("cannot take logarithm of a negative number")));
+ /* For NAN or PINF, just duplicate the input */
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
+ }
init_var_from_num(num, &arg);
init_var(&result);
@@ -3151,10 +3684,39 @@ numeric_log(PG_FUNCTION_ARGS)
NumericVar result;
/*
- * Handle NaN
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
+ {
+ int sign1,
+ sign2;
+
+ if (NUMERIC_IS_NAN(num1) || NUMERIC_IS_NAN(num2))
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ /* fail on negative inputs including -Inf, as log_var would */
+ sign1 = numeric_sign_internal(num1);
+ sign2 = numeric_sign_internal(num2);
+ if (sign1 < 0 || sign2 < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_LOG),
+ errmsg("cannot take logarithm of a negative number")));
+ /* fail on zero inputs, as log_var would */
+ if (sign1 == 0 || sign2 == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_LOG),
+ errmsg("cannot take logarithm of zero")));
+ if (NUMERIC_IS_PINF(num1))
+ {
+ /* log(Inf, Inf) reduces to Inf/Inf, so it's NaN */
+ if (NUMERIC_IS_PINF(num2))
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ /* log(Inf, finite-positive) is zero (we don't throw underflow) */
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+ }
+ Assert(NUMERIC_IS_PINF(num2));
+ /* log(finite-positive, Inf) is Inf */
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ }
/*
* Initialize things
@@ -3180,7 +3742,7 @@ numeric_log(PG_FUNCTION_ARGS)
/*
* numeric_power() -
*
- * Raise b to the power of x
+ * Raise x to the power of y
*/
Datum
numeric_power(PG_FUNCTION_ARGS)
@@ -3190,60 +3752,170 @@ numeric_power(PG_FUNCTION_ARGS)
Numeric res;
NumericVar arg1;
NumericVar arg2;
- NumericVar arg2_trunc;
NumericVar result;
+ int sign1,
+ sign2;
/*
- * Handle NaN cases. We follow the POSIX spec for pow(3), which says that
- * NaN ^ 0 = 1, and 1 ^ NaN = 1, while all other cases with NaN inputs
- * yield NaN (with no error).
+ * Handle NaN and infinities
*/
- if (NUMERIC_IS_NAN(num1))
+ if (NUMERIC_IS_SPECIAL(num1) || NUMERIC_IS_SPECIAL(num2))
{
- if (!NUMERIC_IS_NAN(num2))
+ /*
+ * We follow the POSIX spec for pow(3), which says that NaN ^ 0 = 1,
+ * and 1 ^ NaN = 1, while all other cases with NaN inputs yield NaN
+ * (with no error).
+ */
+ if (NUMERIC_IS_NAN(num1))
+ {
+ if (!NUMERIC_IS_SPECIAL(num2))
+ {
+ init_var_from_num(num2, &arg2);
+ if (cmp_var(&arg2, &const_zero) == 0)
+ PG_RETURN_NUMERIC(make_result(&const_one));
+ }
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ }
+ if (NUMERIC_IS_NAN(num2))
+ {
+ if (!NUMERIC_IS_SPECIAL(num1))
+ {
+ init_var_from_num(num1, &arg1);
+ if (cmp_var(&arg1, &const_one) == 0)
+ PG_RETURN_NUMERIC(make_result(&const_one));
+ }
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ }
+ /* At least one input is infinite, but error rules still apply */
+ sign1 = numeric_sign_internal(num1);
+ sign2 = numeric_sign_internal(num2);
+ if (sign1 == 0 && sign2 < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_POWER_FUNCTION),
+ errmsg("zero raised to a negative power is undefined")));
+ if (sign1 < 0 && !numeric_is_integral(num2))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_POWER_FUNCTION),
+ errmsg("a negative number raised to a non-integer power yields a complex result")));
+
+ /*
+ * POSIX gives this series of rules for pow(3) with infinite inputs:
+ *
+ * For any value of y, if x is +1, 1.0 shall be returned.
+ */
+ if (!NUMERIC_IS_SPECIAL(num1))
{
- init_var_from_num(num2, &arg2);
- if (cmp_var(&arg2, &const_zero) == 0)
+ init_var_from_num(num1, &arg1);
+ if (cmp_var(&arg1, &const_one) == 0)
PG_RETURN_NUMERIC(make_result(&const_one));
}
- PG_RETURN_NUMERIC(make_result(&const_nan));
- }
- if (NUMERIC_IS_NAN(num2))
- {
- init_var_from_num(num1, &arg1);
- if (cmp_var(&arg1, &const_one) == 0)
+
+ /*
+ * For any value of x, if y is [-]0, 1.0 shall be returned.
+ */
+ if (sign2 == 0)
PG_RETURN_NUMERIC(make_result(&const_one));
- PG_RETURN_NUMERIC(make_result(&const_nan));
- }
- /*
- * Initialize things
- */
- init_var(&arg2_trunc);
- init_var(&result);
- init_var_from_num(num1, &arg1);
- init_var_from_num(num2, &arg2);
+ /*
+ * For any odd integer value of y > 0, if x is [-]0, [-]0 shall be
+ * returned. For y > 0 and not an odd integer, if x is [-]0, +0 shall
+ * be returned. (Since we don't deal in minus zero, we need not
+ * distinguish these two cases.)
+ */
+ if (sign1 == 0 && sign2 > 0)
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+
+ /*
+ * If x is -1, and y is [-]Inf, 1.0 shall be returned.
+ *
+ * For |x| < 1, if y is -Inf, +Inf shall be returned.
+ *
+ * For |x| > 1, if y is -Inf, +0 shall be returned.
+ *
+ * For |x| < 1, if y is +Inf, +0 shall be returned.
+ *
+ * For |x| > 1, if y is +Inf, +Inf shall be returned.
+ */
+ if (NUMERIC_IS_INF(num2))
+ {
+ bool abs_x_gt_one;
+
+ if (NUMERIC_IS_SPECIAL(num1))
+ abs_x_gt_one = true; /* x is either Inf or -Inf */
+ else
+ {
+ init_var_from_num(num1, &arg1);
+ if (cmp_var(&arg1, &const_minus_one) == 0)
+ PG_RETURN_NUMERIC(make_result(&const_one));
+ arg1.sign = NUMERIC_POS; /* now arg1 = abs(x) */
+ abs_x_gt_one = (cmp_var(&arg1, &const_one) > 0);
+ }
+ if (abs_x_gt_one == (sign2 > 0))
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ else
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+ }
+
+ /*
+ * For y < 0, if x is +Inf, +0 shall be returned.
+ *
+ * For y > 0, if x is +Inf, +Inf shall be returned.
+ */
+ if (NUMERIC_IS_PINF(num1))
+ {
+ if (sign2 > 0)
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ else
+ PG_RETURN_NUMERIC(make_result(&const_zero));
+ }
+
+ Assert(NUMERIC_IS_NINF(num1));
+
+ /*
+ * For y an odd integer < 0, if x is -Inf, -0 shall be returned. For
+ * y < 0 and not an odd integer, if x is -Inf, +0 shall be returned.
+ * (Again, we need not distinguish these two cases.)
+ */
+ if (sign2 < 0)
+ PG_RETURN_NUMERIC(make_result(&const_zero));
- set_var_from_var(&arg2, &arg2_trunc);
- trunc_var(&arg2_trunc, 0);
+ /*
+ * For y an odd integer > 0, if x is -Inf, -Inf shall be returned. For
+ * y > 0 and not an odd integer, if x is -Inf, +Inf shall be returned.
+ */
+ init_var_from_num(num2, &arg2);
+ if (arg2.ndigits > 0 && arg2.ndigits == arg2.weight + 1 &&
+ (arg2.digits[arg2.ndigits - 1] & 1))
+ PG_RETURN_NUMERIC(make_result(&const_ninf));
+ else
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ }
/*
* The SQL spec requires that we emit a particular SQLSTATE error code for
* certain error conditions. Specifically, we don't return a
* divide-by-zero error code for 0 ^ -1.
*/
- if (cmp_var(&arg1, &const_zero) == 0 &&
- cmp_var(&arg2, &const_zero) < 0)
+ sign1 = numeric_sign_internal(num1);
+ sign2 = numeric_sign_internal(num2);
+
+ if (sign1 == 0 && sign2 < 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_POWER_FUNCTION),
errmsg("zero raised to a negative power is undefined")));
- if (cmp_var(&arg1, &const_zero) < 0 &&
- cmp_var(&arg2, &arg2_trunc) != 0)
+ if (sign1 < 0 && !numeric_is_integral(num2))
ereport(ERROR,
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_POWER_FUNCTION),
errmsg("a negative number raised to a non-integer power yields a complex result")));
+ /*
+ * Initialize things
+ */
+ init_var(&result);
+ init_var_from_num(num1, &arg1);
+ init_var_from_num(num2, &arg2);
+
/*
* Call power_var() to compute and return the result; note it handles
* scale selection itself.
@@ -3253,7 +3925,6 @@ numeric_power(PG_FUNCTION_ARGS)
res = make_result(&result);
free_var(&result);
- free_var(&arg2_trunc);
PG_RETURN_NUMERIC(res);
}
@@ -3268,7 +3939,7 @@ numeric_scale(PG_FUNCTION_ARGS)
{
Numeric num = PG_GETARG_NUMERIC(0);
- if (NUMERIC_IS_NAN(num))
+ if (NUMERIC_IS_SPECIAL(num))
PG_RETURN_NULL();
PG_RETURN_INT32(NUMERIC_DSCALE(num));
@@ -3335,7 +4006,7 @@ numeric_min_scale(PG_FUNCTION_ARGS)
NumericVar arg;
int min_scale;
- if (NUMERIC_IS_NAN(num))
+ if (NUMERIC_IS_SPECIAL(num))
PG_RETURN_NULL();
init_var_from_num(num, &arg);
@@ -3355,8 +4026,8 @@ numeric_trim_scale(PG_FUNCTION_ARGS)
Numeric res;
NumericVar result;
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (NUMERIC_IS_SPECIAL(num))
+ PG_RETURN_NUMERIC(duplicate_numeric(num));
init_var_from_num(num, &result);
result.dscale = get_min_scale(&result);
@@ -3403,7 +4074,7 @@ numeric_int4_opt_error(Numeric num, bool *have_error)
*have_error = false;
/* XXX would it be better to return NULL? */
- if (NUMERIC_IS_NAN(num))
+ if (NUMERIC_IS_SPECIAL(num))
{
if (have_error)
{
@@ -3412,9 +4083,14 @@ numeric_int4_opt_error(Numeric num, bool *have_error)
}
else
{
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert NaN to integer")));
+ if (NUMERIC_IS_NAN(num))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert NaN to integer")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert infinity to integer")));
}
}
@@ -3494,10 +4170,17 @@ numeric_int8(PG_FUNCTION_ARGS)
int64 result;
/* XXX would it be better to return NULL? */
- if (NUMERIC_IS_NAN(num))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert NaN to bigint")));
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_NAN(num))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert NaN to bigint")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert infinity to bigint")));
+ }
/* Convert to variable format and thence to int8 */
init_var_from_num(num, &x);
@@ -3539,10 +4222,17 @@ numeric_int2(PG_FUNCTION_ARGS)
int16 result;
/* XXX would it be better to return NULL? */
- if (NUMERIC_IS_NAN(num))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert NaN to smallint")));
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_NAN(num))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert NaN to smallint")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert infinity to smallint")));
+ }
/* Convert to variable format and thence to int8 */
init_var_from_num(num, &x);
@@ -3577,9 +4267,12 @@ float8_numeric(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(make_result(&const_nan));
if (isinf(val))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert infinity to numeric")));
+ {
+ if (val < 0)
+ PG_RETURN_NUMERIC(make_result(&const_ninf));
+ else
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ }
snprintf(buf, sizeof(buf), "%.*g", DBL_DIG, val);
@@ -3603,8 +4296,15 @@ numeric_float8(PG_FUNCTION_ARGS)
char *tmp;
Datum result;
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_FLOAT8(get_float8_nan());
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_PINF(num))
+ PG_RETURN_FLOAT8(get_float8_infinity());
+ else if (NUMERIC_IS_NINF(num))
+ PG_RETURN_FLOAT8(-get_float8_infinity());
+ else
+ PG_RETURN_FLOAT8(get_float8_nan());
+ }
tmp = DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(num)));
@@ -3628,6 +4328,7 @@ numeric_float8_no_overflow(PG_FUNCTION_ARGS)
Numeric num = PG_GETARG_NUMERIC(0);
double val;
+ /* numeric_to_double_no_overflow will handle infinities */
if (NUMERIC_IS_NAN(num))
PG_RETURN_FLOAT8(get_float8_nan());
@@ -3648,9 +4349,12 @@ float4_numeric(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(make_result(&const_nan));
if (isinf(val))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot convert infinity to numeric")));
+ {
+ if (val < 0)
+ PG_RETURN_NUMERIC(make_result(&const_ninf));
+ else
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ }
snprintf(buf, sizeof(buf), "%.*g", FLT_DIG, val);
@@ -3674,8 +4378,15 @@ numeric_float4(PG_FUNCTION_ARGS)
char *tmp;
Datum result;
- if (NUMERIC_IS_NAN(num))
- PG_RETURN_FLOAT4(get_float4_nan());
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_PINF(num))
+ PG_RETURN_FLOAT4(get_float4_infinity());
+ else if (NUMERIC_IS_NINF(num))
+ PG_RETURN_FLOAT4(-get_float4_infinity());
+ else
+ PG_RETURN_FLOAT4(get_float4_nan());
+ }
tmp = DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(num)));
@@ -3711,9 +4422,15 @@ typedef struct NumericAggState
NumericSumAccum sumX2; /* sum of squares of processed numbers */
int maxScale; /* maximum scale seen so far */
int64 maxScaleCount; /* number of values seen with maximum scale */
- int64 NaNcount; /* count of NaN values (not included in N!) */
+ /* These counts are *not* included in N! Use NA_TOTAL_COUNT() as needed */
+ int64 NaNcount; /* count of NaN values */
+ int64 pInfcount; /* count of +Inf values */
+ int64 nInfcount; /* count of -Inf values */
} NumericAggState;
+#define NA_TOTAL_COUNT(na) \
+ ((na)->N + (na)->NaNcount + (na)->pInfcount + (na)->nInfcount)
+
/*
* Prepare state data for a numeric aggregate function that needs to compute
* sum, count and optionally sum of squares of the input.
@@ -3765,10 +4482,15 @@ do_numeric_accum(NumericAggState *state, Numeric newval)
NumericVar X2;
MemoryContext old_context;
- /* Count NaN inputs separately from all else */
- if (NUMERIC_IS_NAN(newval))
+ /* Count NaN/infinity inputs separately from all else */
+ if (NUMERIC_IS_SPECIAL(newval))
{
- state->NaNcount++;
+ if (NUMERIC_IS_PINF(newval))
+ state->pInfcount++;
+ else if (NUMERIC_IS_NINF(newval))
+ state->nInfcount++;
+ else
+ state->NaNcount++;
return;
}
@@ -3830,10 +4552,15 @@ do_numeric_discard(NumericAggState *state, Numeric newval)
NumericVar X2;
MemoryContext old_context;
- /* Count NaN inputs separately from all else */
- if (NUMERIC_IS_NAN(newval))
+ /* Count NaN/infinity inputs separately from all else */
+ if (NUMERIC_IS_SPECIAL(newval))
{
- state->NaNcount--;
+ if (NUMERIC_IS_PINF(newval))
+ state->pInfcount--;
+ else if (NUMERIC_IS_NINF(newval))
+ state->nInfcount--;
+ else
+ state->NaNcount--;
return true;
}
@@ -3956,6 +4683,8 @@ numeric_combine(PG_FUNCTION_ARGS)
state1 = makeNumericAggStateCurrentContext(true);
state1->N = state2->N;
state1->NaNcount = state2->NaNcount;
+ state1->pInfcount = state2->pInfcount;
+ state1->nInfcount = state2->nInfcount;
state1->maxScale = state2->maxScale;
state1->maxScaleCount = state2->maxScaleCount;
@@ -3969,6 +4698,8 @@ numeric_combine(PG_FUNCTION_ARGS)
state1->N += state2->N;
state1->NaNcount += state2->NaNcount;
+ state1->pInfcount += state2->pInfcount;
+ state1->nInfcount += state2->nInfcount;
if (state2->N > 0)
{
@@ -4044,6 +4775,8 @@ numeric_avg_combine(PG_FUNCTION_ARGS)
state1 = makeNumericAggStateCurrentContext(false);
state1->N = state2->N;
state1->NaNcount = state2->NaNcount;
+ state1->pInfcount = state2->pInfcount;
+ state1->nInfcount = state2->nInfcount;
state1->maxScale = state2->maxScale;
state1->maxScaleCount = state2->maxScaleCount;
@@ -4056,6 +4789,8 @@ numeric_avg_combine(PG_FUNCTION_ARGS)
state1->N += state2->N;
state1->NaNcount += state2->NaNcount;
+ state1->pInfcount += state2->pInfcount;
+ state1->nInfcount += state2->nInfcount;
if (state2->N > 0)
{
@@ -4134,6 +4869,12 @@ numeric_avg_serialize(PG_FUNCTION_ARGS)
/* NaNcount */
pq_sendint64(&buf, state->NaNcount);
+ /* pInfcount */
+ pq_sendint64(&buf, state->pInfcount);
+
+ /* nInfcount */
+ pq_sendint64(&buf, state->nInfcount);
+
result = pq_endtypsend(&buf);
PG_RETURN_BYTEA_P(result);
@@ -4188,6 +4929,12 @@ numeric_avg_deserialize(PG_FUNCTION_ARGS)
/* NaNcount */
result->NaNcount = pq_getmsgint64(&buf);
+ /* pInfcount */
+ result->pInfcount = pq_getmsgint64(&buf);
+
+ /* nInfcount */
+ result->nInfcount = pq_getmsgint64(&buf);
+
pq_getmsgend(&buf);
pfree(buf.data);
@@ -4256,6 +5003,12 @@ numeric_serialize(PG_FUNCTION_ARGS)
/* NaNcount */
pq_sendint64(&buf, state->NaNcount);
+ /* pInfcount */
+ pq_sendint64(&buf, state->pInfcount);
+
+ /* nInfcount */
+ pq_sendint64(&buf, state->nInfcount);
+
result = pq_endtypsend(&buf);
PG_RETURN_BYTEA_P(result);
@@ -4319,6 +5072,12 @@ numeric_deserialize(PG_FUNCTION_ARGS)
/* NaNcount */
result->NaNcount = pq_getmsgint64(&buf);
+ /* pInfcount */
+ result->pInfcount = pq_getmsgint64(&buf);
+
+ /* nInfcount */
+ result->nInfcount = pq_getmsgint64(&buf);
+
pq_getmsgend(&buf);
pfree(buf.data);
@@ -5111,12 +5870,20 @@ numeric_avg(PG_FUNCTION_ARGS)
state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
/* If there were no non-null inputs, return NULL */
- if (state == NULL || (state->N + state->NaNcount) == 0)
+ if (state == NULL || NA_TOTAL_COUNT(state) == 0)
PG_RETURN_NULL();
if (state->NaNcount > 0) /* there was at least one NaN input */
PG_RETURN_NUMERIC(make_result(&const_nan));
+ /* adding plus and minus infinities gives NaN */
+ if (state->pInfcount > 0 && state->nInfcount > 0)
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (state->pInfcount > 0)
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ if (state->nInfcount > 0)
+ PG_RETURN_NUMERIC(make_result(&const_ninf));
+
N_datum = DirectFunctionCall1(int8_numeric, Int64GetDatum(state->N));
init_var(&sumX_var);
@@ -5137,12 +5904,20 @@ numeric_sum(PG_FUNCTION_ARGS)
state = PG_ARGISNULL(0) ? NULL : (NumericAggState *) PG_GETARG_POINTER(0);
/* If there were no non-null inputs, return NULL */
- if (state == NULL || (state->N + state->NaNcount) == 0)
+ if (state == NULL || NA_TOTAL_COUNT(state) == 0)
PG_RETURN_NULL();
if (state->NaNcount > 0) /* there was at least one NaN input */
PG_RETURN_NUMERIC(make_result(&const_nan));
+ /* adding plus and minus infinities gives NaN */
+ if (state->pInfcount > 0 && state->nInfcount > 0)
+ PG_RETURN_NUMERIC(make_result(&const_nan));
+ if (state->pInfcount > 0)
+ PG_RETURN_NUMERIC(make_result(&const_pinf));
+ if (state->nInfcount > 0)
+ PG_RETURN_NUMERIC(make_result(&const_ninf));
+
init_var(&sumX_var);
accum_sum_final(&state->sumX, &sumX_var);
result = make_result(&sumX_var);
@@ -5178,9 +5953,9 @@ numeric_stddev_internal(NumericAggState *state,
/*
* Sample stddev and variance are undefined when N <= 1; population stddev
* is undefined when N == 0. Return NULL in either case (note that NaNs
- * count as normal inputs for this purpose).
+ * and infinities count as normal inputs for this purpose).
*/
- if (state == NULL || (totCount = state->N + state->NaNcount) == 0)
+ if (state == NULL || (totCount = NA_TOTAL_COUNT(state)) == 0)
{
*is_null = true;
return NULL;
@@ -5195,9 +5970,10 @@ numeric_stddev_internal(NumericAggState *state,
*is_null = false;
/*
- * Deal with NaN inputs.
+ * Deal with NaN and infinity cases. By analogy to the behavior of the
+ * float8 functions, any infinity input produces NaN output.
*/
- if (state->NaNcount > 0)
+ if (state->NaNcount > 0 || state->pInfcount > 0 || state->nInfcount > 0)
return make_result(&const_nan);
/* OK, normal calculation applies */
@@ -5840,6 +6616,12 @@ dump_numeric(const char *str, Numeric num)
case NUMERIC_NAN:
printf("NaN");
break;
+ case NUMERIC_PINF:
+ printf("Infinity");
+ break;
+ case NUMERIC_NINF:
+ printf("-Infinity");
+ break;
default:
printf("SIGN=0x%x", NUMERIC_SIGN(num));
break;
@@ -5871,6 +6653,12 @@ dump_var(const char *str, NumericVar *var)
case NUMERIC_NAN:
printf("NaN");
break;
+ case NUMERIC_PINF:
+ printf("Infinity");
+ break;
+ case NUMERIC_NINF:
+ printf("-Infinity");
+ break;
default:
printf("SIGN=0x%x", var->sign);
break;
@@ -6425,6 +7213,19 @@ get_str_from_var_sci(const NumericVar *var, int rscale)
}
+/*
+ * duplicate_numeric() - copy a packed-format Numeric
+ */
+static Numeric
+duplicate_numeric(Numeric num)
+{
+ Numeric res;
+
+ res = (Numeric) palloc(VARSIZE(num));
+ memcpy(res, num, VARSIZE(num));
+ return res;
+}
+
/*
* make_result_opt_error() -
*
@@ -6446,12 +7247,22 @@ make_result_opt_error(const NumericVar *var, bool *have_error)
if (have_error)
*have_error = false;
- if (sign == NUMERIC_NAN)
+ if ((sign & NUMERIC_SIGN_MASK) == NUMERIC_SPECIAL)
{
+ /*
+ * Verify valid special value. This could be just an Assert, perhaps,
+ * but it seems worthwhile to expend a few cycles to ensure that we
+ * never write any nonzero reserved bits to disk.
+ */
+ if (!(sign == NUMERIC_NAN ||
+ sign == NUMERIC_PINF ||
+ sign == NUMERIC_NINF))
+ elog(ERROR, "invalid numeric sign value 0x%x", sign);
+
result = (Numeric) palloc(NUMERIC_HDRSZ_SHORT);
SET_VARSIZE(result, NUMERIC_HDRSZ_SHORT);
- result->choice.n_header = NUMERIC_NAN;
+ result->choice.n_header = sign;
/* the header word is all we need */
dump_numeric("make_result()", result);
@@ -6860,6 +7671,9 @@ int128_to_numericvar(int128 val, NumericVar *var)
/*
* Convert numeric to float8; if out of range, return +/- HUGE_VAL
+ *
+ * Note the caller must already have dealt with NaN, but we can
+ * handle infinities here.
*/
static double
numeric_to_double_no_overflow(Numeric num)
@@ -6868,6 +7682,15 @@ numeric_to_double_no_overflow(Numeric num)
double val;
char *endptr;
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ Assert(!NUMERIC_IS_NAN(num));
+ if (NUMERIC_IS_PINF(num))
+ return HUGE_VAL;
+ else
+ return -HUGE_VAL;
+ }
+
tmp = DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(num)));
@@ -6971,6 +7794,28 @@ cmp_var_common(const NumericDigit *var1digits, int var1ndigits,
var1digits, var1ndigits, var1weight);
}
+/*
+ * Test whether a Numeric value is integral
+ */
+static bool
+numeric_is_integral(Numeric num)
+{
+ NumericVar arg;
+
+ /* Reject NaN, but infinities are considered integral */
+ if (NUMERIC_IS_SPECIAL(num))
+ {
+ if (NUMERIC_IS_NAN(num))
+ return false;
+ return true;
+ }
+
+ /* Integral if there are no digits to the right of the decimal point */
+ init_var_from_num(num, &arg);
+
+ return (arg.ndigits == 0 || arg.ndigits <= arg.weight + 1);
+}
+
/*
* add_var() -
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 0604cb65ed..0b7d4ba3c4 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -57,6 +57,7 @@ typedef struct NumericData *Numeric;
* Utility functions in numeric.c
*/
extern bool numeric_is_nan(Numeric num);
+extern bool numeric_is_inf(Numeric num);
int32 numeric_maximum_size(int32 typmod);
extern char *numeric_out_sci(Numeric num, int scale);
extern char *numeric_normalize(Numeric num);
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 3bd184ae29..477fd1205c 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -211,6 +211,18 @@ SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
0 |
(1 row)
+SELECT var_pop('inf'::numeric), var_samp('inf'::numeric);
+ var_pop | var_samp
+---------+----------
+ NaN |
+(1 row)
+
+SELECT stddev_pop('inf'::numeric), stddev_samp('inf'::numeric);
+ stddev_pop | stddev_samp
+------------+-------------
+ NaN |
+(1 row)
+
SELECT var_pop('nan'::numeric), var_samp('nan'::numeric);
var_pop | var_samp
---------+----------
@@ -285,32 +297,74 @@ select avg('NaN'::numeric) from generate_series(1,3);
(1 row)
-- verify correct results for infinite inputs
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('1'), ('infinity')) v(x);
- avg | var_pop
-----------+---------
- Infinity | NaN
+ sum | avg | var_pop
+----------+----------+---------
+ Infinity | Infinity | NaN
(1 row)
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('infinity'), ('1')) v(x);
- avg | var_pop
-----------+---------
- Infinity | NaN
+ sum | avg | var_pop
+----------+----------+---------
+ Infinity | Infinity | NaN
(1 row)
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('infinity'), ('infinity')) v(x);
- avg | var_pop
-----------+---------
- Infinity | NaN
+ sum | avg | var_pop
+----------+----------+---------
+ Infinity | Infinity | NaN
(1 row)
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('infinity')) v(x);
+ sum | avg | var_pop
+-----+-----+---------
+ NaN | NaN | NaN
+(1 row)
+
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('-infinity')) v(x);
+ sum | avg | var_pop
+-----------+-----------+---------
+ -Infinity | -Infinity | NaN
+(1 row)
+
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('1'), ('infinity')) v(x);
+ sum | avg | var_pop
+----------+----------+---------
+ Infinity | Infinity | NaN
+(1 row)
+
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('infinity'), ('1')) v(x);
+ sum | avg | var_pop
+----------+----------+---------
+ Infinity | Infinity | NaN
+(1 row)
+
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('infinity'), ('infinity')) v(x);
+ sum | avg | var_pop
+----------+----------+---------
+ Infinity | Infinity | NaN
+(1 row)
+
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
FROM (VALUES ('-infinity'), ('infinity')) v(x);
- avg | var_pop
------+---------
- NaN | NaN
+ sum | avg | var_pop
+-----+-----+---------
+ NaN | NaN | NaN
+(1 row)
+
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('-infinity'), ('-infinity')) v(x);
+ sum | avg | var_pop
+-----------+-----------+---------
+ -Infinity | -Infinity | NaN
(1 row)
-- test accuracy with a large input offset
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index c7fe63d037..9e87678012 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -660,6 +660,432 @@ SELECT t1.id1, t1.result, t2.expected
-----+--------+----------
(0 rows)
+-- ******************************
+-- * Check behavior with Inf and NaN inputs. It's easiest to handle these
+-- * separately from the num_data framework used above, because some input
+-- * combinations will throw errors.
+-- ******************************
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan'))
+SELECT x1, x2,
+ x1 + x2 AS sum,
+ x1 - x2 AS diff,
+ x1 * x2 AS prod
+FROM v AS v1(x1), v AS v2(x2);
+ x1 | x2 | sum | diff | prod
+-----------+-----------+-----------+-----------+-----------
+ 0 | 0 | 0 | 0 | 0
+ 0 | 1 | 1 | -1 | 0
+ 0 | -1 | -1 | 1 | 0
+ 0 | 4.2 | 4.2 | -4.2 | 0.0
+ 0 | Infinity | Infinity | -Infinity | NaN
+ 0 | -Infinity | -Infinity | Infinity | NaN
+ 0 | NaN | NaN | NaN | NaN
+ 1 | 0 | 1 | 1 | 0
+ 1 | 1 | 2 | 0 | 1
+ 1 | -1 | 0 | 2 | -1
+ 1 | 4.2 | 5.2 | -3.2 | 4.2
+ 1 | Infinity | Infinity | -Infinity | Infinity
+ 1 | -Infinity | -Infinity | Infinity | -Infinity
+ 1 | NaN | NaN | NaN | NaN
+ -1 | 0 | -1 | -1 | 0
+ -1 | 1 | 0 | -2 | -1
+ -1 | -1 | -2 | 0 | 1
+ -1 | 4.2 | 3.2 | -5.2 | -4.2
+ -1 | Infinity | Infinity | -Infinity | -Infinity
+ -1 | -Infinity | -Infinity | Infinity | Infinity
+ -1 | NaN | NaN | NaN | NaN
+ 4.2 | 0 | 4.2 | 4.2 | 0.0
+ 4.2 | 1 | 5.2 | 3.2 | 4.2
+ 4.2 | -1 | 3.2 | 5.2 | -4.2
+ 4.2 | 4.2 | 8.4 | 0.0 | 17.64
+ 4.2 | Infinity | Infinity | -Infinity | Infinity
+ 4.2 | -Infinity | -Infinity | Infinity | -Infinity
+ 4.2 | NaN | NaN | NaN | NaN
+ Infinity | 0 | Infinity | Infinity | NaN
+ Infinity | 1 | Infinity | Infinity | Infinity
+ Infinity | -1 | Infinity | Infinity | -Infinity
+ Infinity | 4.2 | Infinity | Infinity | Infinity
+ Infinity | Infinity | Infinity | NaN | Infinity
+ Infinity | -Infinity | NaN | Infinity | -Infinity
+ Infinity | NaN | NaN | NaN | NaN
+ -Infinity | 0 | -Infinity | -Infinity | NaN
+ -Infinity | 1 | -Infinity | -Infinity | -Infinity
+ -Infinity | -1 | -Infinity | -Infinity | Infinity
+ -Infinity | 4.2 | -Infinity | -Infinity | -Infinity
+ -Infinity | Infinity | NaN | -Infinity | -Infinity
+ -Infinity | -Infinity | -Infinity | NaN | Infinity
+ -Infinity | NaN | NaN | NaN | NaN
+ NaN | 0 | NaN | NaN | NaN
+ NaN | 1 | NaN | NaN | NaN
+ NaN | -1 | NaN | NaN | NaN
+ NaN | 4.2 | NaN | NaN | NaN
+ NaN | Infinity | NaN | NaN | NaN
+ NaN | -Infinity | NaN | NaN | NaN
+ NaN | NaN | NaN | NaN | NaN
+(49 rows)
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan'))
+SELECT x1, x2,
+ x1 / x2 AS quot,
+ x1 % x2 AS mod,
+ div(x1, x2) AS div
+FROM v AS v1(x1), v AS v2(x2) WHERE x2 != 0;
+ x1 | x2 | quot | mod | div
+-----------+-----------+-------------------------+------+-----------
+ 0 | 1 | 0.00000000000000000000 | 0 | 0
+ 1 | 1 | 1.00000000000000000000 | 0 | 1
+ -1 | 1 | -1.00000000000000000000 | 0 | -1
+ 4.2 | 1 | 4.2000000000000000 | 0.2 | 4
+ Infinity | 1 | Infinity | NaN | Infinity
+ -Infinity | 1 | -Infinity | NaN | -Infinity
+ NaN | 1 | NaN | NaN | NaN
+ 0 | -1 | 0.00000000000000000000 | 0 | 0
+ 1 | -1 | -1.00000000000000000000 | 0 | -1
+ -1 | -1 | 1.00000000000000000000 | 0 | 1
+ 4.2 | -1 | -4.2000000000000000 | 0.2 | -4
+ Infinity | -1 | -Infinity | NaN | -Infinity
+ -Infinity | -1 | Infinity | NaN | Infinity
+ NaN | -1 | NaN | NaN | NaN
+ 0 | 4.2 | 0.00000000000000000000 | 0.0 | 0
+ 1 | 4.2 | 0.23809523809523809524 | 1.0 | 0
+ -1 | 4.2 | -0.23809523809523809524 | -1.0 | 0
+ 4.2 | 4.2 | 1.00000000000000000000 | 0.0 | 1
+ Infinity | 4.2 | Infinity | NaN | Infinity
+ -Infinity | 4.2 | -Infinity | NaN | -Infinity
+ NaN | 4.2 | NaN | NaN | NaN
+ 0 | Infinity | 0 | 0 | 0
+ 1 | Infinity | 0 | 1 | 0
+ -1 | Infinity | 0 | -1 | 0
+ 4.2 | Infinity | 0 | 4.2 | 0
+ Infinity | Infinity | NaN | NaN | NaN
+ -Infinity | Infinity | NaN | NaN | NaN
+ NaN | Infinity | NaN | NaN | NaN
+ 0 | -Infinity | 0 | 0 | 0
+ 1 | -Infinity | 0 | 1 | 0
+ -1 | -Infinity | 0 | -1 | 0
+ 4.2 | -Infinity | 0 | 4.2 | 0
+ Infinity | -Infinity | NaN | NaN | NaN
+ -Infinity | -Infinity | NaN | NaN | NaN
+ NaN | -Infinity | NaN | NaN | NaN
+ 0 | NaN | NaN | NaN | NaN
+ 1 | NaN | NaN | NaN | NaN
+ -1 | NaN | NaN | NaN | NaN
+ 4.2 | NaN | NaN | NaN | NaN
+ Infinity | NaN | NaN | NaN | NaN
+ -Infinity | NaN | NaN | NaN | NaN
+ NaN | NaN | NaN | NaN | NaN
+(42 rows)
+
+SELECT 'inf'::numeric / '0';
+ERROR: division by zero
+SELECT '-inf'::numeric / '0';
+ERROR: division by zero
+SELECT 'nan'::numeric / '0';
+ ?column?
+----------
+ NaN
+(1 row)
+
+SELECT '0'::numeric / '0';
+ERROR: division by zero
+SELECT 'inf'::numeric % '0';
+ERROR: division by zero
+SELECT '-inf'::numeric % '0';
+ERROR: division by zero
+SELECT 'nan'::numeric % '0';
+ ?column?
+----------
+ NaN
+(1 row)
+
+SELECT '0'::numeric % '0';
+ERROR: division by zero
+SELECT div('inf'::numeric, '0');
+ERROR: division by zero
+SELECT div('-inf'::numeric, '0');
+ERROR: division by zero
+SELECT div('nan'::numeric, '0');
+ div
+-----
+ NaN
+(1 row)
+
+SELECT div('0'::numeric, '0');
+ERROR: division by zero
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan'))
+SELECT x, -x as minusx, abs(x), floor(x), ceil(x), sign(x), numeric_inc(x) as inc
+FROM v;
+ x | minusx | abs | floor | ceil | sign | inc
+-----------+-----------+----------+-----------+-----------+------+-----------
+ 0 | 0 | 0 | 0 | 0 | 0 | 1
+ 1 | -1 | 1 | 1 | 1 | 1 | 2
+ -1 | 1 | 1 | -1 | -1 | -1 | 0
+ 4.2 | -4.2 | 4.2 | 4 | 5 | 1 | 5.2
+ -7.777 | 7.777 | 7.777 | -8 | -7 | -1 | -6.777
+ Infinity | -Infinity | Infinity | Infinity | Infinity | 1 | Infinity
+ -Infinity | Infinity | Infinity | -Infinity | -Infinity | -1 | -Infinity
+ NaN | NaN | NaN | NaN | NaN | NaN | NaN
+(8 rows)
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan'))
+SELECT x, round(x), round(x,1) as round1, trunc(x), trunc(x,1) as trunc1
+FROM v;
+ x | round | round1 | trunc | trunc1
+-----------+-----------+-----------+-----------+-----------
+ 0 | 0 | 0.0 | 0 | 0.0
+ 1 | 1 | 1.0 | 1 | 1.0
+ -1 | -1 | -1.0 | -1 | -1.0
+ 4.2 | 4 | 4.2 | 4 | 4.2
+ -7.777 | -8 | -7.8 | -7 | -7.7
+ Infinity | Infinity | Infinity | Infinity | Infinity
+ -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
+ NaN | NaN | NaN | NaN | NaN
+(8 rows)
+
+-- the large values fall into the numeric abbreviation code's maximal classes
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('1e340'),('-1e340'),
+ ('inf'),('-inf'),('nan'),
+ ('inf'),('-inf'),('nan'))
+SELECT substring(x::text, 1, 32)
+FROM v ORDER BY x;
+ substring
+----------------------------------
+ -Infinity
+ -Infinity
+ -1000000000000000000000000000000
+ -7.777
+ -1
+ 0
+ 1
+ 4.2
+ 10000000000000000000000000000000
+ Infinity
+ Infinity
+ NaN
+ NaN
+(13 rows)
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('4.2'),('inf'),('nan'))
+SELECT x, sqrt(x)
+FROM v;
+ x | sqrt
+----------+-------------------
+ 0 | 0.000000000000000
+ 1 | 1.000000000000000
+ 4.2 | 2.049390153191920
+ Infinity | Infinity
+ NaN | NaN
+(5 rows)
+
+SELECT sqrt('-1'::numeric);
+ERROR: cannot take square root of a negative number
+SELECT sqrt('-inf'::numeric);
+ERROR: cannot take square root of a negative number
+WITH v(x) AS
+ (VALUES('1'::numeric),('4.2'),('inf'),('nan'))
+SELECT x,
+ log(x),
+ log10(x),
+ ln(x)
+FROM v;
+ x | log | log10 | ln
+----------+--------------------+--------------------+--------------------
+ 1 | 0.0000000000000000 | 0.0000000000000000 | 0.0000000000000000
+ 4.2 | 0.6232492903979005 | 0.6232492903979005 | 1.4350845252893226
+ Infinity | Infinity | Infinity | Infinity
+ NaN | NaN | NaN | NaN
+(4 rows)
+
+SELECT ln('0'::numeric);
+ERROR: cannot take logarithm of zero
+SELECT ln('-1'::numeric);
+ERROR: cannot take logarithm of a negative number
+SELECT ln('-inf'::numeric);
+ERROR: cannot take logarithm of a negative number
+WITH v(x) AS
+ (VALUES('2'::numeric),('4.2'),('inf'),('nan'))
+SELECT x1, x2,
+ log(x1, x2)
+FROM v AS v1(x1), v AS v2(x2);
+ x1 | x2 | log
+----------+----------+--------------------
+ 2 | 2 | 1.0000000000000000
+ 2 | 4.2 | 2.0703893278913979
+ 2 | Infinity | Infinity
+ 2 | NaN | NaN
+ 4.2 | 2 | 0.4830009440873890
+ 4.2 | 4.2 | 1.0000000000000000
+ 4.2 | Infinity | Infinity
+ 4.2 | NaN | NaN
+ Infinity | 2 | 0
+ Infinity | 4.2 | 0
+ Infinity | Infinity | NaN
+ Infinity | NaN | NaN
+ NaN | 2 | NaN
+ NaN | 4.2 | NaN
+ NaN | Infinity | NaN
+ NaN | NaN | NaN
+(16 rows)
+
+SELECT log('0'::numeric, '10');
+ERROR: cannot take logarithm of zero
+SELECT log('10'::numeric, '0');
+ERROR: cannot take logarithm of zero
+SELECT log('-inf'::numeric, '10');
+ERROR: cannot take logarithm of a negative number
+SELECT log('10'::numeric, '-inf');
+ERROR: cannot take logarithm of a negative number
+SELECT log('inf'::numeric, '0');
+ERROR: cannot take logarithm of zero
+SELECT log('inf'::numeric, '-inf');
+ERROR: cannot take logarithm of a negative number
+SELECT log('-inf'::numeric, 'inf');
+ERROR: cannot take logarithm of a negative number
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('2'),('4.2'),('inf'),('nan'))
+SELECT x1, x2,
+ power(x1, x2)
+FROM v AS v1(x1), v AS v2(x2) WHERE x1 != 0 OR x2 >= 0;
+ x1 | x2 | power
+----------+----------+---------------------
+ 0 | 0 | 1.0000000000000000
+ 0 | 1 | 0.0000000000000000
+ 0 | 2 | 0.0000000000000000
+ 0 | 4.2 | 0.0000000000000000
+ 0 | Infinity | 0
+ 0 | NaN | NaN
+ 1 | 0 | 1.0000000000000000
+ 1 | 1 | 1.0000000000000000
+ 1 | 2 | 1.0000000000000000
+ 1 | 4.2 | 1.0000000000000000
+ 1 | Infinity | 1
+ 1 | NaN | 1
+ 2 | 0 | 1.0000000000000000
+ 2 | 1 | 2.0000000000000000
+ 2 | 2 | 4.0000000000000000
+ 2 | 4.2 | 18.379173679952560
+ 2 | Infinity | Infinity
+ 2 | NaN | NaN
+ 4.2 | 0 | 1.0000000000000000
+ 4.2 | 1 | 4.2000000000000000
+ 4.2 | 2 | 17.6400000000000000
+ 4.2 | 4.2 | 414.61691860129675
+ 4.2 | Infinity | Infinity
+ 4.2 | NaN | NaN
+ Infinity | 0 | 1
+ Infinity | 1 | Infinity
+ Infinity | 2 | Infinity
+ Infinity | 4.2 | Infinity
+ Infinity | Infinity | Infinity
+ Infinity | NaN | NaN
+ NaN | 0 | 1
+ NaN | 1 | NaN
+ NaN | 2 | NaN
+ NaN | 4.2 | NaN
+ NaN | Infinity | NaN
+ NaN | NaN | NaN
+(36 rows)
+
+SELECT power('0'::numeric, '-1');
+ERROR: zero raised to a negative power is undefined
+SELECT power('0'::numeric, '-inf');
+ERROR: zero raised to a negative power is undefined
+SELECT power('-1'::numeric, 'inf');
+ power
+-------
+ 1
+(1 row)
+
+SELECT power('-2'::numeric, '3');
+ power
+---------------------
+ -8.0000000000000000
+(1 row)
+
+SELECT power('-2'::numeric, '3.3');
+ERROR: a negative number raised to a non-integer power yields a complex result
+SELECT power('-2'::numeric, '-1');
+ power
+---------------------
+ -0.5000000000000000
+(1 row)
+
+SELECT power('-2'::numeric, '-1.5');
+ERROR: a negative number raised to a non-integer power yields a complex result
+SELECT power('-2'::numeric, 'inf');
+ power
+----------
+ Infinity
+(1 row)
+
+SELECT power('-2'::numeric, '-inf');
+ power
+-------
+ 0
+(1 row)
+
+SELECT power('inf'::numeric, '-2');
+ power
+-------
+ 0
+(1 row)
+
+SELECT power('inf'::numeric, '-inf');
+ power
+-------
+ 0
+(1 row)
+
+SELECT power('-inf'::numeric, '2');
+ power
+----------
+ Infinity
+(1 row)
+
+SELECT power('-inf'::numeric, '3');
+ power
+-----------
+ -Infinity
+(1 row)
+
+SELECT power('-inf'::numeric, '4.5');
+ERROR: a negative number raised to a non-integer power yields a complex result
+SELECT power('-inf'::numeric, '-2');
+ power
+-------
+ 0
+(1 row)
+
+SELECT power('-inf'::numeric, '-3');
+ power
+-------
+ 0
+(1 row)
+
+SELECT power('-inf'::numeric, '0');
+ power
+-------
+ 1
+(1 row)
+
+SELECT power('-inf'::numeric, 'inf');
+ power
+----------
+ Infinity
+(1 row)
+
+SELECT power('-inf'::numeric, '-inf');
+ power
+-------
+ 0
+(1 row)
+
-- ******************************
-- * miscellaneous checks for things that have been broken in the past...
-- ******************************
@@ -696,6 +1122,13 @@ ERROR: numeric field overflow
DETAIL: A field with precision 4, scale 4 must round to an absolute value less than 1.
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
+INSERT INTO fract_only VALUES (9, 'NaN');
+INSERT INTO fract_only VALUES (10, 'Inf'); -- should fail
+ERROR: numeric field overflow
+DETAIL: A field with precision 4, scale 4 cannot hold an infinite value.
+INSERT INTO fract_only VALUES (11, '-Inf'); -- should fail
+ERROR: numeric field overflow
+DETAIL: A field with precision 4, scale 4 cannot hold an infinite value.
SELECT * FROM fract_only;
id | val
----+---------
@@ -705,7 +1138,8 @@ SELECT * FROM fract_only;
5 | 0.9999
7 | 0.0000
8 | 0.0002
-(6 rows)
+ 9 | NaN
+(7 rows)
DROP TABLE fract_only;
-- Check inf/nan conversion behavior
@@ -716,9 +1150,35 @@ SELECT 'NaN'::float8::numeric;
(1 row)
SELECT 'Infinity'::float8::numeric;
-ERROR: cannot convert infinity to numeric
+ numeric
+----------
+ Infinity
+(1 row)
+
SELECT '-Infinity'::float8::numeric;
-ERROR: cannot convert infinity to numeric
+ numeric
+-----------
+ -Infinity
+(1 row)
+
+SELECT 'NaN'::numeric::float8;
+ float8
+--------
+ NaN
+(1 row)
+
+SELECT 'Infinity'::numeric::float8;
+ float8
+----------
+ Infinity
+(1 row)
+
+SELECT '-Infinity'::numeric::float8;
+ float8
+-----------
+ -Infinity
+(1 row)
+
SELECT 'NaN'::float4::numeric;
numeric
---------
@@ -726,9 +1186,59 @@ SELECT 'NaN'::float4::numeric;
(1 row)
SELECT 'Infinity'::float4::numeric;
-ERROR: cannot convert infinity to numeric
+ numeric
+----------
+ Infinity
+(1 row)
+
SELECT '-Infinity'::float4::numeric;
-ERROR: cannot convert infinity to numeric
+ numeric
+-----------
+ -Infinity
+(1 row)
+
+SELECT 'NaN'::numeric::float4;
+ float4
+--------
+ NaN
+(1 row)
+
+SELECT 'Infinity'::numeric::float4;
+ float4
+----------
+ Infinity
+(1 row)
+
+SELECT '-Infinity'::numeric::float4;
+ float4
+-----------
+ -Infinity
+(1 row)
+
+SELECT '42'::int2::numeric;
+ numeric
+---------
+ 42
+(1 row)
+
+SELECT 'NaN'::numeric::int2;
+ERROR: cannot convert NaN to smallint
+SELECT 'Infinity'::numeric::int2;
+ERROR: cannot convert infinity to smallint
+SELECT '-Infinity'::numeric::int2;
+ERROR: cannot convert infinity to smallint
+SELECT 'NaN'::numeric::int4;
+ERROR: cannot convert NaN to integer
+SELECT 'Infinity'::numeric::int4;
+ERROR: cannot convert infinity to integer
+SELECT '-Infinity'::numeric::int4;
+ERROR: cannot convert infinity to integer
+SELECT 'NaN'::numeric::int8;
+ERROR: cannot convert NaN to bigint
+SELECT 'Infinity'::numeric::int8;
+ERROR: cannot convert infinity to bigint
+SELECT '-Infinity'::numeric::int8;
+ERROR: cannot convert infinity to bigint
-- Simple check that ceil(), floor(), and round() work correctly
CREATE TABLE ceil_floor_round (a numeric);
INSERT INTO ceil_floor_round VALUES ('-5.5');
@@ -794,6 +1304,12 @@ SELECT width_bucket('NaN', 3.0, 4.0, 888);
ERROR: operand, lower bound, and upper bound cannot be NaN
SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
ERROR: operand, lower bound, and upper bound cannot be NaN
+SELECT width_bucket('inf', 3.0, 4.0, 888);
+ERROR: operand, lower bound, and upper bound cannot be infinity
+SELECT width_bucket(2.0, 3.0, '-inf', 888);
+ERROR: operand, lower bound, and upper bound cannot be infinity
+SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888);
+ERROR: lower and upper bounds must be finite
-- normal operation
CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8);
COPY width_bucket_test (operand_num) FROM stdin;
@@ -1199,6 +1715,60 @@ SELECT '' AS to_char_23, to_char(val, '9.999EEEE') FROM num_data;
| -2.493e+07
(10 rows)
+WITH v(val) AS
+ (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
+SELECT val,
+ to_char(val, '9.999EEEE') as numeric,
+ to_char(val::float8, '9.999EEEE') as float8,
+ to_char(val::float4, '9.999EEEE') as float4
+FROM v;
+ val | numeric | float8 | float4
+------------+------------+------------+------------
+ 0 | 0.000e+00 | 0.000e+00 | 0.000e+00
+ -4.2 | -4.200e+00 | -4.200e+00 | -4.200e+00
+ 4200000000 | 4.200e+09 | 4.200e+09 | 4.200e+09
+ 0.000012 | 1.200e-05 | 1.200e-05 | 1.200e-05
+ Infinity | #.####### | #.####### | #.#######
+ -Infinity | #.####### | #.####### | #.#######
+ NaN | #.####### | #.####### | #.#######
+(7 rows)
+
+WITH v(val) AS
+ (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
+SELECT val,
+ to_char(val, 'MI9999999999.99') as numeric,
+ to_char(val::float8, 'MI9999999999.99') as float8,
+ to_char(val::float4, 'MI9999999999.99') as float4
+FROM v;
+ val | numeric | float8 | float4
+------------+----------------+----------------+----------------
+ 0 | .00 | .00 | .00
+ -4.2 | - 4.20 | - 4.20 | - 4.20
+ 4200000000 | 4200000000.00 | 4200000000.00 | 4200000000
+ 0.000012 | .00 | .00 | .00
+ Infinity | Infinity | Infinity | Infinity
+ -Infinity | - Infinity | - Infinity | - Infinity
+ NaN | NaN | NaN | NaN
+(7 rows)
+
+WITH v(val) AS
+ (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
+SELECT val,
+ to_char(val, 'MI99.99') as numeric,
+ to_char(val::float8, 'MI99.99') as float8,
+ to_char(val::float4, 'MI99.99') as float4
+FROM v;
+ val | numeric | float8 | float4
+------------+---------+--------+--------
+ 0 | .00 | .00 | .00
+ -4.2 | - 4.20 | - 4.20 | - 4.20
+ 4200000000 | ##.## | ##.## | ##.
+ 0.000012 | .00 | .00 | .00
+ Infinity | ##.## | ##.## | ##.
+ -Infinity | -##.## | -##.## | -##.
+ NaN | ##.## | ##.## | ##.##
+(7 rows)
+
SELECT '' AS to_char_24, to_char('100'::numeric, 'FM999.9');
to_char_24 | to_char
------------+---------
@@ -1426,6 +1996,12 @@ 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');
+INSERT INTO num_input_test(n1) VALUES (' inf ');
+INSERT INTO num_input_test(n1) VALUES (' +inf ');
+INSERT INTO num_input_test(n1) VALUES (' -inf ');
+INSERT INTO num_input_test(n1) VALUES (' Infinity ');
+INSERT INTO num_input_test(n1) VALUES (' +inFinity ');
+INSERT INTO num_input_test(n1) VALUES (' -INFINITY ');
-- bad inputs
INSERT INTO num_input_test(n1) VALUES (' ');
ERROR: invalid input syntax for type numeric: " "
@@ -1459,17 +2035,27 @@ INSERT INTO num_input_test(n1) VALUES (' N aN ');
ERROR: invalid input syntax for type numeric: " N aN "
LINE 1: INSERT INTO num_input_test(n1) VALUES (' N aN ');
^
+INSERT INTO num_input_test(n1) VALUES ('+ infinity');
+ERROR: invalid input syntax for type numeric: "+ infinity"
+LINE 1: INSERT INTO num_input_test(n1) VALUES ('+ infinity');
+ ^
SELECT * FROM num_input_test;
- n1
----------
- 123
- 3245874
- -93853
- 555.50
- -555.50
- NaN
- NaN
-(7 rows)
+ n1
+-----------
+ 123
+ 3245874
+ -93853
+ 555.50
+ -555.50
+ NaN
+ NaN
+ Infinity
+ Infinity
+ -Infinity
+ Infinity
+ Infinity
+ -Infinity
+(13 rows)
--
-- Test some corner cases for multiplication
@@ -1805,6 +2391,24 @@ select exp(1.0::numeric(71,70));
2.7182818284590452353602874713526624977572470936999595749669676277240766
(1 row)
+select exp('nan'::numeric);
+ exp
+-----
+ NaN
+(1 row)
+
+select exp('inf'::numeric);
+ exp
+----------
+ Infinity
+(1 row)
+
+select exp('-inf'::numeric);
+ exp
+-----
+ 0
+(1 row)
+
-- cases that used to generate inaccurate results
select exp(32.999);
exp
@@ -1876,6 +2480,12 @@ select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
ERROR: start value cannot be NaN
select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
ERROR: stop value cannot be NaN
+select * from generate_series('inf'::numeric, 'inf'::numeric, 10::numeric);
+ERROR: start value cannot be infinity
+select * from generate_series(0::numeric, 'inf'::numeric, 10::numeric);
+ERROR: stop value cannot be infinity
+select * from generate_series(0::numeric, '42'::numeric, '-inf'::numeric);
+ERROR: step size cannot be infinity
-- Checks maximum, output is truncated
select (i / (10::numeric ^ 131071))::numeric(1,0)
from generate_series(6 * (10::numeric ^ 131071),
@@ -2081,6 +2691,12 @@ select scale(numeric 'NaN');
(1 row)
+select scale(numeric 'inf');
+ scale
+-------
+
+(1 row)
+
select scale(NULL::numeric);
scale
-------
@@ -2138,6 +2754,12 @@ select min_scale(numeric 'NaN') is NULL; -- should be true
t
(1 row)
+select min_scale(numeric 'inf') is NULL; -- should be true
+ ?column?
+----------
+ t
+(1 row)
+
select min_scale(0); -- no digits
min_scale
-----------
@@ -2207,6 +2829,12 @@ select trim_scale(numeric 'NaN');
NaN
(1 row)
+select trim_scale(numeric 'inf');
+ trim_scale
+------------
+ Infinity
+(1 row)
+
select trim_scale(1.120);
trim_scale
------------
@@ -2280,16 +2908,23 @@ FROM (VALUES (0::numeric, 0::numeric),
(0::numeric, 46375::numeric),
(433125::numeric, 46375::numeric),
(43312.5::numeric, 4637.5::numeric),
- (4331.250::numeric, 463.75000::numeric)) AS v(a, b);
- a | b | gcd | gcd | gcd | gcd
-----------+-----------+---------+---------+---------+---------
- 0 | 0 | 0 | 0 | 0 | 0
- 0 | NaN | NaN | NaN | NaN | NaN
- 0 | 46375 | 46375 | 46375 | 46375 | 46375
- 433125 | 46375 | 875 | 875 | 875 | 875
- 43312.5 | 4637.5 | 87.5 | 87.5 | 87.5 | 87.5
- 4331.250 | 463.75000 | 8.75000 | 8.75000 | 8.75000 | 8.75000
-(6 rows)
+ (4331.250::numeric, 463.75000::numeric),
+ ('inf', '0'),
+ ('inf', '42'),
+ ('inf', 'inf')
+ ) AS v(a, b);
+ a | b | gcd | gcd | gcd | gcd
+----------+-----------+----------+----------+----------+----------
+ 0 | 0 | 0 | 0 | 0 | 0
+ 0 | NaN | NaN | NaN | NaN | NaN
+ 0 | 46375 | 46375 | 46375 | 46375 | 46375
+ 433125 | 46375 | 875 | 875 | 875 | 875
+ 43312.5 | 4637.5 | 87.5 | 87.5 | 87.5 | 87.5
+ 4331.250 | 463.75000 | 8.75000 | 8.75000 | 8.75000 | 8.75000
+ Infinity | 0 | Infinity | Infinity | Infinity | Infinity
+ Infinity | 42 | 42 | 42 | 42 | 42
+ Infinity | Infinity | Infinity | Infinity | Infinity | Infinity
+(9 rows)
--
-- Tests for LCM()
@@ -2301,7 +2936,11 @@ FROM (VALUES (0::numeric, 0::numeric),
(13272::numeric, 13272::numeric),
(423282::numeric, 13272::numeric),
(42328.2::numeric, 1327.2::numeric),
- (4232.820::numeric, 132.72000::numeric)) AS v(a, b);
+ (4232.820::numeric, 132.72000::numeric),
+ ('inf', '0'),
+ ('inf', '42'),
+ ('inf', 'inf')
+ ) AS v(a, b);
a | b | lcm | lcm | lcm | lcm
----------+-----------+--------------+--------------+--------------+--------------
0 | 0 | 0 | 0 | 0 | 0
@@ -2311,7 +2950,10 @@ FROM (VALUES (0::numeric, 0::numeric),
423282 | 13272 | 11851896 | 11851896 | 11851896 | 11851896
42328.2 | 1327.2 | 1185189.6 | 1185189.6 | 1185189.6 | 1185189.6
4232.820 | 132.72000 | 118518.96000 | 118518.96000 | 118518.96000 | 118518.96000
-(7 rows)
+ Infinity | 0 | 0 | 0 | 0 | 0
+ Infinity | 42 | Infinity | Infinity | Infinity | Infinity
+ Infinity | Infinity | Infinity | Infinity | Infinity | Infinity
+(10 rows)
SELECT lcm(9999 * (10::numeric)^131068 + (10::numeric^131068 - 1), 2); -- overflow
ERROR: value overflows numeric format
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index d5fd4045f9..2512852a94 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1872,7 +1872,7 @@ create temp table numerics(
f_numeric numeric
);
insert into numerics values
-(0, '-infinity', '-infinity', '-1000'), -- numeric type lacks infinities
+(0, '-infinity', '-infinity', '-infinity'),
(1, -3, -3, -3),
(2, -1, -1, -1),
(3, 0, 0, 0),
@@ -1880,7 +1880,7 @@ insert into numerics values
(5, 1.12, 1.12, 1.12),
(6, 2, 2, 2),
(7, 100, 100, 100),
-(8, 'infinity', 'infinity', '1000'),
+(8, 'infinity', 'infinity', 'infinity'),
(9, 'NaN', 'NaN', 'NaN');
select id, f_float4, first_value(id) over w, last_value(id) over w
from numerics
@@ -2006,7 +2006,7 @@ window w as (order by f_numeric range between
1 preceding and 1 following);
id | f_numeric | first_value | last_value
----+-----------+-------------+------------
- 0 | -1000 | 0 | 0
+ 0 | -Infinity | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 3
@@ -2014,7 +2014,7 @@ window w as (order by f_numeric range between
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
- 8 | 1000 | 8 | 8
+ 8 | Infinity | 8 | 8
9 | NaN | 9 | 9
(10 rows)
@@ -2024,7 +2024,7 @@ window w as (order by f_numeric range between
1 preceding and 1.1::numeric following);
id | f_numeric | first_value | last_value
----+-----------+-------------+------------
- 0 | -1000 | 0 | 0
+ 0 | -Infinity | 0 | 0
1 | -3 | 1 | 1
2 | -1 | 2 | 3
3 | 0 | 2 | 4
@@ -2032,7 +2032,7 @@ window w as (order by f_numeric range between
5 | 1.12 | 4 | 6
6 | 2 | 4 | 6
7 | 100 | 7 | 7
- 8 | 1000 | 8 | 8
+ 8 | Infinity | 8 | 8
9 | NaN | 9 | 9
(10 rows)
@@ -2044,6 +2044,24 @@ ERROR: RANGE with offset PRECEDING/FOLLOWING is not supported for column type n
LINE 4: 1 preceding and 1.1::float8 following);
^
HINT: Cast the offset value to an appropriate type.
+select id, f_numeric, first_value(id) over w, last_value(id) over w
+from numerics
+window w as (order by f_numeric range between
+ 'inf' preceding and 'inf' following);
+ id | f_numeric | first_value | last_value
+----+-----------+-------------+------------
+ 0 | -Infinity | 0 | 8
+ 1 | -3 | 0 | 8
+ 2 | -1 | 0 | 8
+ 3 | 0 | 0 | 8
+ 4 | 1.1 | 0 | 8
+ 5 | 1.12 | 0 | 8
+ 6 | 2 | 0 | 8
+ 7 | 100 | 0 | 8
+ 8 | Infinity | 0 | 8
+ 9 | NaN | 9 | 9
+(10 rows)
+
select id, f_numeric, first_value(id) over w, last_value(id) over w
from numerics
window w as (order by f_numeric range between
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 044d515507..54f5cf7ecc 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -53,6 +53,8 @@ SELECT var_pop('nan'::float4), var_samp('nan'::float4);
SELECT stddev_pop('nan'::float4), stddev_samp('nan'::float4);
SELECT var_pop(1.0::numeric), var_samp(2.0::numeric);
SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
+SELECT var_pop('inf'::numeric), var_samp('inf'::numeric);
+SELECT stddev_pop('inf'::numeric), stddev_samp('inf'::numeric);
SELECT var_pop('nan'::numeric), var_samp('nan'::numeric);
SELECT stddev_pop('nan'::numeric), stddev_samp('nan'::numeric);
@@ -69,14 +71,26 @@ select sum('NaN'::numeric) from generate_series(1,3);
select avg('NaN'::numeric) from generate_series(1,3);
-- verify correct results for infinite inputs
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('1'), ('infinity')) v(x);
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('infinity'), ('1')) v(x);
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
FROM (VALUES ('infinity'), ('infinity')) v(x);
-SELECT avg(x::float8), var_pop(x::float8)
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('infinity')) v(x);
+SELECT sum(x::float8), avg(x::float8), var_pop(x::float8)
+FROM (VALUES ('-infinity'), ('-infinity')) v(x);
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('1'), ('infinity')) v(x);
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('infinity'), ('1')) v(x);
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('infinity'), ('infinity')) v(x);
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
FROM (VALUES ('-infinity'), ('infinity')) v(x);
+SELECT sum(x::numeric), avg(x::numeric), var_pop(x::numeric)
+FROM (VALUES ('-infinity'), ('-infinity')) v(x);
-- test accuracy with a large input offset
SELECT avg(x::float8), var_pop(x::float8)
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 41475a9a24..9091087ba3 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -634,6 +634,119 @@ SELECT t1.id1, t1.result, t2.expected
WHERE t1.id1 = t2.id
AND t1.result != t2.expected;
+-- ******************************
+-- * Check behavior with Inf and NaN inputs. It's easiest to handle these
+-- * separately from the num_data framework used above, because some input
+-- * combinations will throw errors.
+-- ******************************
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan'))
+SELECT x1, x2,
+ x1 + x2 AS sum,
+ x1 - x2 AS diff,
+ x1 * x2 AS prod
+FROM v AS v1(x1), v AS v2(x2);
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan'))
+SELECT x1, x2,
+ x1 / x2 AS quot,
+ x1 % x2 AS mod,
+ div(x1, x2) AS div
+FROM v AS v1(x1), v AS v2(x2) WHERE x2 != 0;
+
+SELECT 'inf'::numeric / '0';
+SELECT '-inf'::numeric / '0';
+SELECT 'nan'::numeric / '0';
+SELECT '0'::numeric / '0';
+SELECT 'inf'::numeric % '0';
+SELECT '-inf'::numeric % '0';
+SELECT 'nan'::numeric % '0';
+SELECT '0'::numeric % '0';
+SELECT div('inf'::numeric, '0');
+SELECT div('-inf'::numeric, '0');
+SELECT div('nan'::numeric, '0');
+SELECT div('0'::numeric, '0');
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan'))
+SELECT x, -x as minusx, abs(x), floor(x), ceil(x), sign(x), numeric_inc(x) as inc
+FROM v;
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan'))
+SELECT x, round(x), round(x,1) as round1, trunc(x), trunc(x,1) as trunc1
+FROM v;
+
+-- the large values fall into the numeric abbreviation code's maximal classes
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('1e340'),('-1e340'),
+ ('inf'),('-inf'),('nan'),
+ ('inf'),('-inf'),('nan'))
+SELECT substring(x::text, 1, 32)
+FROM v ORDER BY x;
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('4.2'),('inf'),('nan'))
+SELECT x, sqrt(x)
+FROM v;
+
+SELECT sqrt('-1'::numeric);
+SELECT sqrt('-inf'::numeric);
+
+WITH v(x) AS
+ (VALUES('1'::numeric),('4.2'),('inf'),('nan'))
+SELECT x,
+ log(x),
+ log10(x),
+ ln(x)
+FROM v;
+
+SELECT ln('0'::numeric);
+SELECT ln('-1'::numeric);
+SELECT ln('-inf'::numeric);
+
+WITH v(x) AS
+ (VALUES('2'::numeric),('4.2'),('inf'),('nan'))
+SELECT x1, x2,
+ log(x1, x2)
+FROM v AS v1(x1), v AS v2(x2);
+
+SELECT log('0'::numeric, '10');
+SELECT log('10'::numeric, '0');
+SELECT log('-inf'::numeric, '10');
+SELECT log('10'::numeric, '-inf');
+SELECT log('inf'::numeric, '0');
+SELECT log('inf'::numeric, '-inf');
+SELECT log('-inf'::numeric, 'inf');
+
+WITH v(x) AS
+ (VALUES('0'::numeric),('1'),('2'),('4.2'),('inf'),('nan'))
+SELECT x1, x2,
+ power(x1, x2)
+FROM v AS v1(x1), v AS v2(x2) WHERE x1 != 0 OR x2 >= 0;
+
+SELECT power('0'::numeric, '-1');
+SELECT power('0'::numeric, '-inf');
+SELECT power('-1'::numeric, 'inf');
+SELECT power('-2'::numeric, '3');
+SELECT power('-2'::numeric, '3.3');
+SELECT power('-2'::numeric, '-1');
+SELECT power('-2'::numeric, '-1.5');
+SELECT power('-2'::numeric, 'inf');
+SELECT power('-2'::numeric, '-inf');
+SELECT power('inf'::numeric, '-2');
+SELECT power('inf'::numeric, '-inf');
+SELECT power('-inf'::numeric, '2');
+SELECT power('-inf'::numeric, '3');
+SELECT power('-inf'::numeric, '4.5');
+SELECT power('-inf'::numeric, '-2');
+SELECT power('-inf'::numeric, '-3');
+SELECT power('-inf'::numeric, '0');
+SELECT power('-inf'::numeric, 'inf');
+SELECT power('-inf'::numeric, '-inf');
+
-- ******************************
-- * miscellaneous checks for things that have been broken in the past...
-- ******************************
@@ -652,6 +765,9 @@ INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
+INSERT INTO fract_only VALUES (9, 'NaN');
+INSERT INTO fract_only VALUES (10, 'Inf'); -- should fail
+INSERT INTO fract_only VALUES (11, '-Inf'); -- should fail
SELECT * FROM fract_only;
DROP TABLE fract_only;
@@ -659,9 +775,25 @@ DROP TABLE fract_only;
SELECT 'NaN'::float8::numeric;
SELECT 'Infinity'::float8::numeric;
SELECT '-Infinity'::float8::numeric;
+SELECT 'NaN'::numeric::float8;
+SELECT 'Infinity'::numeric::float8;
+SELECT '-Infinity'::numeric::float8;
SELECT 'NaN'::float4::numeric;
SELECT 'Infinity'::float4::numeric;
SELECT '-Infinity'::float4::numeric;
+SELECT 'NaN'::numeric::float4;
+SELECT 'Infinity'::numeric::float4;
+SELECT '-Infinity'::numeric::float4;
+SELECT '42'::int2::numeric;
+SELECT 'NaN'::numeric::int2;
+SELECT 'Infinity'::numeric::int2;
+SELECT '-Infinity'::numeric::int2;
+SELECT 'NaN'::numeric::int4;
+SELECT 'Infinity'::numeric::int4;
+SELECT '-Infinity'::numeric::int4;
+SELECT 'NaN'::numeric::int8;
+SELECT 'Infinity'::numeric::int8;
+SELECT '-Infinity'::numeric::int8;
-- Simple check that ceil(), floor(), and round() work correctly
CREATE TABLE ceil_floor_round (a numeric);
@@ -697,6 +829,9 @@ SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5);
SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
SELECT width_bucket('NaN', 3.0, 4.0, 888);
SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
+SELECT width_bucket('inf', 3.0, 4.0, 888);
+SELECT width_bucket(2.0, 3.0, '-inf', 888);
+SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888);
-- normal operation
CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8);
@@ -782,6 +917,30 @@ SELECT '' AS to_char_21, to_char(val, '999999SG9999999999') FROM num_data;
SELECT '' AS to_char_22, to_char(val, 'FM9999999999999999.999999999999999') FROM num_data;
SELECT '' AS to_char_23, to_char(val, '9.999EEEE') FROM num_data;
+WITH v(val) AS
+ (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
+SELECT val,
+ to_char(val, '9.999EEEE') as numeric,
+ to_char(val::float8, '9.999EEEE') as float8,
+ to_char(val::float4, '9.999EEEE') as float4
+FROM v;
+
+WITH v(val) AS
+ (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
+SELECT val,
+ to_char(val, 'MI9999999999.99') as numeric,
+ to_char(val::float8, 'MI9999999999.99') as float8,
+ to_char(val::float4, 'MI9999999999.99') as float4
+FROM v;
+
+WITH v(val) AS
+ (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
+SELECT val,
+ to_char(val, 'MI99.99') as numeric,
+ to_char(val::float8, 'MI99.99') as float8,
+ to_char(val::float4, 'MI99.99') as float4
+FROM v;
+
SELECT '' AS to_char_24, to_char('100'::numeric, 'FM999.9');
SELECT '' AS to_char_25, to_char('100'::numeric, 'FM999.');
SELECT '' AS to_char_26, to_char('100'::numeric, 'FM999');
@@ -839,6 +998,12 @@ 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');
+INSERT INTO num_input_test(n1) VALUES (' inf ');
+INSERT INTO num_input_test(n1) VALUES (' +inf ');
+INSERT INTO num_input_test(n1) VALUES (' -inf ');
+INSERT INTO num_input_test(n1) VALUES (' Infinity ');
+INSERT INTO num_input_test(n1) VALUES (' +inFinity ');
+INSERT INTO num_input_test(n1) VALUES (' -INFINITY ');
-- bad inputs
INSERT INTO num_input_test(n1) VALUES (' ');
@@ -849,6 +1014,7 @@ 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 ');
+INSERT INTO num_input_test(n1) VALUES ('+ infinity');
SELECT * FROM num_input_test;
@@ -952,6 +1118,9 @@ select 1.234 ^ 5678;
select exp(0.0);
select exp(1.0);
select exp(1.0::numeric(71,70));
+select exp('nan'::numeric);
+select exp('inf'::numeric);
+select exp('-inf'::numeric);
-- cases that used to generate inaccurate results
select exp(32.999);
@@ -973,6 +1142,9 @@ select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
+select * from generate_series('inf'::numeric, 'inf'::numeric, 10::numeric);
+select * from generate_series(0::numeric, 'inf'::numeric, 10::numeric);
+select * from generate_series(0::numeric, '42'::numeric, '-inf'::numeric);
-- Checks maximum, output is truncated
select (i / (10::numeric ^ 131071))::numeric(1,0)
from generate_series(6 * (10::numeric ^ 131071),
@@ -1040,6 +1212,7 @@ select log(3.1954752e47, 9.4792021e-73);
--
select scale(numeric 'NaN');
+select scale(numeric 'inf');
select scale(NULL::numeric);
select scale(1.12);
select scale(0);
@@ -1054,6 +1227,7 @@ select scale(-13.000000000000000);
--
select min_scale(numeric 'NaN') is NULL; -- should be true
+select min_scale(numeric 'inf') is NULL; -- should be true
select min_scale(0); -- no digits
select min_scale(0.00); -- no digits again
select min_scale(1.0); -- no scale
@@ -1070,6 +1244,7 @@ select min_scale(1e100); -- very big number
--
select trim_scale(numeric 'NaN');
+select trim_scale(numeric 'inf');
select trim_scale(1.120);
select trim_scale(0);
select trim_scale(0.00);
@@ -1096,7 +1271,11 @@ FROM (VALUES (0::numeric, 0::numeric),
(0::numeric, 46375::numeric),
(433125::numeric, 46375::numeric),
(43312.5::numeric, 4637.5::numeric),
- (4331.250::numeric, 463.75000::numeric)) AS v(a, b);
+ (4331.250::numeric, 463.75000::numeric),
+ ('inf', '0'),
+ ('inf', '42'),
+ ('inf', 'inf')
+ ) AS v(a, b);
--
-- Tests for LCM()
@@ -1108,6 +1287,10 @@ FROM (VALUES (0::numeric, 0::numeric),
(13272::numeric, 13272::numeric),
(423282::numeric, 13272::numeric),
(42328.2::numeric, 1327.2::numeric),
- (4232.820::numeric, 132.72000::numeric)) AS v(a, b);
+ (4232.820::numeric, 132.72000::numeric),
+ ('inf', '0'),
+ ('inf', '42'),
+ ('inf', 'inf')
+ ) AS v(a, b);
SELECT lcm(9999 * (10::numeric)^131068 + (10::numeric^131068 - 1), 2); -- overflow
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index fe273aa31e..b4e7f6d8c6 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -499,7 +499,7 @@ create temp table numerics(
);
insert into numerics values
-(0, '-infinity', '-infinity', '-1000'), -- numeric type lacks infinities
+(0, '-infinity', '-infinity', '-infinity'),
(1, -3, -3, -3),
(2, -1, -1, -1),
(3, 0, 0, 0),
@@ -507,7 +507,7 @@ insert into numerics values
(5, 1.12, 1.12, 1.12),
(6, 2, 2, 2),
(7, 100, 100, 100),
-(8, 'infinity', 'infinity', '1000'),
+(8, 'infinity', 'infinity', 'infinity'),
(9, 'NaN', 'NaN', 'NaN');
select id, f_float4, first_value(id) over w, last_value(id) over w
@@ -558,6 +558,10 @@ window w as (order by f_numeric range between
1 preceding and 1.1::float8 following); -- currently unsupported
select id, f_numeric, first_value(id) over w, last_value(id) over w
from numerics
+window w as (order by f_numeric range between
+ 'inf' preceding and 'inf' following);
+select id, f_numeric, first_value(id) over w, last_value(id) over w
+from numerics
window w as (order by f_numeric range between
1.1 preceding and 'NaN' following); -- error, NaN disallowed
pgsql-hackers by date: