Fix numeric width_bucket() to allow its first argument to be infinite.

tglsfdc · tglsfdc · commit 8ce423b1912b · 2020-10-08T12:37:59.000-04:00
While the calculation is not well-defined if the bounds arguments are infinite, there is a perfectly sane outcome if the test operand is infinite: it's just like any other value that's before the first bucket or after the last one. width_bucket_float8() got this right, but I was too hasty about the case when adding infinities to numerics (commit a57d312), so that width_bucket_numeric() just rejected it. Fix that, and sync the relevant error message strings. No back-patch needed, since infinities-in-numeric haven't shipped yet. Discussion: https://postgr.es/m/2465409.1602170063@sss.pgh.pa.us
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
@@ -1724,10 +1724,11 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
 					 errmsg("operand, lower bound, and upper bound cannot be NaN")));
-		else
+		/* We allow "operand" to be infinite; cmp_numerics will cope */
+		if (NUMERIC_IS_INF(bound1) || NUMERIC_IS_INF(bound2))
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
-					 errmsg("operand, lower bound, and upper bound cannot be infinity")));
+					 errmsg("lower and upper bounds must be finite")));
 	}
 
 	init_var(&result_var);
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
@@ -1316,10 +1316,8 @@ 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
+ERROR:  lower and upper bounds must be finite
 SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888);
 ERROR:  lower and upper bounds must be finite
 -- normal operation
@@ -1362,8 +1360,19 @@ SELECT
  10.0000000000001 |    6 |     6 |    0 |     0 |    5 |     5 |   21 |    21 |    8 |     8
 (19 rows)
 
--- for float8 only, check positive and negative infinity: we require
+-- Check positive and negative infinity: we require
 -- finite bucket bounds, but allow an infinite operand
+SELECT width_bucket(0.0::numeric, 'Infinity'::numeric, 5, 10); -- error
+ERROR:  lower and upper bounds must be finite
+SELECT width_bucket(0.0::numeric, 5, '-Infinity'::numeric, 20); -- error
+ERROR:  lower and upper bounds must be finite
+SELECT width_bucket('Infinity'::numeric, 1, 10, 10),
+       width_bucket('-Infinity'::numeric, 1, 10, 10);
+ width_bucket | width_bucket 
+--------------+--------------
+           11 |            0
+(1 row)
+
 SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error
 ERROR:  lower and upper bounds must be finite
 SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
@@ -831,7 +831,6 @@ 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);
 
@@ -876,8 +875,12 @@ SELECT
     width_bucket(operand_f8, -25, 25, 10) AS wb_5f
     FROM width_bucket_test;
 
--- for float8 only, check positive and negative infinity: we require
+-- Check positive and negative infinity: we require
 -- finite bucket bounds, but allow an infinite operand
+SELECT width_bucket(0.0::numeric, 'Infinity'::numeric, 5, 10); -- error
+SELECT width_bucket(0.0::numeric, 5, '-Infinity'::numeric, 20); -- error
+SELECT width_bucket('Infinity'::numeric, 1, 10, 10),
+       width_bucket('-Infinity'::numeric, 1, 10, 10);
 SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error
 SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error
 SELECT width_bucket('Infinity'::float8, 1, 10, 10),
