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@sss.pgh.pa.us> ha scritto:
Davide Jensen <d.jensen@tecnoteca.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?