Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1id0Ce-0003KM-7x for pgsql-docs@arkaria.postgresql.org; Thu, 05 Dec 2019 23:06:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1id0Cb-0006mM-RG for pgsql-docs@arkaria.postgresql.org; Thu, 05 Dec 2019 23:06:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1id0Cb-0006mF-DR for pgsql-docs@lists.postgresql.org; Thu, 05 Dec 2019 23:06:29 +0000 Received: from mail-qt1-x842.google.com ([2607:f8b0:4864:20::842]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1id0CZ-0008Bi-2S for pgsql-docs@lists.postgresql.org; Thu, 05 Dec 2019 23:06:28 +0000 Received: by mail-qt1-x842.google.com with SMTP id p5so5196201qtq.12 for ; Thu, 05 Dec 2019 15:06:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=K8WiwTD6RdLKUGBzglL55WHEacv/VV2PU3VCqfsdKfM=; b=JO2VSCZx9dF7J1Iv6+Kc1Zj/LB/xKN7i9iovbt8onsYw+UJQBKWV7g9oWdxg9JRIgK fDb0130KH0VZxIf6H2zG4HsMM1rnAu8nRwDjfOfjnv6VLQw37OlYH+kJNODIjmNEjMZK W71yqhgOllagRuK5PjBJCs9CUjmNEbaeY5n7UaRYvfKD5JjIzAqN5GIOYHlEp+nqfv9T JEMq78+ssuYQgrPu72Qkv3HH3E9PS4RUT4sWnpyluhEVWYQ6kPJM9Ffd1KRnIpAoUEr3 nbP4A4JTN0jMdpIw/fVIVwMv5CwyFvjAyB8/HubSLlHjXA+a06wkuoBk6IKKQ/oDG/tA UhLw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=K8WiwTD6RdLKUGBzglL55WHEacv/VV2PU3VCqfsdKfM=; b=NkbafoAv8/douZw375WQDpRmwtLTtCOF/QG/3c45H7zS6rYEh7fi/A366/BijWL7JV C3ldldDEJw+WY55XkYfEJ+mYhiJA755lLT+qkCgFtHHWghJK2JCZnI4atkGYsampcT5F uj5HdRvbb5gEkKzs9nGqlB6vDdhC/tytNP/9RaxKaLsbCq10Oe212IFQN8KmZLD31MRf KXL+226l6+BjVpgIECqt/TOrh5kwvgis69a2KHwiPnvTXUhBctjTvybTdVXOW43kb1fn wsocfICTswEx8abG08Y+kLJSmbrEhe6xlw1fJzGzbHGpB7Nh6igR/BtW41pIWR/4nqz8 YBAQ== X-Gm-Message-State: APjAAAXNlDHzyKxDMLeB/9arJER64/gVFo0mwJ3fQsv4ceXJRV2xODft jM39A7Li4VCbkB/inFq0CAoDRg6zFrg/Rtb2qPc= X-Google-Smtp-Source: APXvYqzIEcsFC9GNGR8RL/BLUA01ZfVabfiRhun40PhmUsxxnnS9ru6v4ueFZdBsuzXkCNl/tfwnVkDEHHA7xO8frJc= X-Received: by 2002:ac8:60da:: with SMTP id i26mr10273262qtm.225.1575587185830; Thu, 05 Dec 2019 15:06:25 -0800 (PST) MIME-Version: 1.0 References: <2A91BEF8171A5349931391E0C721CC5375705457@CPEMS-KPN501.KPNCNL.LOCAL> <251962385fdc49da0da54e22588aabd6fbc2e0fa.camel@osdev.nl> <2A91BEF8171A5349931391E0C721CC53757054D3@CPEMS-KPN501.KPNCNL.LOCAL> <2CB4B3C9-F43F-49D8-A594-93DBE99EF514@elevated-dev.com> <7308f0f3a1674a39bada8571189a3735@CPEMS-KPN509.KPNCNL.LOCAL> <91CEBEAA-7260-4503-832F-E5B27B91BE1F@elevated-dev.com> <2A91BEF8171A5349931391E0C721CC5375705528@CPEMS-KPN501.KPNCNL.LOCAL> <2A91BEF8171A5349931391E0C721CC53757055C3@CPEMS-KPN501.KPNCNL.LOCAL> In-Reply-To: From: "David G. Johnston" Date: Thu, 5 Dec 2019 16:06:12 -0700 Message-ID: Subject: Re: description of Aggregate Expressions To: John Lumby Cc: "pgsql-docs@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000862fa20598fcfaa5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000862fa20598fcfaa5 Content-Type: text/plain; charset="UTF-8" On Thu, Dec 5, 2019 at 3:18 PM John Lumby 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. --000000000000862fa20598fcfaa5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 Ex= pressions=C2=A0 it says


The syntax of an aggregate expression is one of the following:
=C2=A0 ...
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTE= R ( WHERE filter_clause ) ]
...

I believe this is incorrect in the case where the DISTINCT is on a comma-se= parated 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=C2=A0 mytable

but that is rejected with
ERROR:=C2=A0 function count(bigint, text) does not exist

The error is that while the query is syntactically cor= rect in order to execute it as written a function would need to exist that = does not.=C2=A0 As far as a general syntax diagram goes it has correctly co= mmunicated what is legal.

=C2=A0
whereas

select count(DISTINCT ( parent_id , name) ) from mytable

is accepted.

Correct, converting the= two individual columns into a "tuple" allows the default tuple d= istinct-making infrastructure to be used to execute the query.
<= div>

So I think to handle all cases the line in the doc should read

aggregate_name (DISTINCT ( expression [ , ... ] ) [ order_by_clause ] ) [ F= ILTER ( WHERE filter_clause ) ]

I don't know how to indicate that those extra parentheses can be omitte= d if the list has only one expression.

=
Then I would have to say the proposed solution to this edge case is wors= e than the problem.=C2=A0 I also don't expect there to be a clean solut= ion to dealing with the complexities of expressions at the syntax diagram l= evel.

David J.


--000000000000862fa20598fcfaa5--