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

On 2015-12-26 19:19, ttmigueltt(at)gmail(dot)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