Skip to content

Commit 7374b3a

Browse files
tglsfdctglsfdc
committed
Allow width_bucket()'s "operand" input to be NaN.
The array-based variant of width_bucket() has always accepted NaN inputs, treating them as equal but larger than any non-NaN, as we do in ordinary comparisons. But up to now, the four-argument variants threw errors for a NaN operand. This is inconsistent and unnecessary, since we can perfectly well regard NaN as falling after the last bucket. We do still throw error for NaN or infinity histogram-bound inputs, since there's no way to compute sensible bucket boundaries. Arguably this is a bug fix, but given the lack of field complaints I'm content to fix it in master. Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com> Discussion: https://postgr.es/m/2822872.1750540911@sss.pgh.pa.us
1 parent c989aff commit 7374b3a

File tree

4 files changed

+32
-21
lines changed

4 files changed

+32
-21
lines changed

‎src/backend/utils/adt/float.c

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -4067,8 +4067,9 @@ float84ge(PG_FUNCTION_ARGS)
40674067
* with the specified characteristics. An operand smaller than the
40684068
* lower bound is assigned to bucket 0. An operand greater than or equal
40694069
* to the upper bound is assigned to an additional bucket (with number
4070-
* count+1). We don't allow "NaN" for any of the float8 inputs, and we
4071-
* don't allow either of the histogram bounds to be +/- infinity.
4070+
* count+1). We don't allow the histogram bounds to be NaN or +/- infinity,
4071+
* but we do allow those values for the operand (taking NaN to be larger
4072+
* than any other value, as we do in comparisons).
40724073
*/
40734074
Datum
40744075
width_bucket_float8(PG_FUNCTION_ARGS)
@@ -4084,28 +4085,27 @@ width_bucket_float8(PG_FUNCTION_ARGS)
40844085
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
40854086
errmsg("count must be greater than zero")));
40864087

4087-
if (isnan(operand) || isnan(bound1) || isnan(bound2))
4088+
if (isnan(bound1) || isnan(bound2))
40884089
ereport(ERROR,
40894090
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
4090-
errmsg("operand, lower bound, and upper bound cannot be NaN")));
4091+
errmsg("lower and upper bounds cannot be NaN")));
40914092

4092-
/* Note that we allow "operand" to be infinite */
40934093
if (isinf(bound1) || isinf(bound2))
40944094
ereport(ERROR,
40954095
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
40964096
errmsg("lower and upper bounds must be finite")));
40974097

40984098
if (bound1 < bound2)
40994099
{
4100-
if (operand < bound1)
4101-
result = 0;
4102-
else if (operand >= bound2)
4100+
if (isnan(operand) || operand >= bound2)
41034101
{
41044102
if (pg_add_s32_overflow(count, 1, &result))
41054103
ereport(ERROR,
41064104
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
41074105
errmsg("integer out of range")));
41084106
}
4107+
else if (operand < bound1)
4108+
result = 0;
41094109
else
41104110
{
41114111
if (!isinf(bound2 - bound1))
@@ -4135,7 +4135,7 @@ width_bucket_float8(PG_FUNCTION_ARGS)
41354135
}
41364136
else if (bound1 > bound2)
41374137
{
4138-
if (operand > bound1)
4138+
if (isnan(operand) || operand > bound1)
41394139
result = 0;
41404140
else if (operand <= bound2)
41414141
{

‎src/backend/utils/adt/numeric.c

Lines changed: 7 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1960,8 +1960,9 @@ generate_series_numeric_support(PG_FUNCTION_ARGS)
19601960
* with the specified characteristics. An operand smaller than the
19611961
* lower bound is assigned to bucket 0. An operand greater than or equal
19621962
* to the upper bound is assigned to an additional bucket (with number
1963-
* count+1). We don't allow "NaN" for any of the numeric inputs, and we
1964-
* don't allow either of the histogram bounds to be +/- infinity.
1963+
* count+1). We don't allow the histogram bounds to be NaN or +/- infinity,
1964+
* but we do allow those values for the operand (taking NaN to be larger
1965+
* than any other value, as we do in comparisons).
19651966
*/
19661967
Datum
19671968
width_bucket_numeric(PG_FUNCTION_ARGS)
@@ -1979,17 +1980,13 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
19791980
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
19801981
errmsg("count must be greater than zero")));
19811982

1982-
if (NUMERIC_IS_SPECIAL(operand) ||
1983-
NUMERIC_IS_SPECIAL(bound1) ||
1984-
NUMERIC_IS_SPECIAL(bound2))
1983+
if (NUMERIC_IS_SPECIAL(bound1) || NUMERIC_IS_SPECIAL(bound2))
19851984
{
1986-
if (NUMERIC_IS_NAN(operand) ||
1987-
NUMERIC_IS_NAN(bound1) ||
1988-
NUMERIC_IS_NAN(bound2))
1985+
if (NUMERIC_IS_NAN(bound1) || NUMERIC_IS_NAN(bound2))
19891986
ereport(ERROR,
19901987
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
1991-
errmsg("operand, lower bound, and upper bound cannot be NaN")));
1992-
/* We allow "operand" to be infinite; cmp_numerics will cope */
1988+
errmsg("lower and upper bounds cannot be NaN")));
1989+
19931990
if (NUMERIC_IS_INF(bound1) || NUMERIC_IS_INF(bound2))
19941991
ereport(ERROR,
19951992
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),

‎src/test/regress/expected/numeric.out

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1464,9 +1464,21 @@ ERROR: count must be greater than zero
14641464
SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
14651465
ERROR: lower bound cannot equal upper bound
14661466
SELECT width_bucket('NaN', 3.0, 4.0, 888);
1467-
ERROR: operand, lower bound, and upper bound cannot be NaN
1467+
width_bucket
1468+
--------------
1469+
889
1470+
(1 row)
1471+
1472+
SELECT width_bucket('NaN'::float8, 3.0::float8, 4.0::float8, 888);
1473+
width_bucket
1474+
--------------
1475+
889
1476+
(1 row)
1477+
1478+
SELECT width_bucket(0, 'NaN', 4.0, 888);
1479+
ERROR: lower and upper bounds cannot be NaN
14681480
SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
1469-
ERROR: operand, lower bound, and upper bound cannot be NaN
1481+
ERROR: lower and upper bounds cannot be NaN
14701482
SELECT width_bucket(2.0, 3.0, '-inf', 888);
14711483
ERROR: lower and upper bounds must be finite
14721484
SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888);

‎src/test/regress/sql/numeric.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -869,6 +869,8 @@ SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0);
869869
SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5);
870870
SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
871871
SELECT width_bucket('NaN', 3.0, 4.0, 888);
872+
SELECT width_bucket('NaN'::float8, 3.0::float8, 4.0::float8, 888);
873+
SELECT width_bucket(0, 'NaN', 4.0, 888);
872874
SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
873875
SELECT width_bucket(2.0, 3.0, '-inf', 888);
874876
SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888);

0 commit comments

Comments
 (0)