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

[Davide Jensen <d.jensen@xxxxxxxxxxxxx>]

Thanks for the suggestion,

I've tried to edit the query and added an order by in the OVER statement but i'm still obtaining two results, but now of course those results are sequential, the edited beginning of the query is:

 SELECT *
FROM   (
              SELECT _id,
                     ROW_NUMBER() OVER (ORDER BY _id ASC) AS _rownumber
...

The results of 3 executions:

* 1) 64297639;22326
64297639;22327

* 2) 64297639;22942
64297639;22943

* 3) 64297639;22072
64297639;22073

Regarding the fictional row number, it's needed for some internal handling of the results, the query is generated by a java application.

Thanks,

Davide Jensen

Il giorno lun 9 nov 2020 alle ore 16:10 Tom Lane <tgl@xxxxxxxxxxxxx> ha scritto:

Davide Jensen <d.jensen@xxxxxxxxxxxxx> 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