So, I have this query with nothing non-deterministic in it, yet I can run it multiple times and get different results in postgres 9.6.2:
with test as ( select g.id2 from staging a join dim_1 e on e.id1 = a.id1 and a.created_at >= e.effective_at and a.created_at < e.expired_at join dim_2 g on g.id2 = a.id2 and a.created_at >= g.effective_at and a.created_at < g.expired_at left join dim_3 i on i.text_field = g.text_field join dim_4 h on h.id4 = a.id4 and a.created_at >= h.effective_at and a.created_at < h.expired_at) select count(*) from test; Now if I rework this query slightly, it produces a consistent result: select count(*) from ( select g.id2 from staging a join dim_1 e on e.id1 = a.id1 and a.created_at >= e.effective_at and a.created_at < e.expired_at join dim_2 g on g.id2 = a.id2 and a.created_at >= g.effective_at and a.created_at < g.expired_at left join dim_3 i on i.text_field = g.text_field join dim_4 h on h.id4 = a.id4 and a.created_at >= h.effective_at and a.created_at < h.expired_at) as test; Furthermore, if I run the first query on postgres 9.6.3 rather than 9.6.2, it also seems to produce a consistent result. Is it possible that this is a bug in 9.6.2 that was fixed in 9.6.3? Or is it more likely that somehow just restarting the instance (after doing the point release upgrade) is what fixed this issue. Noteworthy is that all the "created_at", "effective_at" and "expired_at" fields are all timestamp without time zone. All the id's are integers. Thanks in advance, Greig Wise -- View this message in context: http://www.postgresql-archive.org/Bug-in-postgres-9-6-2-tp5972185.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
