Re: BUG #18690: A count function returns wrong value when using FDW

Thanks David

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

On Wed, Nov 6, 2024 at 12:03 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 6 Nov 2024 at 22:58, PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> > ```
> > WITH
> >
> > transactions AS ( SELECT * FROM transactions_fwd WHERE org_id = 1
> > ),
> > recurring_payments AS ( SELECT * FROM recurring_payments_fwd
> WHERE
> > org_id = 1 )
> > SELECT AVG(t.usd_amount_cents / 100) AS average_donation_usd
> > FROM transactions t
> > JOIN recurring_payments r ON r.id = t.recurring_payment_id
> > WHERE t.status = 'paid' AND t.deleted_at IS NULL AND
> t.amount_refunded_cents
> > = 0
> > ```
> >
> > But when I wrap it to `select count(*) from (my_query)` i get 288 as a
> > result, but I expect it to be 1.
> >
> > ```
> > select count(*)
> > FROM (
> > WITH
> >
> > transactions AS ( SELECT * FROM transactions_fwd WHERE org_id = 1
> > ),
> > recurring_payments AS ( SELECT * FROM recurring_payments_fwd
> WHERE
> > org_id = 1 )
> > SELECT AVG(t.usd_amount_cents / 100) AS average_donation_usd
> > FROM transactions t
> > JOIN recurring_payments r ON r.id = t.recurring_payment_id
> > WHERE t.status = 'paid' AND t.deleted_at IS NULL AND
> t.amount_refunded_cents
> > = 0
> > );
> > ```
>
> Can you show the EXPLAIN ANALYZE of both of these queries?
>
> Also, can you work on getting a self-contained reproducer that we can
> run to recreate the issue.
>
> David
>