Subject: ANY_VALUE aggregate

From: Vik Fearing <[email-masked]>
Date: Mon, 5 Dec 2022 15:57:13 +0100

The SQL:2023 Standard defines a new aggregate named ANY_VALUE.  It 
returns an implementation-dependent (i.e. non-deterministic) value from 
the rows in its group.

PFA an implementation of this aggregate.

Ideally, the transition function would stop being called after the first 
non-null was found, and then the entire aggregation would stop when all 
functions say they are finished[*], but this patch does not go anywhere 
near that far.

This patch is based off of commit fb958b5da8.

[*] I can imagine something like array_agg(c ORDER BY x LIMIT 5) to get 
the top five of something without going through a LATERAL subquery.
-- 
Vik Fearing

From 7465fac12fc636ff26088ae31de2937f7c3a459f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Sat, 9 Apr 2022 00:07:38 +0200
Subject: [PATCH] Implement ANY_VALUE aggregate

SQL:2023 defines an ANY_VALUE aggregate whose purpose is to emit an
implementation-dependent (i.e. non-deterministic) value from the
aggregated rows.
---
 doc/src/sgml/func.sgml                   | 14 ++++++++++++++
 src/backend/utils/adt/misc.c             | 12 ++++++++++++
 src/include/catalog/pg_aggregate.dat     |  4 ++++
 src/include/catalog/pg_proc.dat          |  8 ++++++++
 src/test/regress/expected/aggregates.out | 18 ++++++++++++++++++
 src/test/regress/sql/aggregates.sql      |  5 +++++
 6 files changed, 61 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2052d3c844..1823ee71d7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19706,16 +19706,30 @@ SELECT NULLIF(value, '(none)') ...
        <para>
         Description
        </para></entry>
        <entry>Partial Mode</entry>
       </row>
      </thead>
 
      <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>any_value</primary>
+        </indexterm>
+        <function>any_value</function> ( <type>"any"</type> )
+        <returnvalue><replaceable>same as input type</replaceable></returnvalue>
+       </para>
+       <para>
+        Chooses a non-deterministic value from the non-null input values.
+       </para></entry>
+       <entry>Yes</entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>array_agg</primary>
         </indexterm>
         <function>array_agg</function> ( <type>anynonarray</type> )
         <returnvalue>anyarray</returnvalue>
        </para>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 9c13251231..94c92de06d 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -928,8 +928,20 @@ pg_get_replica_identity_index(PG_FUNCTION_ARGS)
 	idxoid = RelationGetReplicaIndex(rel);
 	table_close(rel, AccessShareLock);
 
 	if (OidIsValid(idxoid))
 		PG_RETURN_OID(idxoid);
 	else
 		PG_RETURN_NULL();
 }
+
+Datum
+any_value_trans(PG_FUNCTION_ARGS)
+{
+	/* Return the first non-null argument */
+	if (!PG_ARGISNULL(0))
+		PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+	if (!PG_ARGISNULL(1))
+		PG_RETURN_DATUM(PG_GETARG_DATUM(1));
+	PG_RETURN_NULL();
+}
+
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index b9110a5298..37626d6f0c 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -620,9 +620,13 @@
   aggtransfn => 'ordered_set_transition_multi', aggfinalfn => 'cume_dist_final',
   aggfinalextra => 't', aggfinalmodify => 'w', aggmfinalmodify => 'w',
   aggtranstype => 'internal' },
 { aggfnoid => 'dense_rank(any)', aggkind => 'h', aggnumdirectargs => '1',
   aggtransfn => 'ordered_set_transition_multi',
   aggfinalfn => 'dense_rank_final', aggfinalextra => 't', aggfinalmodify => 'w',
   aggmfinalmodify => 'w', aggtranstype => 'internal' },
 
+# any_value
+{ aggfnoid => 'any_value(anyelement)', aggtransfn => 'any_value_trans',
+  aggcombinefn => 'any_value_trans', aggtranstype => 'anyelement' },
+
 ]
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f9301b2627..2ee4797559 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11849,9 +11849,17 @@
   proname => 'brin_minmax_multi_summary_recv', provolatile => 's',
   prorettype => 'pg_brin_minmax_multi_summary', proargtypes => 'internal',
   prosrc => 'brin_minmax_multi_summary_recv' },
 { oid => '4641', descr => 'I/O',
   proname => 'brin_minmax_multi_summary_send', provolatile => 's',
   prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
   prosrc => 'brin_minmax_multi_summary_send' },
 
+{ oid => '8981', descr => 'arbitrary value from among input values',
+  proname => 'any_value', prokind => 'a', proisstrict => 'f',
+  prorettype => 'anyelement', proargtypes => 'anyelement',
+  prosrc => 'aggregate_dummy' },
+{ oid => '8982', descr => 'any_value transition function',
+  proname => 'any_value_trans', prorettype => 'anyelement', proargtypes => 'anyelement anyelement',
+  prosrc => 'any_value_trans' },
+
 ]
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index fc2bd40be2..fb87b9abf1 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -20,16 +20,28 @@ SELECT avg(four) AS avg_1 FROM onek;
 (1 row)
 
 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
        avg_32        
 ---------------------
  32.6666666666666667
 (1 row)
 
+SELECT any_value(v) FROM (VALUES (1)) AS v (v);
+ any_value 
+-----------
+         1
+(1 row)
+
+SELECT any_value(v) FROM (VALUES (NULL)) AS v (v);
+ any_value 
+-----------
+ 
+(1 row)
+
 -- In 7.1, avg(float4) is computed using float8 arithmetic.
 -- Round the result to 3 digits to avoid platform-specific results.
 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
  avg_107_943 
 -------------
      107.943
 (1 row)
 
@@ -1875,16 +1887,22 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four);
 
 select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
 from (values ('a', 'b')) AS v(foo,bar);
  max 
 -----
  a
 (1 row)
 
+SELECT any_value(v) FILTER (WHERE v > 2) FROM (VALUES (1), (2), (3)) AS v (v);
+ any_value 
+-----------
+         3
+(1 row)
+
 -- outer reference in FILTER (PostgreSQL extension)
 select (select count(*)
         from (values (1)) t0(inner_c))
 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
  count 
 -------
      1
      1
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index a4c00ff7a9..7206e475a1 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -19,16 +19,19 @@ COPY aggtest FROM :'filename';
 
 ANALYZE aggtest;

 SELECT avg(four) AS avg_1 FROM onek;
 
 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
 
+SELECT any_value(v) FROM (VALUES (1)) AS v (v);
+SELECT any_value(v) FROM (VALUES (NULL)) AS v (v);
+
 -- In 7.1, avg(float4) is computed using float8 arithmetic.
 -- Round the result to 3 digits to avoid platform-specific results.
 
 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
 
 SELECT avg(gpa) AS avg_3_4 FROM ONLY student;

@@ -711,16 +714,18 @@ group by ten;
 
 select ten, sum(distinct four) filter (where four > 10) from onek a
 group by ten
 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
 
 select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
 from (values ('a', 'b')) AS v(foo,bar);
 
