On Wed, 21 Jul 2021 at 22:33, Tom Lane <[email protected]> wrote:
>
> I took a brief look at this and have a couple of quick suggestions:
>
Thanks for looking at this!
> * As you mention, keeping some spare bits in the typmod might come
> in handy some day, but as given this patch isn't really doing so.
> I think it might be advisable to mask the scale off at 11 bits,
> preserving the high 5 bits of the low-order half of the word for future
> use. The main objection to that I guess is that it would complicate
> doing sign extension in TYPMOD_SCALE(). But it doesn't seem like we
> use that logic in any really hot code paths, so another instruction
> or three probably is not much of a cost.
>
Yeah, that makes sense, and it's worth documenting where the spare bits are.
Interestingly, gcc recognised the bit hack I used for sign extension
and turned it into (x << 21) >> 21 using x86 shl and sar instructions,
though I didn't write it that way because apparently that's not
portable.
> * I agree with wrapping the typmod construction/extraction into macros
> (or maybe they should be inline functions?) but the names you chose
> seem generic enough to possibly confuse onlookers. I'd suggest
> changing TYPMOD to NUMERIC_TYPMOD or NUM_TYPMOD. The comment for them
> should probably also explicitly explain "For purely historical reasons,
> VARHDRSZ is added to the typmod value after these fields are combined",
> or words to that effect.
>
I've turned them into inline functions, since that makes them easier
to read, and debug if necessary.
All your other suggestions make sense too. Attached is a new version.
Regards,
Dean
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
new file mode 100644
index e016f96..6abda2f
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -545,8 +545,8 @@
<programlisting>
NUMERIC(<replaceable>precision</replaceable>, <replaceable>scale</replaceable>)
</programlisting>
- The precision must be positive, the scale zero or positive.
- Alternatively:
+ The precision must be positive, the scale may be positive or negative
+ (see below). Alternatively:
<programlisting>
NUMERIC(<replaceable>precision</replaceable>)
</programlisting>
@@ -578,11 +578,41 @@ NUMERIC
<para>
If the scale of a value to be stored is greater than the declared
scale of the column, the system will round the value to the specified
- number of fractional digits. Then, if the number of digits to the
- left of the decimal point exceeds the declared precision minus the
- declared scale, an error is raised.
+ number of fractional digits. If the declared scale of the column is
+ negative, the value will be rounded to the left of the decimal point.
+ If, after rounding, the number of digits to the left of the decimal point
+ exceeds the declared precision minus the declared scale, an error is
+ raised. Similarly, if the declared scale exceeds the declared precision
+ and the number of zero digits to the right of the decimal point is less
+ than the declared scale minus the declared precision, an error is raised.
+ For example, a column declared as
+<programlisting>
+NUMERIC(3, 1)
+</programlisting>
+ will round values to 1 decimal place and be able to store values between
+ -99.9 and 99.9, inclusive. A column declared as
+<programlisting>
+NUMERIC(2, -3)
+</programlisting>
+ will round values to the nearest thousand and be able to store values
+ between -99000 and 99000, inclusive. A column declared as
+<programlisting>
+NUMERIC(3, 5)
+</programlisting>
+ will round values to 5 decimal places and be able to store values between
+ -0.00999 and 0.00999, inclusive.
</para>
+ <note>
+ <para>
+ The scale in a <type>NUMERIC</type> type declaration may be any value in
+ the range -1000 to 1000. (The <acronym>SQL</acronym> standard requires
+ the scale to be in the range 0 to <replaceable>precision</replaceable>.
+ Using values outside this range may not be portable to other database
+ systems.)
+ </para>
+ </note>
+
<para>
Numeric values are physically stored without any extra leading or
trailing zeroes. Thus, the declared precision and scale of a column
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
new file mode 100644
index 2a0f68f..46cb37c
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -816,6 +816,52 @@ numeric_is_integral(Numeric num)
}
/*
+ * make_numeric_typmod() -
+ *
+ * Pack numeric precision and scale values into a typmod. The upper 16 bits
+ * are used for the precision (though actually not all these bits are needed,
+ * since the maximum allowed precision is 1000). The lower 16 bits are for
+ * the scale, but since the scale is constrained to the range [-1000, 1000],
+ * we use just the lower 11 of those 16 bits, and leave the remaining 5 bits
+ * unset, for possible future use.
+ *
+ * For purely historical reasons VARHDRSZ is then added to the result, thus
+ * the unused space in the upper 16 bits is not all as freely available as it
+ * might seem.
+ */
+static inline int
+make_numeric_typmod(int precision, int scale)
+{
+ return ((precision << 16) | (scale & 0x7ff)) + VARHDRSZ;
+}
+
+/*
+ * numeric_typmod_precision() -
+ *
+ * Extract the precision from a numeric typmod --- see make_numeric_typmod().
+ */
+static inline int
+numeric_typmod_precision(int typmod)
+{
+ return ((typmod - VARHDRSZ) >> 16) & 0xffff;
+}
+
+/*
+ * numeric_typmod_scale() -
+ *
+ * Extract the scale from a numeric typmod --- see make_numeric_typmod().
+ *
+ * Note that the scale may be negative, so we must do sign extension when
+ * unpacking it. We do this using the bit hack (x^1024)-1024, which sign
+ * extends an 11-bit two's complement number x.
+ */
+static inline int
+numeric_typmod_scale(int typmod)
+{
+ return (((typmod - VARHDRSZ) & 0x7ff) ^ 1024) - 1024;
+}
+
+/*
* numeric_maximum_size() -
*
* Maximum size of a numeric with given typmod, or -1 if unlimited/unknown.
@@ -830,7 +876,7 @@ numeric_maximum_size(int32 typmod)
return -1;
/* precision (ie, max # of digits) is in upper bits of typmod */
- precision = ((typmod - VARHDRSZ) >> 16) & 0xffff;
+ precision = numeric_typmod_precision(typmod);
/*
* This formula computes the maximum number of NumericDigits we could need
@@ -1084,10 +1130,10 @@ numeric_support(PG_FUNCTION_ARGS)
Node *source = (Node *) linitial(expr->args);
int32 old_typmod = exprTypmod(source);
int32 new_typmod = DatumGetInt32(((Const *) typmod)->constvalue);
- int32 old_scale = (old_typmod - VARHDRSZ) & 0xffff;
- int32 new_scale = (new_typmod - VARHDRSZ) & 0xffff;
- int32 old_precision = (old_typmod - VARHDRSZ) >> 16 & 0xffff;
- int32 new_precision = (new_typmod - VARHDRSZ) >> 16 & 0xffff;
+ int32 old_scale = numeric_typmod_scale(old_typmod);
+ int32 new_scale = numeric_typmod_scale(new_typmod);
+ int32 old_precision = numeric_typmod_precision(old_typmod);
+ int32 new_precision = numeric_typmod_precision(new_typmod);
/*
* If new_typmod < VARHDRSZ, the destination is unconstrained;
@@ -1119,11 +1165,11 @@ numeric (PG_FUNCTION_ARGS)
Numeric num = PG_GETARG_NUMERIC(0);
int32 typmod = PG_GETARG_INT32(1);
Numeric new;
- int32 tmp_typmod;
int precision;
int scale;
int ddigits;
int maxdigits;
+ int dscale;
NumericVar var;
/*
@@ -1146,11 +1192,13 @@ numeric (PG_FUNCTION_ARGS)
/*
* Get the precision and scale out of the typmod value
*/
- tmp_typmod = typmod - VARHDRSZ;
- precision = (tmp_typmod >> 16) & 0xffff;
- scale = tmp_typmod & 0xffff;
+ precision = numeric_typmod_precision(typmod);
+ scale = numeric_typmod_scale(typmod);
maxdigits = precision - scale;
+ /* The target display scale is non-negative */
+ dscale = Max(scale, 0);
+
/*
* 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
@@ -1160,17 +1208,17 @@ numeric (PG_FUNCTION_ARGS)
*/
ddigits = (NUMERIC_WEIGHT(num) + 1) * DEC_DIGITS;
if (ddigits <= maxdigits && scale >= NUMERIC_DSCALE(num)
- && (NUMERIC_CAN_BE_SHORT(scale, NUMERIC_WEIGHT(num))
+ && (NUMERIC_CAN_BE_SHORT(dscale, NUMERIC_WEIGHT(num))
|| !NUMERIC_IS_SHORT(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)
- | (scale << NUMERIC_SHORT_DSCALE_SHIFT);
+ | (dscale << NUMERIC_SHORT_DSCALE_SHIFT);
else
new->choice.n_long.n_sign_dscale = NUMERIC_SIGN(new) |
- ((uint16) scale & NUMERIC_DSCALE_MASK);
+ ((uint16) dscale & NUMERIC_DSCALE_MASK);
PG_RETURN_NUMERIC(new);
}
@@ -1206,12 +1254,12 @@ numerictypmodin(PG_FUNCTION_ARGS)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("NUMERIC precision %d must be between 1 and %d",
tl[0], NUMERIC_MAX_PRECISION)));
- if (tl[1] < 0 || tl[1] > tl[0])
+ if (tl[1] < NUMERIC_MIN_SCALE || tl[1] > NUMERIC_MAX_SCALE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("NUMERIC scale %d must be between 0 and precision %d",
- tl[1], tl[0])));
- typmod = ((tl[0] << 16) | tl[1]) + VARHDRSZ;
+ errmsg("NUMERIC scale %d must be between %d and %d",
+ tl[1], NUMERIC_MIN_SCALE, NUMERIC_MAX_SCALE)));
+ typmod = make_numeric_typmod(tl[0], tl[1]);
}
else if (n == 1)
{
@@ -1221,7 +1269,7 @@ numerictypmodin(PG_FUNCTION_ARGS)
errmsg("NUMERIC precision %d must be between 1 and %d",
tl[0], NUMERIC_MAX_PRECISION)));
/* scale defaults to zero */
- typmod = (tl[0] << 16) + VARHDRSZ;
+ typmod = make_numeric_typmod(tl[0], 0);
}
else
{
@@ -1242,8 +1290,8 @@ numerictypmodout(PG_FUNCTION_ARGS)
if (typmod >= 0)
snprintf(res, 64, "(%d,%d)",
- ((typmod - VARHDRSZ) >> 16) & 0xffff,
- (typmod - VARHDRSZ) & 0xffff);
+ numeric_typmod_precision(typmod),
+ numeric_typmod_scale(typmod));
else
*res = '\0';
@@ -7432,14 +7480,17 @@ apply_typmod(NumericVar *var, int32 typm
if (typmod < (int32) (VARHDRSZ))
return;
- typmod -= VARHDRSZ;
- precision = (typmod >> 16) & 0xffff;
- scale = typmod & 0xffff;
+ precision = numeric_typmod_precision(typmod);
+ scale = numeric_typmod_scale(typmod);
maxdigits = precision - scale;
/* Round to target scale (and set var->dscale) */
round_var(var, scale);
+ /* but don't allow var->dscale to be negative */
+ if (var->dscale < 0)
+ var->dscale = 0;
+
/*
* Check for overflow - note we can't do this before rounding, because
* rounding could raise the weight. Also note that the var's weight could
@@ -7517,9 +7568,8 @@ apply_typmod_special(Numeric num, int32
if (typmod < (int32) (VARHDRSZ))
return;
- typmod -= VARHDRSZ;
- precision = (typmod >> 16) & 0xffff;
- scale = typmod & 0xffff;
+ precision = numeric_typmod_precision(typmod);
+ scale = numeric_typmod_scale(typmod);
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
new file mode 100644
index dfc8688..91ac5ed
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -17,12 +17,22 @@
#include "fmgr.h"
/*
- * Limit on the precision (and hence scale) specifiable in a NUMERIC typmod.
- * Note that the implementation limit on the length of a numeric value is
- * much larger --- beware of what you use this for!
+ * Limits on the precision and scale specifiable in a NUMERIC typmod. The
+ * precision is strictly positive, but the scale may be positive or negative.
+ * A negative scale implies rounding before the decimal point.
+ *
+ * Note that the minimum display scale defined below is zero --- we always
+ * display all digits before the decimal point, even when the scale is
+ * negative.
+ *
+ * Note that the implementation limits on the precision and display scale of a
+ * numeric value are much larger --- beware of what you use these for!
*/
#define NUMERIC_MAX_PRECISION 1000
+#define NUMERIC_MIN_SCALE (-1000)
+#define NUMERIC_MAX_SCALE 1000
+
/*
* Internal limits on the scales chosen for calculation results
*/
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
new file mode 100644
index 385e963..cc11995
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2119,6 +2119,69 @@ SELECT * FROM num_input_test;
(13 rows)
--
+-- Test precision and scale typemods
+--
+CREATE TABLE num_typemod_test (
+ millions numeric(3, -6),
+ thousands numeric(3, -3),
+ units numeric(3, 0),
+ thousandths numeric(3, 3),
+ millionths numeric(3, 6)
+);
+\d num_typemod_test
+ Table "public.num_typemod_test"
+ Column | Type | Collation | Nullable | Default
+-------------+---------------+-----------+----------+---------
+ millions | numeric(3,-6) | | |
+ thousands | numeric(3,-3) | | |
+ units | numeric(3,0) | | |
+ thousandths | numeric(3,3) | | |
+ millionths | numeric(3,6) | | |
+
+-- rounding of valid inputs
+INSERT INTO num_typemod_test VALUES (123456, 123, 0.123, 0.000123, 0.000000123);
+INSERT INTO num_typemod_test VALUES (654321, 654, 0.654, 0.000654, 0.000000654);
+INSERT INTO num_typemod_test VALUES (2345678, 2345, 2.345, 0.002345, 0.000002345);
+INSERT INTO num_typemod_test VALUES (7654321, 7654, 7.654, 0.007654, 0.000007654);
+INSERT INTO num_typemod_test VALUES (12345678, 12345, 12.345, 0.012345, 0.000012345);
+INSERT INTO num_typemod_test VALUES (87654321, 87654, 87.654, 0.087654, 0.000087654);
+INSERT INTO num_typemod_test VALUES (123456789, 123456, 123.456, 0.123456, 0.000123456);
+INSERT INTO num_typemod_test VALUES (987654321, 987654, 987.654, 0.987654, 0.000987654);
+INSERT INTO num_typemod_test VALUES ('NaN', 'NaN', 'NaN', 'NaN', 'NaN');
+SELECT scale(millions), * FROM num_typemod_test ORDER BY millions;
+ scale | millions | thousands | units | thousandths | millionths
+-------+-----------+-----------+-------+-------------+------------
+ 0 | 0 | 0 | 0 | 0.000 | 0.000000
+ 0 | 1000000 | 1000 | 1 | 0.001 | 0.000001
+ 0 | 2000000 | 2000 | 2 | 0.002 | 0.000002
+ 0 | 8000000 | 8000 | 8 | 0.008 | 0.000008
+ 0 | 12000000 | 12000 | 12 | 0.012 | 0.000012
+ 0 | 88000000 | 88000 | 88 | 0.088 | 0.000088
+ 0 | 123000000 | 123000 | 123 | 0.123 | 0.000123
+ 0 | 988000000 | 988000 | 988 | 0.988 | 0.000988
+ | NaN | NaN | NaN | NaN | NaN
+(9 rows)
+
+-- invalid inputs
+INSERT INTO num_typemod_test (millions) VALUES ('inf');
+ERROR: numeric field overflow
+DETAIL: A field with precision 3, scale -6 cannot hold an infinite value.
+INSERT INTO num_typemod_test (millions) VALUES (999500000);
+ERROR: numeric field overflow
+DETAIL: A field with precision 3, scale -6 must round to an absolute value less than 10^9.
+INSERT INTO num_typemod_test (thousands) VALUES (999500);
+ERROR: numeric field overflow
+DETAIL: A field with precision 3, scale -3 must round to an absolute value less than 10^6.
+INSERT INTO num_typemod_test (units) VALUES (999.5);
+ERROR: numeric field overflow
+DETAIL: A field with precision 3, scale 0 must round to an absolute value less than 10^3.
+INSERT INTO num_typemod_test (thousandths) VALUES (0.9995);
+ERROR: numeric field overflow
+DETAIL: A field with precision 3, scale 3 must round to an absolute value less than 1.
+INSERT INTO num_typemod_test (millionths) VALUES (0.0009995);
+ERROR: numeric field overflow
+DETAIL: A field with precision 3, scale 6 must round to an absolute value less than 10^-3.
+--
-- Test some corner cases for multiplication
--
select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
new file mode 100644
index a64f96e..982b6af
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -92,6 +92,7 @@ num_exp_sqrt|t
num_exp_sub|t
num_input_test|f
num_result|f
+num_typemod_test|f
nummultirange_test|t
numrange_test|t
onek|t
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
new file mode 100644
index 7e17c28..14b4acf
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1033,6 +1033,40 @@ INSERT INTO num_input_test(n1) VALUES ('
SELECT * FROM num_input_test;
--
+-- Test precision and scale typemods
+--
+
+CREATE TABLE num_typemod_test (
+ millions numeric(3, -6),
+ thousands numeric(3, -3),
+ units numeric(3, 0),
+ thousandths numeric(3, 3),
+ millionths numeric(3, 6)
+);
+\d num_typemod_test
+
+-- rounding of valid inputs
+INSERT INTO num_typemod_test VALUES (123456, 123, 0.123, 0.000123, 0.000000123);
+INSERT INTO num_typemod_test VALUES (654321, 654, 0.654, 0.000654, 0.000000654);
+INSERT INTO num_typemod_test VALUES (2345678, 2345, 2.345, 0.002345, 0.000002345);
+INSERT INTO num_typemod_test VALUES (7654321, 7654, 7.654, 0.007654, 0.000007654);
+INSERT INTO num_typemod_test VALUES (12345678, 12345, 12.345, 0.012345, 0.000012345);
+INSERT INTO num_typemod_test VALUES (87654321, 87654, 87.654, 0.087654, 0.000087654);
+INSERT INTO num_typemod_test VALUES (123456789, 123456, 123.456, 0.123456, 0.000123456);
+INSERT INTO num_typemod_test VALUES (987654321, 987654, 987.654, 0.987654, 0.000987654);
+INSERT INTO num_typemod_test VALUES ('NaN', 'NaN', 'NaN', 'NaN', 'NaN');
+
+SELECT scale(millions), * FROM num_typemod_test ORDER BY millions;
+
+-- invalid inputs
+INSERT INTO num_typemod_test (millions) VALUES ('inf');
+INSERT INTO num_typemod_test (millions) VALUES (999500000);
+INSERT INTO num_typemod_test (thousands) VALUES (999500);
+INSERT INTO num_typemod_test (units) VALUES (999.5);
+INSERT INTO num_typemod_test (thousandths) VALUES (0.9995);
+INSERT INTO num_typemod_test (millionths) VALUES (0.0009995);
+
+--
-- Test some corner cases for multiplication
--
