Re: Bugs in planner's equivalence-class processing

Joel Jacobson <joel(at)trustly(dot)com> writes:
> If helpful, here is a simple test to reproduce the problem:
> http://pgsql.privatepaste.com/6429e8a200

FWIW, this is fixed already in git, or at least this particular example
gives what seems the right answer:

fooid | barid | fooint
-------+-------+--------
2 | | 1
(1 row)

> Would you recommend me to rewrite all queries of this particular
> type, where you have COALESCE in the WHERE statement,
> as a precaution?

No, but you might want to grab the appropriate patch and apply it
locally, if you tend to write queries like this. You want one
of these:

Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Branch: master [72a4231f0] 2012-10-18 12:30:10 -0400
Branch: REL9_2_STABLE [0237b3945] 2012-10-18 12:30:25 -0400
Branch: REL9_1_STABLE [447dad719] 2012-10-18 12:29:00 -0400
Branch: REL9_0_STABLE [afdc7515f] 2012-10-18 12:29:06 -0400
Branch: REL8_4_STABLE [779016271] 2012-10-18 12:29:13 -0400
Branch: REL8_3_STABLE [c29a91037] 2012-10-18 12:29:19 -0400

Fix planning of non-strict equivalence clauses above outer joins.

> We haven't migrated to 9.2 yet, but perhaps there is a risk
> similar queries can render the same problems even in 9.1?

9.2 has considerably more scope to make this kind of error, but related
bugs can be demonstrated as far back as 7.4. It's a bit surprising
nobody noticed until now.

regards, tom lane