public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: Functions in sort order - undocumented restriction
Date: Sat, 10 Feb 2018 13:54:46 -0700
Message-ID: <CAKFQuwb_7UWTYgAGAmJn-PE2qFLc5VZgjAr7ZNSWYNJcmWg8rw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Sat, Feb 10, 2018 at 4:40 AM, PG Doc comments form <
[email protected]> 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.
​

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Functions in sort order - undocumented restriction
  In-Reply-To: <CAKFQuwb_7UWTYgAGAmJn-PE2qFLc5VZgjAr7ZNSWYNJcmWg8rw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox