From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pavelsivash@gmail.com
Subject: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
Date: Wed, 19 Aug 2020 10:31:49 +0000
Message-ID: <16585-9d8c340d23ade8c1@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 16585
Logged by: Paul Sivash
Email address: pavelsivash@gmail.com
PostgreSQL version: 12.4
Operating system: x86_64-pc-linux-gnu
Description:
Hello! There is a problem with filtering COALESCE field which has constant
from nested subselect with GROUPING SETS as first element.
Example:
WITH table1 AS (
SELECT 2 AS city_id, 5 AS cnt
UNION ALL
SELECT 2 AS city_id, 1 AS cnt
UNION ALL
SELECT 3 AS city_id, 2 AS cnt
UNION ALL
SELECT 3 AS city_id, 7 AS cnt
),
fin AS (
SELECT
coalesce(country_id, city_id) AS location_id,
total
FROM (
SELECT
1 as country_id,
city_id,
sum(cnt) as total
FROM table1
GROUP BY GROUPING SETS (1,2)
) base
)
SELECT *
FROM fin
WHERE location_id = 1;
As you can see in the end I want to keep only rows with location_id = 1 but
the result gives me all available rows. This happens because Postgres sees
that I filter COALESCE field which has "country_id" as first element and
"country_id" is previously set as constant - 1. But the thing is that using
GROUPING SETS turns "country_id" to NULL in some rows and this behaviour is
wrong.
When I change final filter to "location_id = 2" it returns 0 rows for the
same reason.
Thank you in advance!
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, pavelsivash@gmail.com
Subject: Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
In-Reply-To: <16585-9d8c340d23ade8c1@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