+SELECT any_value(v) FILTER (WHERE v > 2) FROM (VALUES (1), (2), (3)) AS v (v);
+
 -- outer reference in FILTER (PostgreSQL extension)
 select (select count(*)
         from (values (1)) t0(inner_c))
 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
 select (select count(*) filter (where outer_c <> 0)
         from (values (1)) t0(inner_c))
 from (values (2),(3)) t1(outer_c); -- outer query is aggregation query
 select (select count(inner_c) filter (where outer_c <> 0)
-- 
2.34.1

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 5 Dec 2022 10:56:44 -0700

> PFA an implementation of this aggregate.
Can we please add "first_value" and "last_value" if we are going to add
"some_random_value" to our library of aggregates?

Also, maybe we should have any_value do something like compute a 50/50
chance that any new value seen replaces the existing chosen value, instead
of simply returning the first value all the time.  Maybe even prohibit the
first value from being chosen so long as a second value appears.

David J.

---

From: Tom Lane <[email-masked]>
Date: Mon, 05 Dec 2022 13:04:39 -0500

> "some_random_value" to our library of aggregates?

First and last according to what ordering?  We have those in the
window-aggregate case, and I don't think we want to encourage people
to believe that "first" and "last" are meaningful otherwise.

ANY_VALUE at least makes it clear that you're getting an unspecified
one of the inputs.

			regards, tom lane

---

From: Robert Haas <[email-masked]>
Date: Mon, 5 Dec 2022 13:06:53 -0500

> ANY_VALUE at least makes it clear that you're getting an unspecified

I have personally implemented first_value() and last_value() in the
past in cases where I had guaranteed the ordering myself, or didn't
care what ordering was used. I think they're perfectly sensible. But
if we don't add them to core, at least they're easy to add in
user-space.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Corey Huinker <[email-masked]>
Date: Mon, 5 Dec 2022 14:31:24 -0500

> first value from being chosen so long as a second value appears.

Adding to the pile of wanted aggregates: in the past I've lobbied for
only_value() which is like first_value() but it raises an error on
encountering a second value.

---

From: Robert Haas <[email-masked]>
Date: Mon, 5 Dec 2022 14:41:47 -0500

On Mon, Dec 5, 2022 at 2:31 PM Corey Huinker <[email-masked]> wrote:
> Adding to the pile of wanted aggregates: in the past I've lobbied for only_value() which is like first_value() but it raises an error on encountering a second value.

Yeah, that's another that I have hand-rolled in the past.

---

From: Vik Fearing <[email-masked]>
Date: Mon, 5 Dec 2022 21:18:57 +0100

> PFA an implementation of this aggregate.

Here is v2 of this patch.  I had forgotten to update sql_features.txt.
-- 
Vik Fearing
From a9bb61aab9788ae25fdcd28f7dcfb54a263665cc Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Sat, 9 Apr 2022 00:07:38 +0200
Subject: [PATCH] Implement ANY_VALUE aggregate

 src/backend/catalog/sql_features.txt     |  1 +
 7 files changed, 62 insertions(+)

      <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>any_value</primary>
+        </indexterm>
+        <function>any_value</function> ( <type>"any"</type> )
+        <returnvalue><replaceable>same as input type</replaceable></returnvalue>
+       </para>
+       <para>
+        Chooses a non-deterministic value from the non-null input values.
+       </para></entry>
+       <entry>Yes</entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
          <primary>array_agg</primary>
         </indexterm>
         <function>array_agg</function> ( <type>anynonarray</type> )
         <returnvalue>anyarray</returnvalue>
        </para>
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 8704a42b60..b7b6ad6334 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -515,16 +515,17 @@ T617	FIRST_VALUE and LAST_VALUE functions			YES
 T618	NTH_VALUE function			NO	function exists, but some options missing
 T619	Nested window functions			NO	
 T620	WINDOW clause: GROUPS option			YES	
 T621	Enhanced numeric functions			YES	
 T622	Trigonometric functions			YES	
 T623	General logarithm functions			YES	
 T624	Common logarithm functions			YES	
 T625	LISTAGG			NO	
+T626	ANY_VALUE			YES	
 T631	IN predicate with one list element			YES	
 T641	Multiple column assignment			NO	only some syntax variants supported
 T651	SQL-schema statements in SQL routines			YES	
 T652	SQL-dynamic statements in SQL routines			NO	
 T653	SQL-schema statements in external routines			YES	
 T654	SQL-dynamic statements in external routines			NO	
 T655	Cyclically dependent routines			YES	
 T811	Basic SQL/JSON constructor functions			NO	
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 9c13251231..94c92de06d 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -928,8 +928,20 @@ pg_get_replica_identity_index(PG_FUNCTION_ARGS)
 	idxoid = RelationGetReplicaIndex(rel);
 	table_close(rel, AccessShareLock);
 
 	if (OidIsValid(idxoid))
 		PG_RETURN_OID(idxoid);
 	else
 		PG_RETURN_NULL();
 }
