Re: BUG #6131: Query Returning Incorrect Results

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, July 26, 2011 7:42 PM
To: David Johnston
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6131: Query Returning Incorrect Results

"David Johnston" <polobo(at)yahoo(dot)com> writes:
> The embedded script exhibits some strange behavior. When the query is
> run the [adjustment_paid] column for [technician] "6338B" should show +/-
25.00.
> Instead, if I run the last query immediately after creating the
> schema and inserting the data the results I get for that technician is
> 0.00 for both records. However, if I continue to run the query, or
> explicitly run ANALYZE , I then get the correct results.

Interesting example. I don't believe it's a Postgres bug though, but rather
indeterminism in your query. If you pull out the subquery that is being fed
to the window aggregate (row_number()):

SELECT s_id, date_reference, accountnumber, technician, rate_paid, COUNT(*)
AS rate_count FROM laborwip_payroll_ticket GROUP BY s_id, date_reference,
accountnumber, technician, rate_paid ORDER BY s_id, date_reference,
accountnumber, technician, rate_count DESC;

you will find that it produces slightly different output row ordering before
and after the ANALYZE. I get

... you get only the first one of those two rows. And then your upper
query's results vary depending on which rate_paid you got. So basically you
need to add more columns to the window aggregate PARTITION/ORDER BY clauses
to make the result more deterministic.

-----------------------------------------

Now I feel like a schmuck...sorry for the noise. I should/do know better
but my mind is fried. Thank you so much for the quick response.

David J.