On 09.02.23 10:42, Vik Fearing wrote:
v4 attached. I am putting this back to Needs Review in the commitfest
app, but these changes were editorial so it is probably RfC like Peter
had set it. I will let you be the judge of that.
I have committed this.
I made a few small last-minute tweaks:
I could have used such an aggregate in the past, so +1.
This is maybe getting into nit-picking, but perhaps it should be
documented as returning an "arbitrary" value instead of a
"non-deterministic" one? Technically the value is deterministic:
there's a concrete algorithm specifying how it's
ming it would always be a MONOTONICFUNC_BOTH function. It seems
unlikely that someone would have a subquery with a WHERE clause in the
upper-level query referencing the any_value() aggregate. Thought I'd
mention both of these things anyway as someone else might think of
some good reason we shou
es of code it would take to write it. I'm
assuming it would always be a MONOTONICFUNC_BOTH function. It seems
unlikely that someone would have a subquery with a WHERE clause in the
upper-level query referencing the any_value() aggregate. Thought I'd
mention both of these things anyway as someone else migh
On 18.01.23 18:01, Vik Fearing wrote:
On 1/18/23 16:06, Peter Eisentraut wrote:
On 05.12.22 21:18, Vik Fearing wrote:
On 12/5/22 15:57, Vik Fearing wrote:
The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
returns an implementation-dependent (i.e. non-deterministic) value
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/catalog/sql_features.txt | 1 +
s
On 1/18/23 16:55, Tom Lane wrote:
Peter Eisentraut writes:
On 05.12.22 21:18, Vik Fearing wrote:
On 12/5/22 15:57, Vik Fearing wrote:
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
Peter Eisentraut writes:
> On 05.12.22 21:18, Vik Fearing wrote:
>> On 12/5/22 15:57, Vik Fearing wrote:
>>> 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.
> Since the
On 05.12.22 21:18, Vik Fearing wrote:
On 12/5/22 15:57, Vik Fearing wrote:
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.
Here is v2
ate (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.
Yes, both statements seem true. At least until we decide to start ignoring
a user's explicit order by clause.
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
creating an any_value aggregate (which is by definition
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-depe
On Wed, Dec 7, 2022 at 1:58 AM Pantelis Theodosiou
wrote:
> On Tue, Dec 6, 2022 at 4:57 AM David G. Johnston
> wrote:
> ...
> >
> >
> > 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
On Tue, Dec 6, 2022 at 4:57 AM David G. Johnston
wrote:
...
>
>
> 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
On Mon, Dec 5, 2022 at 10:40 PM Vik Fearing wrote:
> On 12/6/22 05:57, David G. Johnston wrote:
> > On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing
> wrote:
> >
> >> I can imagine an optimization that would remove an ORDER BY clause
> >> because it isn't needed for any other aggregate.
> >
> >
> >
On 12/6/22 05:57, David G. Johnston wrote:
On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing wrote:
I can imagine an optimization that would remove an ORDER BY clause
because it isn't needed for any other aggregate.
I'm referring to the query:
select any_value(v order by v) from (values
On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing wrote:
> On 12/6/22 05:22, David G. Johnston wrote:
> > On Mon, Dec 5, 2022 at 8:46 PM Vik Fearing
> wrote:
> >
> >> On 12/5/22 18:56, David G. Johnston wrote:
> >>> Also, maybe we should have any_value do something like compute a 50/50
> >>> chance
On 12/6/22 05:22, David G. Johnston wrote:
On Mon, Dec 5, 2022 at 8:46 PM Vik Fearing wrote:
On 12/5/22 18:56, David G. Johnston wrote:
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
On Mon, Dec 5, 2022 at 8:46 PM Vik Fearing wrote:
> On 12/5/22 18:56, David G. Johnston wrote:
> > 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
On Mon, 5 Dec 2022 at 22:52, Vik Fearing wrote:
> On 12/5/22 20:31, Corey Huinker 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.
>
> I have had use for
On 12/5/22 20:31, Corey Huinker 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.
I have had use for this in the past, but I can't remember why. What is
your use case
On 12/5/22 18:56, David G. Johnston wrote:
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
to update sql_features.txt.
--
Vik Fearing
From a9bb61aab9788ae25fdcd28f7dcfb54a263665cc Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
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
On Mon, Dec 5, 2022 at 2:31 PM Corey Huinker 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.
--
Robert
On Mon, Dec 5, 2022 at 12:57 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Dec 5, 2022 at 7:57 AM Vik Fearing
> wrote:
>
>> The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
>> returns an implementation-dependent (i.e. non-deterministic) value from
>> the
On Mon, Dec 5, 2022 at 1:04 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > Can we please add "first_value" and "last_value" if we are going to add
> > "some_random_value" to our library of aggregates?
>
> First and last according to what ordering? We have those in the
> window-aggregate
"David G. Johnston" writes:
> Can we please add "first_value" and "last_value" if we are going to add
> "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
On Mon, Dec 5, 2022 at 7:57 AM Vik Fearing wrote:
> 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.
>
>
Can we please add
going through a LATERAL subquery.
--
Vik FearingFrom 7465fac12fc636ff26088ae31de2937f7c3a459f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
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
29 matches
Mail list logo
