From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: aidar@donorbox.org
Subject: BUG #18690: A count function returns wrong value when using FDW
Date: Wed, 06 Nov 2024 08:57:20 +0000
Message-ID: <18690-cbb98b56ecb0b130@postgresql.org> (raw)
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)
```
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, aidar@donorbox.org
Subject: Re: BUG #18690: A count function returns wrong value when using FDW
In-Reply-To: <18690-cbb98b56ecb0b130@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox