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 1ekX2h-0002Pz-RN for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 15:26:20 +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 1ekX2h-000503-0h for pgsql-docs@arkaria.postgresql.org; Sat, 10 Feb 2018 15:26:19 +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 1ekX2g-0004zu-Pf for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 15:26:18 +0000 Received: from mail-qt0-x243.google.com ([2607:f8b0:400d:c0d::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ekX2e-0006pA-2a for pgsql-docs@lists.postgresql.org; Sat, 10 Feb 2018 15:26:18 +0000 Received: by mail-qt0-x243.google.com with SMTP id x27so14158674qtm.12 for ; Sat, 10 Feb 2018 07:26:15 -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=whPUjd+xtbSZs5WdbPfUVEwYjl0a6mvACPLTHnYTnYA=; b=bBBzDjvEGaAyKY7yBdE+UXuXQlF2SQqTcn2OmYrklMOLI8moSQuBHXRGKYlgcYSFPx fOJPoCwVHpI6oU/fP0PyDeSrIcnWPfB0tVAedBj1NoJdCbD5OTfbiMMWaLNhcYw/qJsX BYo8kOynZ1wE4+iCA14wvBqrmY9l7y4I7pDPkGo4f7ZTnJuhJ3sl5jr5aNEGcNzzaQ/t 7NgjrLlqtRvpwvazDnFknPQrLq3JcUnu3CZ3X40TMVJoxtjCU7NyLpDVNxq8+ca7Z9vL cMXFyqMS/PtBS+w9Qq9odsGPQi5Nlmw4dmF1PwQTXs+TnDjvkAx2nWzHbcK13uftK5EV RRZw== 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=whPUjd+xtbSZs5WdbPfUVEwYjl0a6mvACPLTHnYTnYA=; b=Ex0f27XUaKZRdtC88+boZVlgSz0lbLM2P3LmcO9YaIj3L9N3K5Vg9eEBor3EjzX8kC RkVEgiH1uPSise/+RJyTFmYc44DQqoAQKAK5lUmUpugcAGQsKS1w02Agg50lyo57zXLX KAoLwj1joOrnLrdI3PFNELzaqH/B3Tu6UWVosYcrSpPhDfth86N+6H66eU8K17PJTvTe 1OoiSVToEGR52vTi5Ffv9nEU91Sd5lDlHwkqt/Z7bxHhc03misFg9emSgLLDa5XYTElv /CDiK6LGRFVv0ESrrR0tRUbgjOxG+fXSNZUlXD9r0eDPuJrMIWEO3+vq04iAW9BQr833 Rv1Q== X-Gm-Message-State: APf1xPCN4ggeDawmQpWCEcmNmfvgkCp8m31Kps0jeuFSsxiqP3gjf/dv fgVxAjowI5ZW9kebAz6YPrOdOm9+Q7n9cHMCKnc= X-Google-Smtp-Source: AH8x224kF/Pb8WysJSVsob29jxBVvLJXoq1FyT3nN4NOYVTYMp4+iJrMeKBpQ+5AnrgaMgjJ7qfC2Dt72axUFF5tvGU= X-Received: by 10.237.46.132 with SMTP id k4mr10066937qtd.325.1518276373601; Sat, 10 Feb 2018 07:26:13 -0800 (PST) MIME-Version: 1.0 Received: by 10.12.135.38 with HTTP; Sat, 10 Feb 2018 07:26:13 -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 08:26:13 -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="94eb2c0678c6eb63470564dd43b6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --94eb2c0678c6eb63470564dd43b6 Content-Type: text/plain; charset="UTF-8" On Saturday, February 10, 2018, PG Doc comments form 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 ='' > then null else trim(phone) end, ' ', ''), phone, o.active, website, email, > (select count(*) from notes as n where n.bid = o.bid) as nn from > organisations as o right join notes as n on o.bid = n.bid where true order > by replace(case when phone ='' then null else trim(phone) end, ' ', '') > nulls last ; > > This does not work: > > select distinct (o.bid), organisation, posttown, replace(case when > postcode > ='' then null else trim(postcode) end, ' ', '') as pc, phone, o.active, > website, email, (select count(*) from notes as n where n.bid = o.bid) as nn > from organisations as o right join notes as n on o.bid = n.bid where true > order by replace(case when phone ='' then null else trim(phone) end, ' ', > '') nulls last ; > ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select > list I suppose that error message should say "FOR SELECT DISTINCT ON, ORDER BY ..." > LINE 1: ...n notes as n on o.bid = n.bid where true order by replace(ca... > > The documentation does not explain the restriction, It is explained in the SELECT command documentation. """ The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group. """ https://www.postgresql.org/docs/9.5/static/sql-select.html > > nor that, or why, you > cannot use 'as' to rename the field and then cite the renamed version. > You can and it does with limitations. "Note that an output column name has to stand alone, that is, it cannot be used in an expression" You are correct that the "why" behind some of the decisions and limitations is not always covered in great detail. I suppose that the sorting docs could cover DSTINCT ON, it just needs someone willing to write it up. Since the SELECT docs cover the material it is a usability issue as opposed to a correctness one. Also, that chapter is somewhat of an introductory piece and distinct on is somewhat of an intermediate query concept. Adding it there might be considered too much information at that point in the docs. Given the directness of the error and the coverage in the command reference the status quo has merit. David J. --94eb2c0678c6eb63470564dd43b6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Saturday, February 10, 2018, 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/quer= ies-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

I suppose that error message should sa= y "FOR SELECT DISTINCT ON, ORDER BY ..."
=C2=A0
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,=C2=A0
= =C2=A0
It is explained in the SELECT command documentation.
=

=C2=A0"""
The=C2=A0DISTINCT ON=C2=A0express= ion(s) must match the leftmost=C2=A0ORDER BY=C2=A0expression(s). The=C2=A0ORDER BY=C2=A0clause will norma= lly contain additional expression(s) that determine the desired precedence = of rows within each=C2=A0DISTINCT ON=C2=A0group.
"""=

=C2=A0

nor that, or why, you cannot use 'as' to rename the field and then cite the renamed versi= on.

You can and it does with limitations.

"Note that an output column name has to stand a= lone, that is, it cannot be used in an expression"

You are correct that the "why" behind some of th= e decisions and limitations is not always covered in great detail.

I suppose that the sorting docs could cover DSTINCT ON, it= just needs someone willing to write it up.=C2=A0 Since the SELECT docs cov= er the material it is a usability issue as opposed to a correctness one.=C2= =A0 Also, that chapter is somewhat of an introductory piece and distinct on= is somewhat of an intermediate query concept.=C2=A0 Adding it there might = be considered too much information at that point in the docs.=C2=A0 Given t= he directness of the error and the coverage in the command reference the st= atus quo has merit.

David J.

<= div>
 --94eb2c0678c6eb63470564dd43b6--