I noticed that the PostgreSQL entry in a pan-database feature matrix by
Modern SQL was not reflecting the reality of our features.[1]
It turns out that test case used by the author produced an error which
the tool took to mean the feature was not implemented. I don't have the
actual test, but here is a simulation of it:
postgres=# SELECT LAG(n, 1, -99) OVER (ORDER BY n)
postgres-# FROM (VALUES (1.1), (2.2), (3.3)) AS v (n)
postgres-# ORDER BY n;
ERROR: function lag(numeric, integer, integer) does not exist
LINE 1: SELECT LAG(n, 1, -99) OVER (ORDER BY n)
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
Attached is a patch that fixes this issue using the new anycompatible
pseudotype. I am hoping this can be slipped into 13 even though it
requires a catversion bump after BETA1.
I looked for other functions with a similar issue but didn't find any.
[1] https://twitter.com/pg_xocolatl/status/1266694496194093057
--
Vik Fearing
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7c06afd3ea..f1b6f6392e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19577,17 +19577,17 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>lag</primary>
</indexterm>
- <function>lag</function> ( <parameter>value</parameter> <type>anyelement</type>
+ <function>lag</function> ( <parameter>value</parameter> <type>anycompatible</type>
<optional>, <parameter>offset</parameter> <type>integer</type>
- <optional>, <parameter>default</parameter> <type>anyelement</type> </optional></optional> )
- <returnvalue>anyelement</returnvalue>
+ <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+ <returnvalue>anycompatible</returnvalue>
</para>
<para>
Returns <parameter>value</parameter> evaluated at
the row that is <parameter>offset</parameter>
rows before the current row within the partition; if there is no such
row, instead returns <parameter>default</parameter>
- (which must be of the same type as
+ (which must be of a compatible type as
<parameter>value</parameter>).
Both <parameter>offset</parameter> and
<parameter>default</parameter> are evaluated
@@ -19602,17 +19602,17 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>lead</primary>
</indexterm>
- <function>lead</function> ( <parameter>value</parameter> <type>anyelement</type>
+ <function>lead</function> ( <parameter>value</parameter> <type>anycompatible</type>
<optional>, <parameter>offset</parameter> <type>integer</type>
- <optional>, <parameter>default</parameter> <type>anyelement</type> </optional></optional> )
- <returnvalue>anyelement</returnvalue>
+ <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+ <returnvalue>anycompatible</returnvalue>
</para>
<para>
Returns <parameter>value</parameter> evaluated at
the row that is <parameter>offset</parameter>
rows after the current row within the partition; if there is no such
row, instead returns <parameter>default</parameter>
- (which must be of the same type as
+ (which must be of a compatible type as
<parameter>value</parameter>).
Both <parameter>offset</parameter> and
<parameter>default</parameter> are evaluated
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 61f2c2f5b4..4b2d5f6ee6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9689,8 +9689,8 @@
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_lag_with_offset' },
{ oid => '3108', descr => 'fetch the Nth preceding row value with default',
- proname => 'lag', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement int4 anyelement',
+ proname => 'lag', prokind => 'w', prorettype => 'anycompatible',
+ proargtypes => 'anycompatible int4 anycompatible',
prosrc => 'window_lag_with_offset_and_default' },
{ oid => '3109', descr => 'fetch the following row value',
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
@@ -9699,8 +9699,8 @@
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_lead_with_offset' },
{ oid => '3111', descr => 'fetch the Nth following row value with default',
- proname => 'lead', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement int4 anyelement',
+ proname => 'lead', prokind => 'w', prorettype => 'anycompatible',
+ proargtypes => 'anycompatible int4 anycompatible',
prosrc => 'window_lead_with_offset_and_default' },
{ oid => '3112', descr => 'fetch the first row value',
proname => 'first_value', prokind => 'w', prorettype => 'anyelement',
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index d5fd4045f9..b064cd0c97 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -300,6 +300,21 @@ SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM t
0 | 3 | 3
(10 rows)
+SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;
+ lag | ten | four
+-----+-----+------
+ 0 | 0 | 0
+ 0 | 0 | 0
+ 4 | 4 | 0
+ 0.7 | 1 | 1
+ 1 | 1 | 1
+ 1 | 7 | 1
+ 7 | 9 | 1
+ 0.7 | 0 | 2
+ 0.7 | 1 | 3
+ 0.7 | 3 | 3
+(10 rows)
+
SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
lead | ten | four
------+-----+------
@@ -345,6 +360,21 @@ SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FRO
-1 | 3 | 3
(10 rows)
+SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;
+ lead | ten | four
+------+-----+------
+ 0 | 0 | 0
+ 8 | 0 | 0
+ -1.4 | 4 | 0
+ 2 | 1 | 1
+ 14 | 1 | 1
+ 18 | 7 | 1
+ -1.4 | 9 | 1
+ -1.4 | 0 | 2
+ 6 | 1 | 3
+ -1.4 | 3 | 3
+(10 rows)
+
SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
first_value | ten | four
-------------+-----+------
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index fe273aa31e..49570458be 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -63,12 +63,14 @@ SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHER
SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;
SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;
SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
