Enterprise Postgres 17 リリース情報

2.2.181 ウィンドウ関数の動作の変更

非互換

FUJITSU Enterprise Postgres 14では、範囲が無限大の場合、ウィンドウ関数の結果を変更する場合があります。

FUJITSU Enterprise Postgres 13 SP1以前の場合

[例]

=# CREATE TABLE test(c1 int, c2 float4, c3 float8, c4 numeric);
CREATE TABLE
=# INSERT INTO test VALUES (0, '-inf', '-inf', 1000);
INSERT 0 1
=# SELECT c1 ,c2 , first_value(c1) over w, last_value(c1) OVER w FROM test WINDOW w AS (ORDER BY c2 RANGE BETWEEN 'inf' PRECEDING AND 'inf' PRECEDING);
 c1 |    c2     | first_value | last_value
----+-----------+-------------+------------
  0 | -Infinity |             |
(1 row)

=# SELECT c1 ,c2 , first_value(c1) over w, last_value(c1) OVER w FROM test WINDOW w AS (ORDER BY c2 RANGE BETWEEN 'inf' FOLLOWING AND 'inf' FOLLOWING);
 c1 |    c2     | first_value | last_value
----+-----------+-------------+------------
  0 | -Infinity |             |
(1 row)

FUJITSU Enterprise Postgres 14の場合

[例]

=# CREATE TABLE test(c1 int, c2 float4, c3 float8, c4 numeric);
CREATE TABLE
=# INSERT INTO test VALUES (0, '-inf', '-inf', 1000);
INSERT 0 1
=# SELECT c1 ,c2 , first_value(c1) over w, last_value(c1) OVER w FROM test WINDOW w AS (ORDER BY c2 RANGE BETWEEN 'inf' PRECEDING AND 'inf' PRECEDING);
 c1 |    c2     | first_value | last_value
----+-----------+-------------+------------
  0 | -Infinity |           0 |          0
(1 row)

=# SELECT c1 ,c2 , first_value(c1) over w, last_value(c1) OVER w FROM test WINDOW w AS (ORDER BY c2 RANGE BETWEEN 'inf' FOLLOWING AND 'inf' FOLLOWING);
 c1 |    c2     | first_value | last_value
----+-----------+-------------+------------
  0 | -Infinity |           0 |          0
(1 row)

対処方法

ありません。