Re: weird behavior of ORDER BY

On Thu, Apr 26, 2018 at 2:29 AM, zoolus . <700671(at)gmail(dot)com> wrote:

> pgsql-bugs(at)postgresql(dot)org
>
>
> Hello, friends!
>
> This statement returns nothing:
>
> WITH x AS (VALUES (1))
> SELECT * FROM x
> ORDER BY unnest(NULL :: INT [])
> ;
>
> --0 rows retrieved
> --PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit
> --PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit
>
> I think any "ORDER BY" construct can't filter result set.
>

​While a bit surprising I don't really have a problem with it. I suppose I
would have expected that set-returning-functions in ORDER BY would be
prohibited but absent that restriction this result is consistent with other
behavior: if you had placed the unnest in the select-list and done "ORDER
BY #" to reference it you would have achieved the same result.

One of the hackers would need to provide insight as to how reasonable
"fixing" this behavior would be (and whether the standard has anything to
say on the topic) - otherwise a quick documentation update would suffice
for me personally.

David J.