+
+Datum
 
+# any_value

 

 
 
 ANALYZE aggtest;

---

From: Vik Fearing <[email-masked]>
Date: Tue, 6 Dec 2022 04:46:37 +0100

> first value from being chosen so long as a second value appears.

The spec says the result is implementation-dependent meaning we don't 
even need to document how it is obtained, but surely behavior like this 
would preclude future optimizations like the ones I mentioned?

I once wrote a random_agg() for a training course that used reservoir 
sampling to get an evenly distributed value from the inputs.  Something 
like that seems to be what you are looking for here.  I don't see the 
use case for adding it to core, though.

The use case for ANY_VALUE is compliance with the standard.
-- 
Vik Fearing

---

From: Vik Fearing <[email-masked]>
Date: Tue, 6 Dec 2022 04:52:21 +0100

> encountering a second value.

I have had use for this in the past, but I can't remember why.  What is 
your use case for it?  I will happily write a patch for it, and also 
submit it to the SQL Committee for inclusion in the standard.  I need to 
justify why it's a good idea, though, and we would need to consider what 
to do with nulls now that there is <unique null treatment>.
-- 
Vik Fearing

---

From: Isaac Morland <[email-masked]>
Date: Mon, 5 Dec 2022 23:06:46 -0500

> to do with nulls now that there is <unique null treatment>.

I have this in my local library of "stuff that I really wish came with
Postgres", although I call it same_agg and it just goes to NULL if there
are more than one distinct value.

I sometimes use it when normalizing non-normalized data, but more commonly
I use it when the query planner isn't capable of figuring out that a column
I want to use in the output depends only on the grouping columns. For
example, something like:

SELECT group_id, group_name, count(*) from group_group as gg natural join
group_member as gm group by group_id

I think that exact example actually does or is supposed to work now, since
it realizes that I'm grouping on the primary key of group_group so the
group_name field in the same table can't differ between rows of a group,
but most of the time when I expect that feature to allow me to use a field
it actually doesn't.

I have a vague notion that part of the issue may be the distinction between
gg.group_id, gm.group_id, and group_id; maybe the above doesn't work but it
does work if I group by gg.group_id instead of by group_id. But obviously
there should be no difference because in this query those 3 values cannot
differ (outer joins are another story).

For reference, here is my definition:

CREATE OR REPLACE FUNCTION same_sfunc (
    a anyelement,
    b anyelement
) RETURNS anyelement
    LANGUAGE SQL IMMUTABLE STRICT
    SET search_path FROM CURRENT
AS $$
    SELECT CASE WHEN $1 = $2 THEN $1 ELSE NULL END
$$;
COMMENT ON FUNCTION same_sfunc (anyelement, anyelement) IS 'SFUNC for
same_agg aggregate; returns common value of parameters, or NULL if they
differ';

DROP AGGREGATE IF EXISTS same_agg (anyelement);
CREATE AGGREGATE same_agg (anyelement) (
    SFUNC = same_sfunc,
    STYPE = anyelement
);
COMMENT ON AGGREGATE same_agg (anyelement) IS 'Return the common non-NULL
value of all non-NULL aggregated values, or NULL if some values differ';

You can tell I've had this for a while - there are several newer Postgres
features that could be used to clean this up noticeably.

I also have a repeat_agg which returns the last value (not so interesting)
but which is sometimes useful as a window function (more interesting:
replace NULLs with the previous non-NULL value in the column).

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 5 Dec 2022 21:22:25 -0700

