I think I am just looking for an opinion on fundamentals. Can WHERE clause impact an OUTER JOIN ?
What I am seeing is that on 9.2 rows from the joined table are restricted prior to joining them with the rest
of the tables, and that leads to incorrect results. I have noticed such behaviour only when CASE is used
in the WHERE clause - straight text comparison does produce correct results.
Thanks!
________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Monday, December 10, 2012 6:09 PM
To: Alex Burkoff
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] execution plan is wrong, or the query ?
Alex Burkoff <alex.burkoff@jivesoftware.com> writes:
> I have a following query that used to work as intended on 8.3.5 :
> ...
> After upgrade to 9.2 the query doesn't return the same results any more, and the execution plan has changed :
You'd need to provide a self-contained test case if you want an informed
opinion on this. The odds that 8.3.5 was wrong and the newer result is
right are not negligible. (If you were comparing to a *current* 8.3
release I might be more prepared to assume that 9.2 is the one that is
broken.)
FWIW, it looks like the 9.2 plan has been simplified by outer-join
elimination, which implies that unique constraints on the join columns
matter. Possibly you've found a bug in that optimization, but without
a concrete test case it's impossible to be sure, let alone fix it.
regards, tom lane
