Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ekcAg-0003um-OS for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 20:54:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ekcAf-0001UD-9h for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 20:54:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ekcAf-0001U4-1G for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 20:54:53 +0000 Received: from mail-qt0-x241.google.com ([2607:f8b0:400d:c0d::241]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ekcAb-0006PJ-9k for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 20:54:52 +0000 Received: by mail-qt0-x241.google.com with SMTP id i8so14754258qtj.9 for ; Sat, 10 Feb 2018 12:54:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=Zmf5xKQ0Y9Fm4CvMyFsYHGDcCXMg/1dIC3ERLvYBzF8=; b=GGOJZYJvtujXzBVV2gahzx+czMM/qoA/+LkwCeJ8et4mXdYZYqrHTQ17OjwQgFSREI hNIosm/Fyb5gn+bvXXWuJodSQJFUYyD8OinIBc7oiEoftREyPLiIIP0Yb1O1c3hWJKCS MN5e/ZmnS8rQMVx2bxv0UIyAdMs2eJMZMOvVyVbKvppHYNvikz9wBiYqjlK4oGPTiG1t N7X53UXOPXNSUFPnmKRZOo6HgXImsZvW+5Ag53L/uRreZQLqhnl+IPTzHrPCoUMMstmw tNDY2K/8VbQrShZsWdoWQgMLXWODM+kzyzoFSopkLYBaGFpyAG5SIBjzXpMftGHdrzLZ lwoQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=Zmf5xKQ0Y9Fm4CvMyFsYHGDcCXMg/1dIC3ERLvYBzF8=; b=e+wO4LjXNXHZVAuI+L/my8Po7uYhWILOJuY2i3oXfx9IGjcCzOubmHYwO1tQMKOnW5 f4OeXeVvbAwujx17lelP5YAB6UK3bkc/Vx7ye488570t+w8VDTZzahMIad4H4M03ChKI snXQkn5p5pkt/YwCuDu3KYpYloz04FOb+wD6VHglmd1qGWyy4Qw8UpUazKRXenv+j9wn 4/pbOmo2D6G2hCdRWRHTCtLRYuPWrla7lX404e2q9Oqg2KDG6vkxdpkJeZbwXJ76A2Q3 GpE2Iy3D3KfDCfspBcnOcUsgt7WyXB7i8EkCWS+u2eBZuI1zhbbrHJVOEYwMgtjURYae z+cA== X-Gm-Message-State: APf1xPDWwO8SaxOMo02yo+S1TuSao1a2y/IunSZ03efJBBPX2jMDlqrM 8Cytu+/SMErncd39JGqH/MPX5ffH70f5ClqKZxE= X-Google-Smtp-Source: AH8x2265a/GbpTnaMo6oD8WUYaEhD9vt5sIkPiEtIBToC9//Z0W0gQqfXES32M7+g4VA2Lx+04H3afefaPb5S3p8uns= X-Received: by 10.237.46.132 with SMTP id k4mr11276521qtd.325.1518296086758; Sat, 10 Feb 2018 12:54:46 -0800 (PST) MIME-Version: 1.0 Received: by 10.12.135.38 with HTTP; Sat, 10 Feb 2018 12:54:46 -0800 (PST) In-Reply-To: <151826283029.1443.7699948679319699778@wrigleys.postgresql.org> References: <151826283029.1443.7699948679319699778@wrigleys.postgresql.org> From: "David G. Johnston" Date: Sat, 10 Feb 2018 13:54:46 -0700 Message-ID: Subject: Re: Functions in sort order - undocumented restriction To: andrew@grillet.co.uk, pgsql-docs@lists.postgresql.org Content-Type: multipart/alternative; boundary="94eb2c0678c6ea4b4e0564e1da48" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --94eb2c0678c6ea4b4e0564e1da48 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Feb 10, 2018 at 4:40 AM, PG Doc comments form < noreply@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/9.5/static/queries-order.html > Description: > > This works: > > select distinct o.bid, organisation, posttown, replace(case when phone = =3D'' > then null else trim(phone) end, ' ', ''), phone, o.active, website, email= , > (select count(*) from notes as n where n.bid =3D o.bid) as nn from > organisations as o right join notes as n on o.bid =3D n.bid where true or= der > by replace(case when phone =3D'' then null else trim(phone) end, ' ', '') > nulls last ; > > This does not work: > > select distinct (o.bid), organisation, posttown, replace(case when > postcode > =3D'' then null else trim(postcode) end, ' ', '') as pc, phone, o.active, > website, email, (select count(*) from notes as n where n.bid =3D o.bid) a= s nn > from organisations as o right join notes as n on o.bid =3D n.bid where tr= ue > order by replace(case when phone =3D'' then null else trim(phone) end, ' = ', > '') nulls last ; > ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select > list > LINE 1: ...n notes as n on o.bid =3D n.bid where true order by replace(ca= ... > > The documentation does not explain the restriction, nor that, or why, you > cannot use 'as' to rename the field and then cite the renamed version. > =E2=80=8BLet me get some clarity now since my first reply saw an "ON" claus= e where there isn't one... What was the point of adding parens around o.bid in the second query? (I assumed it was to do DISTINCT ON but you didn't add the word ON) Why did you change replace(case when phone...) in the first query with replace (case when postcode...) in the second query - but only in the select-list? (phone no longer exists in the select-list and thus you get the error.) The following works just fine with an alias in the ORDER BY: WITH vals (v,t,o) AS ( VALUES (1,'a',10),(2,'c',20),(3,'d',30),(3,'b',40),(4,'e',50) ) SELECT DISTINCT v, replace(t || '2', ' ', '') AS v2 FROM vals ORDER BY v2 Adding "o" to the ORDER BY provokes the error you have been seeing. Tom has explained why this is the case. In short, DISTINCT seems to effectively remove the ability to specify "arbitrary expressions from input column values" in the ORDER BY - though you can still specify non-arbitrary expressions, as long as they are the same expressions that are used in the select-list. David J. =E2=80=8B --94eb2c0678c6ea4b4e0564e1da48 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sat, Fe= b 10, 2018 at 4:40 AM, PG Doc comments form <noreply@postgresql.org> wrote:
The following documentation comment has been logged on the website:=

Page: https://www.postgresql.org/d= ocs/9.5/static/queries-order.html
Description:

This works:

select distinct o.bid, organisation, posttown, replace(case when phone =3D&= #39;'
then null else trim(phone) end, ' ', ''), phone, o.active, = website, email,
(select count(*) from notes as n where n.bid =3D o.bid) as nn from
organisations as o right join notes as n on o.bid =3D n.bid where true orde= r
by replace(case when phone =3D'' then null else trim(phone) end, &#= 39; ', '')
nulls last ;

This does not work:

=C2=A0select distinct (o.bid), organisation, posttown, replace(case when po= stcode
=3D'' then null else trim(postcode) end, ' ', '') a= s pc, phone, o.active,
website, email, (select count(*) from notes as n where n.bid =3D o.bid) as = nn
from organisations as o right join notes as n on o.bid =3D n.bid where true=
order by replace(case when phone =3D'' then null else trim(phone) e= nd, ' ',
'') nulls last ;
ERROR:=C2=A0 for SELECT DISTINCT, ORDER BY expressions must appear in selec= t
list
LINE 1: ...n notes as n on o.bid =3D n.bid where true order by replace(ca..= .

The documentation does not explain the restriction, nor that, or why, you cannot use 'as' to rename the field and then cite the renamed versi= on.

=E2=80=8BLet me = get some clarity now since my first reply saw an "ON" clause wher= e there isn't one...

What was the point of adding = parens around o.bid in the second query? (I assumed it was to do DISTINCT O= N but you didn't add the word ON)

Why did you chan= ge replace(case when phone...) in the first query with replace (case when p= ostcode...) in the second query - but only in the select-list?=C2=A0 (phone= no longer exists in the select-list and thus you get the error.)
<= br>
The following works just fine with an alias in the ORDER BY:

WITH vals (v,t,o) AS ( VALU= ES (1,'a',10),(2,'c',20),(3,'d',30),(3,'b',= 40),(4,'e',50) )
SELECT DISTINCT = v, replace(t || '2', ' ', '') AS v2=C2=A0
FROM vals ORDER BY v2

Adding "o" to the OR= DER BY provokes the error you have been seeing.

Tom has explained why this is= the case.=C2=A0 In short, DISTINCT seems to effectively remove the ability= to specify "arbitrary expressions from input column values" in t= he ORDER BY - though you can still specify non-arbitrary expressions, as lo= ng as they are the same expressions that are used in the select-list.
=

David J= .
=E2=80=8B
--94eb2c0678c6ea4b4e0564e1da48--