Thread: BUG #13833: Postgres ORDER BY value inside json causes “column does not exist” error

The following bug has been logged on the website:

Bug reference:      13833
Logged by:          Michael Milton
Email address:      ttmigueltt@gmail.com
PostgreSQL version: 9.4.4
Operating system:   Fedora 22
Description:

It's probably easiest to link to the StackOverflow question I posted:
http://stackoverflow.com/questions/34472961/postgres-order-by-value-inside-json-causes-column-does-not-exist-error

The summary of it is that this query throws the error "column sets does not
exist", despite the fact that it is created in the SELECT clause:

SELECT
    coalesce(block.name, 'Other') as name,
    json_agg(set.data) as sets
FROM
    set
    FULL OUTER JOIN block ON set.block_id = block.id
GROUP BY block.id
ORDER BY sets

On 2015-12-26 19:19, ttmigueltt@gmail.com wrote:
> It's probably easiest to link to the StackOverflow question I posted:
> http://stackoverflow.com/questions/34472961/postgres-order-by-value-inside-json-causes-column-does-not-exist-error

This is not a bug.  See the accepted answer and
http://www.postgresql.org/docs/9.4/static/sql-select.html#SQL-ORDERBY:

"Each expression can be the name or ordinal number of an output column
(SELECT list item), or it can be an arbitrary expression formed from
input-column values."

The important part is that if you want to order by an arbitrary
expression, you can only use input-column values in it.

> The summary of it is that this query throws the error "column sets does not
> exist", despite the fact that it is created in the SELECT clause:
>
> SELECT
>      coalesce(block.name, 'Other') as name,
>      json_agg(set.data) as sets
> FROM
>      set
>      FULL OUTER JOIN block ON set.block_id = block.id
> GROUP BY block.id
> ORDER BY sets

No, this query really doesn't, since you're ordering by exactly the
alias, not by an expression using the alias.  "ORDER BY sets IS NULL",
for example, would throw an error, but that still isn't a bug, as per above.


.m