Fwd: weird behavior of ORDER BY

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

pgsql-bugs@postgresql.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.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

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

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.

Indeed. Consider also the case where the SRF in ORDER BY produces more
than one output per input row --- how many result rows do you expect to
get then? If you don't like reasoning about these cases, don't use a
SRF in ORDER BY.

regards, tom lane