Re: Different result behavior when using ANY(ARRAY(SELECT)) and IN (SELECT)

Davide Jensen <d(dot)jensen(at)tecnoteca(dot)com> writes:
> I'm encountering some problems in understanding the behavior of a query
> that uses an IN operator, the query i'm executing is the following:

> SELECT * FROM (
> SELECT _id,
> ROW_NUMBER() OVER () AS _rownumber

I think your problem is in the above, not anything later in the query.
ROW_NUMBER without any ordering specification is indeterminate.
If the query gets parallelized, it's no surprise that the selected rows
will be processed in varying order from one try to the next. Your
second phrasing of the query seems to be non-parallelizable, but the
row_number() result is still pretty indeterminate; it just doesn't
happen to have changed within your test run.

What is it you are expecting to get out of including a fictional
row number in the query result, anyway?

regards, tom lane