> would preclude future optimizations like the ones I mentioned?

So, given the fact that we don't actually want to name a function
first_value (because some users are readily confused as to when the concept
of first is actually valid or not) but some users do actually wish for this
functionality - and you are proposing to implement it here anyway - how
about we actually do document that we promise to return the first non-null
value encountered by the aggregate.  We can then direct people to this
function and just let them know to pretend the function is really named
first_value in the case where they specify an order by. (last_value comes
for basically free with descending sorting).

> use case for adding it to core, though.
The use case was basically what Tom was saying - I don't want our users
that don't understand the necessity of order by, and don't read the
documentation, to observe that we consistently return the first non-null
value and assume that this is what the function promises when we are not
making any such promise to them.  As noted above, my preference at this
point would be to just make that promise.

David J.

---

From: Vik Fearing <[email-masked]>
Date: Tue, 6 Dec 2022 05:48:15 +0100

> for basically free with descending sorting).

I can imagine an optimization that would remove an ORDER BY clause 
because it isn't needed for any other aggregate.  There is no reason to 
cause an extra sort when the user has requested *any value*.

> making any such promise to them.  

Documenting something for the benefit of those who do not read the 
documentation is a ridiculous proposal.

> As noted above, my preference at this point would be to just make that promise.

I see no reason to paint ourselves into a corner here.
-- 
Vik Fearing

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 5 Dec 2022 21:57:05 -0700

On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing <[email-masked]> wrote:

> because it isn't needed for any other aggregate.

I'm referring to the query:

select any_value(v order by v) from (values (2),(1),(3)) as vals (v);
// produces 1, per the documented implementation-defined behavior.

Someone writing:

select any_value(v) from (values (2),(1),(3)) as vals (v) order by v;

Is not presently, nor am I saying, promised the value 1.

I'm assuming you are thinking of the second query form, while the guarantee
only needs to apply to the first.

David J.

---

From: Vik Fearing <[email-masked]>
Date: Tue, 6 Dec 2022 06:40:30 +0100

> // produces 1, per the documented implementation-defined behavior.

Implementation-dependent.  It is NOT implementation-defined, per spec.

We often loosen the spec rules when they don't make technical sense to 
us, but I don't know of any example of when we have tightened them.

> only needs to apply to the first.

I am saying that a theoretical pg_aggregate.aggorderdoesnotmatter could 
bestow upon ANY_VALUE the ability to make those two queries equivalent.

If you care about which value you get back, use something else.
-- 
Vik Fearing

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 6 Dec 2022 20:22:36 -0700

On Mon, Dec 5, 2022 at 10:40 PM Vik Fearing <[email-masked]> wrote:

> I really don't care all that much about the spec here given that ORDER BY
in an aggregate call is non-spec.

> us, but I don't know of any example of when we have tightened them.

The function has to choose some row from among its inputs, and the system
has to obey an order by specification added to the function call.  You are
de-facto creating a first_value aggregate (which is by definition
non-standard) whether you like it or not.  I'm just saying to be upfront
and honest about it - our users do want such a capability so maybe accept
that there is a first time for everything.  Not that picking an
advantageous "implementation-dependent" implementation should be considered
deviating from the spec.

> bestow upon ANY_VALUE the ability to make those two queries equivalent.

That theoretical idea should not be entertained.  Removing a user's
explicitly added ORDER BY should be off-limits.  Any approach at
optimization here should simply look at whether an ORDER BY is specified
and pass that information to the function.  If the function itself really
believes that ordering matters it can emit its own runtime exception
stating that fact and the user can fix their query.

> If you care about which value you get back, use something else.
There isn't a "something else" to use so that isn't presently an option.

I suppose it comes down to what level of belief and care you have that
people will simply mis-use this function if it is added in its current form
to get the desired first_value effect that it produces.

David J.

