Functions in sort order - undocumented restriction

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/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.

=?utf-8?q?PG_Doc_comments_form?= <noreply@postgresql.org> writes:

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

What do you find unclear about this error message?

The reason seems clear enough to me: if the ordering expression isn't one
of the values that are being de-duplicated on, then there isn't a unique
value to associate with each surviving row for sorting purposes.

regards, tom lane

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 =''
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
LINE 1: ...n notes as n on o.bid = 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.

​Let me get some clarity now since my first reply saw an "ON" clause 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.
​