Re: BUG #18690: A count function returns wrong value when using FDW - Mailing list pgsql-bugs

I am trying to reproduce it on a new db but I couldn't so far, though I was able to simplify the queries

```select count(*)
FROM (
SELECT AVG(t.usd_amount_cents / 100) AS average
FROM transactions t
JOIN recurring_payments r ON r.id = t.recurring_payment_id
WHERE t.org_id = 1 AND r.org_id = 1 and  t.status = 'paid'                                                        
);
```
 count
-------
 22429
(1 row)

```
select count(*)
FROM (
SELECT AVG(t.usd_amount_cents / 100) AS average
FROM transactions t
JOIN recurring_payments r ON r.id = t.recurring_payment_id
WHERE t.status = 'paid'                                                        
);
```
 count
-------
     1
(1 row)

Those queries are the same and both should return 1 because the inner query returns just one AVG value. The difference between those two queries is `t.org_id = 1 AND r.org_id = 1`

Here are SQL explains

explain verbose
select count(*)
FROM (
SELECT AVG(t.usd_amount_cents / 100) AS average
FROM transactions t
JOIN recurring_payments r ON r.id = t.recurring_payment_id
WHERE t.org_id = 1 AND r.org_id = 1 and  t.status = 'paid'
);
                                                                                                                                                                       QUERY PLAN                                                            
                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=213.22..213.22 rows=1 width=8)
   Output: count(*)
   ->  Foreign Scan  (cost=102.30..213.21 rows=1 width=32)
         Output: NULL::numeric
         Relations: Aggregate on ((((transactions t) INNER JOIN (orgs o)) INNER JOIN (recurring_payments rp)) INNER JOIN (orgs o_1))
         Remote SQL: SELECT NULL::numeric FROM (((public.transactions r4 INNER JOIN public.orgs r5 ON (((r4.org_id = r5.id)) AND ((r5.provider_id = 1)) AND ((r4.status = 'paid')))) INNER JOIN public.recurring_payments r7 ON (((r4.recurrin
g_payment_id = r7.id)))) INNER JOIN public.orgs r8 ON (((r7.org_id = r8.id)) AND ((r8.provider_id = 1))))
(7 rows)

explain verbose
select count(*)
FROM (
SELECT AVG(t.usd_amount_cents / 100) AS average
FROM transactions t
JOIN recurring_payments r ON r.id = t.recurring_payment_id
WHERE t.status = 'paid'                                                    
);
                                                                                                                                          QUERY PLAN                                                                                          
                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
 Aggregate  (cost=15478.69..15478.69 rows=1 width=8)
   Output: count(*)
   ->  Aggregate  (cost=15478.69..15478.69 rows=1 width=32)
         Output: NULL::numeric
         ->  Foreign Scan  (cost=396.66..15478.69 rows=820775 width=32)
               Relations: (((transactions t) INNER JOIN (orgs o)) INNER JOIN (recurring_payments rp)) INNER JOIN (orgs o_1)
               Remote SQL: SELECT NULL FROM (((public.transactions r4 INNER JOIN public.orgs r5 ON (((r4.org_id = r5.id)) AND ((r4.status = 'paid')))) INNER JOIN public.recurring_payments r7 ON (((r4.recurring_payment_id = r7.id)))) INNER
 JOIN public.orgs r8 ON (((r7.org_id = r8.id))))

(8 rows)

Thanks and regards
Aidar