---

From: Pantelis Theodosiou <[email-masked]>
Date: Wed, 7 Dec 2022 08:58:44 +0000

On Tue, Dec 6, 2022 at 4:57 AM David G. Johnston
<[email-masked]> wrote:
...
> Is not presently, nor am I saying, promised the value 1.

Shouldn't the 2nd query be producing an error, as it has an implied
GROUP BY () - so column v cannot appear (unless aggregated) in SELECT
and ORDER BY?

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 7 Dec 2022 06:36:04 -0700

> GROUP BY () - so column v cannot appear (unless aggregated) in SELECT

Right, that should be written as:

select any_value(v) from (values (2),(1),(3) order by 1) as vals (v);

(you said SELECT; the discussion here is that any_value is going to be
added as a new aggregate function)

David J.

---

From: Vik Fearing <[email-masked]>
Date: Thu, 8 Dec 2022 06:00:54 +0100

> in an aggregate call is non-spec.

Well, this is demonstrably wrong.

<array aggregate function> ::=
   ARRAY_AGG <left paren>
     <value expression>
     [ ORDER BY <sort specification list> ]
     <right paren>

> The function has to choose some row from among its inputs,

True.

> and the system has to obey an order by specification added to the function call.

False.

> You are de-facto creating a first_value aggregate (which is by definition
> non-standard) whether you like it or not.

I am de jure creating an any_value aggregate (which is by definition 
standard) whether you like it or not.

> I'm just saying to be upfront

It absolutely should be entertained, and I plan on doing so in an 
upcoming thread.  Whether it errors or ignores is something that should 
be discussed on that thread.

> There isn't a "something else" to use so that isn't presently an option.

The query

     SELECT proposed_first_value(x ORDER BY y) FROM ...

is equivalent to

     SELECT (ARRAY_AGG(x ORDER BY y))[1] FROM ...

so I am not very sympathetic to your claim of "no other option".

> to get the desired first_value effect that it produces.

People who rely on explicitly undefined behavior get what they deserve 
when the implementation changes.
-- 
Vik Fearing

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 7 Dec 2022 22:48:16 -0700

>      [ ORDER BY <sort specification list> ]

Demoable only by you and a few others...

We should update our documentation - the source of SQL Standard knowledge
for mere mortals.

https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES

"Note: The ability to specify both DISTINCT and ORDER BY in an aggregate
function is a PostgreSQL extension."

Apparently only DISTINCT remains as our extension.

> standard) whether you like it or not.

Yes, both statements seem true.  At least until we decide to start ignoring
a user's explicit order by clause.

> so I am not very sympathetic to your claim of "no other option".

Semantically, yes, in terms of performance, not so much, for any
non-trivial sized group.

I'm done, and apologize for getting too emotionally invested in this.  I
hope to get others to voice enough +1s to get a first_value function into
core along-side this one (which makes the above discussion either moot or
deferred until there is a concrete use case for ignoring an explicit ORDER
BY).  If that doesn't happen, well, it isn't going to make or break us
either way.

David J.

---

From: Vik Fearing <[email-masked]>
Date: Thu, 8 Dec 2022 13:32:30 +0100

> Demoable only by you and a few others...

The standard is publicly available.  It is strange that we, being so 
open, hold ourselves to such a closed standard; but that is what we do.

> Apparently only DISTINCT remains as our extension.

Using DISTINCT in an aggregate is also standard.  What that note is 
saying is that the standard does not allow *both* to be used at the same 
time.

The standard defines these things for specific aggregates whereas we are 
much more generic about it and therefore have to deal with the combinations.

I have submitted a doc patch to clarify that.

> a user's explicit order by clause.

I ran some tests and including an ORDER BY in an aggregate that doesn't 
care (like COUNT) is devastating for performance.  I will be proposing a 
solution to that soon and I invite you to participate in that 
conversation when I do.
-- 
Vik Fearing