Re: SELECT do not return all rows depending on selected columns

Fabrice LEGROS <fabrice.legros@abbd.fr> writes:
> The request return 2122 rows, if I remove the second column (v2.journalcode) from the select I get 2801 rows (which
isthe expected result). 

I think your problem here is an underspecified query.  The ecriturenum
column is not unique, hence "LAG(ecriturenum) OVER ( ORDER BY ecriturenum
ASC )" is not entirely determinate, hence the set of rows selected by
"WHERE ecriturenum - ecriturenumprev != 1" isn't determinate.  I get
slightly different counts than you mention, and I imagine the apparent
dependency on the number of output columns has to do with the volume
of data passing through the sort steps being different, causing the
sorts to do different arbitrary things with equal-keyed rows.

If I change those OVER clauses to be like

LAG(ecriturenum) OVER ( ORDER BY ecriturenum ASC, line_number )

then I get determinate results (since line_number is unique).

            regards, tom lane