-----Original Message----- From: Tom Lane [mailto:t...@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" <pol...@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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
