Re: Bugs in planner's equivalence-class processing

When I tried our test suite against 9.2.1 one of the tests failed,
it looked really strange, the query had returned a row which could
impossibly match the WHERE statement.

I heard on #postgresql this bug has already been reported.

If helpful, here is a simple test to reproduce the problem:
http://pgsql.privatepaste.com/6429e8a200

From what I understand from the discussion, this "bug" or behaviour
has been around for quite some time, dating back to 7.4,
perhaps not exactly the same in all major versions though.

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

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?

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