Bug in postgres 9.6.2?

[greigwise <greigwise@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general