-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, July 26, 2011 7:42 PM
To: David Johnston
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #6131: Query Returning Incorrect Results
"David Johnston" <polobo@yahoo.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.