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

On Wed, 6 Nov 2024 at 22:58, PG Bug reporting form
<noreply@postgresql.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