The following bug has been logged on the website:
Bug reference: 18690
Logged by: Aidar Uldanov
Email address: aidar@donorbox.org
PostgreSQL version: 16.4
Operating system: Ubuntu
Description:
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)
```
