Re: query that worked in 8.1 not working in 8.4 - Mailing list pgsql-general

Sean Foreman <sean.foreman@mpaygateway.com> writes:
> We recently upgraded postgres from 8.1 to 8.4.
> One of our queries stopped working and after some digging I've narrowed
> the problem down to this:

> select
>     count(customer.customer_id)
> from
>     acquire.customer customer
>     inner join entity_setup.merchant_set merchant_set on
>         (customer.merchant_set_id = merchant_set.merchant_set_id
>         and merchant_set.merchant_set_id in (
>             select merchant_set_id
>             from entity_setup.merchant merchant
>             where merchant.merchant_id in (4,8,85,67)))
> where
>     customer.merchant_set_id = 2;

There are some bugs in 8.4.0 associated with possibly re-ordering
semijoins (IN joins) incorrectly with respect to other joins.
It looks like you got bit by that.  Are you in a position to try
8.4 branch tip (from CVS or nightly snapshots)?  If not, you'll
have to wait for 8.4.1, but it'd be nice to confirm this case
is fixed before we ship 8.4.1.

            regards, tom lane