[SQL] Postgres count function returns wrong value when using FDW

My following query returns a single average_donation_usd value

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
);


I tried to create materialized views to see it this bug relates to using FWD tables.

CREATE MATERIALIZED VIEW transactions AS
SELECT * FROM transactions_fwd WHERE org_id = 1;

CREATE MATERIALIZED VIEW recurring_payments AS
SELECT * FROM recurring_payments_fwd WHERE org_id = 1;


And it worked well returning me 1 as a result.

select count(*)
FROM (
WITH
transactions AS ( SELECT * FROM transactions WHERE org_id = 1 ),
recurring_payments AS ( SELECT * FROM recurring_payments 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
);



My OS and Postgres version (hosted on heroku)

User => select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
(1 row)

Continue reading...