Re: Bugs in planner's equivalence-class processing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joel Jacobson <joel(at)trustly(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Bugs in planner's equivalence-class processing
Date: 2012-11-02 23:27:16
Message-ID: 7225.1351898836@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2012-11-03 00:08:18 Re: Synchronous commit not... synchronous?
Previous Message Alexander Korotkov 2012-11-02 21:07:39 Re: [v9.3] writable foreign tables