From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Fabrice LEGROS <fabrice(dot)legros(at)abbd(dot)fr> |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: SELECT do not return all rows depending on selected columns |
Date: | 2019-01-04 18:04:41 |
Message-ID: | 9817.1546625081@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Fabrice LEGROS <fabrice(dot)legros(at)abbd(dot)fr> writes:
> The request return 2122 rows, if I remove the second column (v2.journalcode) from the select I get 2801 rows (which is the 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
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2019-01-04 18:48:03 | BUG #15574: Upgrading postgis broke pg_dump |
Previous Message | Tom Lane | 2019-01-04 17:18:24 | Re: BUG #15555: Syntax errors when using the COMMENT command in plpgsql and a "comment" variable |