From 5c292e6b90433c760a3e15027646c7b94afd0cdd Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 4 Nov 2020 15:08:37 -0500 Subject: Declare lead() and lag() using anycompatible not anyelement. This allows use of a "default" expression that doesn't slavishly match the data column's type. Formerly you got something like "function lag(numeric, integer, integer) does not exist", which is not just unhelpful but actively misleading. The SQL spec suggests that the default should be coerced to the data column's type, but this implementation instead chooses the common supertype, which seems at least as reasonable. (Note: I took the opportunity to run "make reformat-dat-files" on pg_proc.dat, so this commit includes some cosmetic changes to recently-added entries that aren't related to lead/lag.) Vik Fearing Discussion: https://postgr.es/m/77675130-89da-dab1-51dd-492c93dcf5d1@postgresfriends.org --- doc/src/sgml/func.sgml | 16 ++++++++-------- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 32 +++++++++++++++++--------------- src/test/regress/expected/window.out | 30 ++++++++++++++++++++++++++++++ src/test/regress/sql/window.sql | 2 ++ 5 files changed, 58 insertions(+), 24 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 87a6ba8d10f..46d6db3cfe0 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19594,17 +19594,17 @@ SELECT count(*) FROM sometable; lag - lag ( value anyelement + lag ( value anycompatible , offset integer - , default anyelement ) - anyelement + , default anycompatible ) + anycompatible Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead returns default - (which must be of the same type as + (which must be of a type compatible with value). Both offset and default are evaluated @@ -19619,17 +19619,17 @@ SELECT count(*) FROM sometable; lead - lead ( value anyelement + lead ( value anycompatible , offset integer - , default anyelement ) - anyelement + , default anycompatible ) + anycompatible Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default - (which must be of the same type as + (which must be of a type compatible with value). Both offset and default are evaluated diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index b3d0bc609bc..77102bab8db 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202011042 +#define CATALOG_VERSION_NO 202011043 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 88378953620..9acdb28d939 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -1563,11 +1563,11 @@ proname => 'string_to_array', proisstrict => 'f', prorettype => '_text', proargtypes => 'text text text', prosrc => 'text_to_array_null' }, { oid => '8432', descr => 'split delimited text', - proname => 'string_to_table', proisstrict => 'f', prorows => '1000', + proname => 'string_to_table', prorows => '1000', proisstrict => 'f', proretset => 't', prorettype => 'text', proargtypes => 'text text', prosrc => 'text_to_table' }, { oid => '8433', descr => 'split delimited text, with null string', - proname => 'string_to_table', proisstrict => 'f', prorows => '1000', + proname => 'string_to_table', prorows => '1000', proisstrict => 'f', proretset => 't', prorettype => 'text', proargtypes => 'text text text', prosrc => 'text_to_table_null' }, { oid => '395', @@ -5257,8 +5257,8 @@ proargnames => '{pid,status,receive_start_lsn,receive_start_tli,written_lsn,flushed_lsn,received_tli,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time,slot_name,sender_host,sender_port,conninfo}', prosrc => 'pg_stat_get_wal_receiver' }, { oid => '8595', descr => 'statistics: information about replication slots', - proname => 'pg_stat_get_replication_slots', prorows => '10', proisstrict => 'f', - proretset => 't', provolatile => 's', proparallel => 'r', + proname => 'pg_stat_get_replication_slots', prorows => '10', + proisstrict => 'f', proretset => 't', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => '', proallargtypes => '{text,int8,int8,int8,int8,int8,int8,timestamptz}', proargmodes => '{o,o,o,o,o,o,o,o}', @@ -5491,8 +5491,7 @@ { oid => '1136', descr => 'statistics: information about WAL activity', proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => '', - proallargtypes => '{int8,timestamptz}', - proargmodes => '{o,o}', + proallargtypes => '{int8,timestamptz}', proargmodes => '{o,o}', proargnames => '{wal_buffers_full,stats_reset}', prosrc => 'pg_stat_get_wal' }, @@ -5615,8 +5614,9 @@ prorettype => 'void', proargtypes => 'text', prosrc => 'pg_stat_reset_slru' }, { oid => '8596', descr => 'statistics: reset collected statistics for a single replication slot', - proname => 'pg_stat_reset_replication_slot', proisstrict => 'f', provolatile => 'v', - prorettype => 'void', proargtypes => 'text', prosrc => 'pg_stat_reset_replication_slot' }, + proname => 'pg_stat_reset_replication_slot', proisstrict => 'f', + provolatile => 'v', prorettype => 'void', proargtypes => 'text', + prosrc => 'pg_stat_reset_replication_slot' }, { oid => '3163', descr => 'current trigger depth', proname => 'pg_trigger_depth', provolatile => 's', proparallel => 'r', @@ -7833,9 +7833,11 @@ prosrc => 'pg_get_shmem_allocations' }, # memory context of local backend -{ oid => '2282', descr => 'information about all memory contexts of local backend', - proname => 'pg_get_backend_memory_contexts', prorows => '100', proretset => 't', - provolatile => 'v', proparallel => 'r', prorettype => 'record', proargtypes => '', +{ oid => '2282', + descr => 'information about all memory contexts of local backend', + proname => 'pg_get_backend_memory_contexts', prorows => '100', + proretset => 't', provolatile => 'v', proparallel => 'r', + prorettype => 'record', proargtypes => '', proallargtypes => '{text,text,text,int4,int8,int8,int8,int8,int8}', proargmodes => '{o,o,o,o,o,o,o,o,o}', proargnames => '{name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes}', @@ -9748,8 +9750,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', @@ -9758,8 +9760,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 21c6cac491f..19e2ac518af 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 9485aebce85..eae5fa60178 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; -- cgit v1.2.3