Thread: Fwd: weird behavior of ORDER BY
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.
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