diff options
| -rw-r--r-- | src/backend/utils/adt/float.c | 46 | ||||
| -rw-r--r-- | src/test/regress/expected/window.out | 72 | ||||
| -rw-r--r-- | src/test/regress/sql/window.sql | 16 |
3 files changed, 118 insertions, 16 deletions
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c index 50ec3d3dde5..ffd1ce8c761 100644 --- a/src/backend/utils/adt/float.c +++ b/src/backend/utils/adt/float.c @@ -1088,18 +1088,25 @@ in_range_float8_float8(PG_FUNCTION_ARGS) } /* - * Deal with infinite offset (necessarily +inf, at this point). We must - * special-case this because if base happens to be -inf, their sum would - * be NaN, which is an overflow-ish condition we should avoid. + * Deal with cases where both base and offset are infinite, and computing + * base +/- offset would produce NaN. This corresponds to a window frame + * whose boundary infinitely precedes +inf or infinitely follows -inf, + * which is not well-defined. For consistency with other cases involving + * infinities, such as the fact that +inf infinitely follows +inf, we + * choose to assume that +inf infinitely precedes +inf and -inf infinitely + * follows -inf, and therefore that all finite and infinite values are in + * such a window frame. + * + * offset is known positive, so we need only check the sign of base in + * this test. */ - if (isinf(offset)) - { - PG_RETURN_BOOL(sub ? !less : less); - } + if (isinf(offset) && isinf(base) && + (sub ? base > 0 : base < 0)) + PG_RETURN_BOOL(true); /* * Otherwise it should be safe to compute base +/- offset. We trust the - * FPU to cope if base is +/-inf or the true sum would overflow, and + * FPU to cope if an input is +/-inf or the true sum would overflow, and * produce a suitably signed infinity, which will compare properly against * val whether or not that's infinity. */ @@ -1157,18 +1164,25 @@ in_range_float4_float8(PG_FUNCTION_ARGS) } /* - * Deal with infinite offset (necessarily +inf, at this point). We must - * special-case this because if base happens to be -inf, their sum would - * be NaN, which is an overflow-ish condition we should avoid. + * Deal with cases where both base and offset are infinite, and computing + * base +/- offset would produce NaN. This corresponds to a window frame + * whose boundary infinitely precedes +inf or infinitely follows -inf, + * which is not well-defined. For consistency with other cases involving + * infinities, such as the fact that +inf infinitely follows +inf, we + * choose to assume that +inf infinitely precedes +inf and -inf infinitely + * follows -inf, and therefore that all finite and infinite values are in + * such a window frame. + * + * offset is known positive, so we need only check the sign of base in + * this test. */ - if (isinf(offset)) - { - PG_RETURN_BOOL(sub ? !less : less); - } + if (isinf(offset) && isinf(base) && + (sub ? base > 0 : base < 0)) + PG_RETURN_BOOL(true); /* * Otherwise it should be safe to compute base +/- offset. We trust the - * FPU to cope if base is +/-inf or the true sum would overflow, and + * FPU to cope if an input is +/-inf or the true sum would overflow, and * produce a suitably signed infinity, which will compare properly against * val whether or not that's infinity. */ diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index d5fd4045f9f..432edfa0630 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -1939,6 +1939,42 @@ window w as (order by f_float4 range between select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between + 'inf' preceding and 'inf' preceding); + id | f_float4 | first_value | last_value +----+-----------+-------------+------------ + 0 | -Infinity | 0 | 0 + 1 | -3 | 0 | 0 + 2 | -1 | 0 | 0 + 3 | 0 | 0 | 0 + 4 | 1.1 | 0 | 0 + 5 | 1.12 | 0 | 0 + 6 | 2 | 0 | 0 + 7 | 100 | 0 | 0 + 8 | Infinity | 0 | 8 + 9 | NaN | 9 | 9 +(10 rows) + +select id, f_float4, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float4 range between + 'inf' following and 'inf' following); + id | f_float4 | first_value | last_value +----+-----------+-------------+------------ + 0 | -Infinity | 0 | 8 + 1 | -3 | 8 | 8 + 2 | -1 | 8 | 8 + 3 | 0 | 8 | 8 + 4 | 1.1 | 8 | 8 + 5 | 1.12 | 8 | 8 + 6 | 2 | 8 | 8 + 7 | 100 | 8 | 8 + 8 | Infinity | 8 | 8 + 9 | NaN | 9 | 9 +(10 rows) + +select id, f_float4, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float4 range between 1.1 preceding and 'NaN' following); -- error, NaN disallowed ERROR: invalid preceding or following size in window function select id, f_float8, first_value(id) over w, last_value(id) over w @@ -1998,6 +2034,42 @@ window w as (order by f_float8 range between select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between + 'inf' preceding and 'inf' preceding); + id | f_float8 | first_value | last_value +----+-----------+-------------+------------ + 0 | -Infinity | 0 | 0 + 1 | -3 | 0 | 0 + 2 | -1 | 0 | 0 + 3 | 0 | 0 | 0 + 4 | 1.1 | 0 | 0 + 5 | 1.12 | 0 | 0 + 6 | 2 | 0 | 0 + 7 | 100 | 0 | 0 + 8 | Infinity | 0 | 8 + 9 | NaN | 9 | 9 +(10 rows) + +select id, f_float8, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float8 range between + 'inf' following and 'inf' following); + id | f_float8 | first_value | last_value +----+-----------+-------------+------------ + 0 | -Infinity | 0 | 8 + 1 | -3 | 8 | 8 + 2 | -1 | 8 | 8 + 3 | 0 | 8 | 8 + 4 | 1.1 | 8 | 8 + 5 | 1.12 | 8 | 8 + 6 | 2 | 8 | 8 + 7 | 100 | 8 | 8 + 8 | Infinity | 8 | 8 + 9 | NaN | 9 | 9 +(10 rows) + +select id, f_float8, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float8 range between 1.1 preceding and 'NaN' following); -- error, NaN disallowed ERROR: invalid preceding or following size in window function select id, f_numeric, first_value(id) over w, last_value(id) over w diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index fe273aa31e6..51ec0bac9ad 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -525,6 +525,14 @@ window w as (order by f_float4 range between select id, f_float4, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float4 range between + 'inf' preceding and 'inf' preceding); +select id, f_float4, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float4 range between + 'inf' following and 'inf' following); +select id, f_float4, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float4 range between 1.1 preceding and 'NaN' following); -- error, NaN disallowed select id, f_float8, first_value(id) over w, last_value(id) over w @@ -542,6 +550,14 @@ window w as (order by f_float8 range between select id, f_float8, first_value(id) over w, last_value(id) over w from numerics window w as (order by f_float8 range between + 'inf' preceding and 'inf' preceding); +select id, f_float8, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float8 range between + 'inf' following and 'inf' following); +select id, f_float8, first_value(id) over w, last_value(id) over w +from numerics +window w as (order by f_float8 range between 1.1 preceding and 'NaN' following); -- error, NaN disallowed select id, f_numeric, first_value(id) over w, last_value(id) over w |