pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddescription of Aggregate Expressions
7+ messages / 3 participants
[nested] [flat]
* description of Aggregate Expressions
@ 2019-12-05 22:18 John Lumby <johnlumby@hotmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: John Lumby @ 2019-12-05 22:18 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
In PostgreSQL 12.1 Documentation chapter 4.2.7. Aggregate Expressions it says
The syntax of an aggregate expression is one of the following:
...
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
...
I believe this is incorrect in the case where the DISTINCT is on a comma-separated list of expressions.
It would imply that this is legal
select count(DISTINCT parent_id , name) from mytable
but that is rejected with
ERROR: function count(bigint, text) does not exist
whereas
select count(DISTINCT ( parent_id , name) ) from mytable
is accepted.
So I think to handle all cases the line in the doc should read
aggregate_name (DISTINCT ( expression [ , ... ] ) [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
I don't know how to indicate that those extra parentheses can be omitted if the list has only one expression.
Cheers, John Lumby
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: description of Aggregate Expressions
@ 2019-12-05 23:06 David G. Johnston <david.g.johnston@gmail.com>
parent: John Lumby <johnlumby@hotmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: David G. Johnston @ 2019-12-05 23:06 UTC (permalink / raw)
To: John Lumby <johnlumby@hotmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Thu, Dec 5, 2019 at 3:18 PM John Lumby <johnlumby@hotmail.com> wrote:
> In PostgreSQL 12.1 Documentation chapter 4.2.7. Aggregate Expressions it
> says
>
>
> The syntax of an aggregate expression is one of the following:
> ...
> aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [
> FILTER ( WHERE filter_clause ) ]
> ...
>
> I believe this is incorrect in the case where the DISTINCT is on a
> comma-separated list of expressions.
> It would imply that this is legal
>
It is...you didn't get a syntax error.
>
> select count(DISTINCT parent_id , name) from mytable
>
> but that is rejected with
> ERROR: function count(bigint, text) does not exist
>
The error is that while the query is syntactically correct in order to
execute it as written a function would need to exist that does not. As far
as a general syntax diagram goes it has correctly communicated what is
legal.
> whereas
>
> select count(DISTINCT ( parent_id , name) ) from mytable
>
> is accepted.
>
Correct, converting the two individual columns into a "tuple" allows the
default tuple distinct-making infrastructure to be used to execute the
query.
> So I think to handle all cases the line in the doc should read
>
> aggregate_name (DISTINCT ( expression [ , ... ] ) [ order_by_clause ] ) [
> FILTER ( WHERE filter_clause ) ]
>
> I don't know how to indicate that those extra parentheses can be omitted
> if the list has only one expression.
>
Then I would have to say the proposed solution to this edge case is worse
than the problem. I also don't expect there to be a clean solution to
dealing with the complexities of expressions at the syntax diagram level.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: description of Aggregate Expressions
@ 2019-12-05 23:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2019-12-05 23:29 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: John Lumby <johnlumby@hotmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Dec 5, 2019 at 3:18 PM John Lumby <johnlumby@hotmail.com> wrote:
>> whereas
>> select count(DISTINCT ( parent_id , name) ) from mytable
>> is accepted.
> Correct, converting the two individual columns into a "tuple" allows the
> default tuple distinct-making infrastructure to be used to execute the
> query.
Yeah. This might be more intelligible if it were written
select count(DISTINCT ROW(parent_id, name) ) from mytable
However, the SQL committee in their finite wisdom have decreed that
the ROW keyword is optional. (As long as there's more than one
column expression; the need for that special case is another reason
why omitting ROW isn't really a nice thing to do.)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: description of Aggregate Expressions
@ 2019-12-06 18:43 John Lumby <johnlumby@hotmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: John Lumby @ 2019-12-06 18:43 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>
On 12/05/19 18:06, David G. Johnston wrote:
On Thu, Dec 5, 2019 at 3:18 PM John Lumby <<mailto:johnlumby@hotmail.com>johnlumby@hotmail.com<mailto:johnlumby@hotmail.com>> wrote:
In PostgreSQL 12.1 Documentation chapter 4.2.7. Aggregate Expressions it says
The syntax of an aggregate expression is one of the following:
...
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
...
I believe this is incorrect in the case where the DISTINCT is on a comma-separated list of expressions.
It would imply that this is legal
It is...you didn't get a syntax error.
Hmm, even though true, I think this is unhelpful.
If a reference document states that the syntax for a something-or-other construct is one of the following diagrams,
then I expect that the diagrams are valid for *every* kind of something-or-other, not just some.
Yet the diagram I quote always results in being rejected in the case of COUNT -
which I consider to be as good as saying it is invalid syntax.
select count(DISTINCT parent_id , name) from mytable
but that is rejected with
ERROR: function count(bigint, text) does not exist
The error is that while the query is syntactically correct in order to execute it as written a function would need to exist that does not. As far as a general syntax diagram goes it has correctly communicated what is legal.
whereas
select count(DISTINCT ( parent_id , name) ) from mytable
is accepted.
Correct, converting the two individual columns into a "tuple" allows the default tuple distinct-making infrastructure to be used to execute the query.
So I think to handle all cases the line in the doc should read
aggregate_name (DISTINCT ( expression [ , ... ] ) [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
I don't know how to indicate that those extra parentheses can be omitted if the list has only one expression.
Then I would have to say the proposed solution to this edge case is worse than the problem. I also don't expect there to be a clean solution to dealing with the complexities of expressions at the syntax diagram level.
Yes, I see what I suggested is not ideal either. But I think something needs to be changed.
How about replacing "expression [ , ... ]" by "parameter_list" in the description, and then stating that parameter_list can be either a single expression or , if the particular aggregate function accepts it (for which, consult that function's reference), a comma-separated list of expressions.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: description of Aggregate Expressions
@ 2019-12-06 19:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Lumby <johnlumby@hotmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2019-12-06 19:17 UTC (permalink / raw)
To: John Lumby <johnlumby@hotmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>; David G. Johnston <david.g.johnston@gmail.com>
John Lumby <johnlumby@hotmail.com> writes:
> On 12/05/19 18:06, David G. Johnston wrote:
>> Then I would have to say the proposed solution to this edge case is worse than the problem. I also don't expect there to be a clean solution to dealing with the complexities of expressions at the syntax diagram level.
> Yes, I see what I suggested is not ideal either. But I think something needs to be changed.
> How about replacing "expression [ , ... ]" by "parameter_list" in the description, and then stating that parameter_list can be either a single expression or , if the particular aggregate function accepts it (for which, consult that function's reference), a comma-separated list of expressions.
That's just as wrong. As we tried to explain before, the
parenthesized-list syntax is a row constructor, so it only works
in cases where the aggregate function can accept a composite
argument type. Most don't.
Moreover, the very same thing holds in *any* expression context,
not only aggregate arguments. So if we took this seriously there
would have to be a lot of other places plastered with equally
confusing/unhelpful addenda.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: description of Aggregate Expressions
@ 2019-12-06 20:01 John Lumby <johnlumby@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: John Lumby @ 2019-12-06 20:01 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote
> > How about replacing "expression [ , ... ]" by "parameter_list" in the description,
> > and then stating that parameter_list can be either a single expression or ,
> > if the particular aggregate function accepts it (for which, consult that function's reference),
> > a comma-separated list of expressions.
>
> That's just as wrong. As we tried to explain before, the
> parenthesized-list syntax is a row constructor, so it only works
> in cases where the aggregate function can accept a composite
> argument type. Most don't.
>
But surely not *all* cases of a multi-expression parameter list of an aggregate function are row constructors are they? What about
select parent_id, name, GROUPING(parent_id , name), count(*) FROM mytable GROUP BY ROLLUP(parent_id , name);
In the piece "GROUPING(parent_id , name)" ,
is "(parent_id , name)" a row constructor or a list of two expressions?
Or are you saying those are one and the same thing?
Cheers, John
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: description of Aggregate Expressions
@ 2019-12-06 21:43 John Lumby <johnlumby@hotmail.com>
parent: John Lumby <johnlumby@hotmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: John Lumby @ 2019-12-06 21:43 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>
John Lumby wrote:
<
< Tom Lane wrote
<
< > > How about replacing "expression [ , ... ]" by "parameter_list" in the description,
< > > and then stating that parameter_list can be either a single expression or ,
< > > if the particular aggregate function accepts it (for which, consult that function's reference),
< > > a comma-separated list of expressions.
< >
< > That's just as wrong. As we tried to explain before, the
< > parenthesized-list syntax is a row constructor, so it only works
< > in cases where the aggregate function can accept a composite
< > argument type. Most don't.
< >
<
< But surely not *all* cases of a multi-expression parameter list of an aggregate function are row constructors are they? What about
<
< select parent_id, name, GROUPING(parent_id , name), count(*) FROM mytable GROUP BY ROLLUP(parent_id , name);
<
< In the piece "GROUPING(parent_id , name)" ,
< is "(parent_id , name)" a row constructor or a list of two expressions?
<
< Or are you saying those are one and the same thing?
<
I think I can answer my own question - No they are not the same - because :
select parent_id, name, GROUPING(ROW(parent_id , name)), count(*) FROM mytable GROUP BY ROLLUP(parent_id , name);
ERROR: arguments to GROUPING must be grouping expressions of the associated query level
LINE 1: select parent_id, name, GROUPING(ROW(parent_id , name)), cou...
^
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-12-06 21:43 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-05 22:18 description of Aggregate Expressions John Lumby <johnlumby@hotmail.com>
2019-12-05 23:06 ` Re: description of Aggregate Expressions David G. Johnston <david.g.johnston@gmail.com>
2019-12-05 23:29 ` Re: description of Aggregate Expressions Tom Lane <tgl@sss.pgh.pa.us>
2019-12-06 18:43 ` Re: description of Aggregate Expressions John Lumby <johnlumby@hotmail.com>
2019-12-06 19:17 ` Re: description of Aggregate Expressions Tom Lane <tgl@sss.pgh.pa.us>
2019-12-06 20:01 ` Re: description of Aggregate Expressions John Lumby <johnlumby@hotmail.com>
2019-12-06 21:43 ` Re: description of Aggregate Expressions John Lumby <johnlumby@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox