Re: query that worked in 8.1 not working in 8.4

Thanks Tom. That is what I suspected. I will install the branch tip on
Monday and see if the problem goes away.

Sean

Tom Lane wrote:
> 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
>