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(dot)foreman(at)mpaygateway(dot)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
>