From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | pavelsivash(at)gmail(dot)com |
Subject: | BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS |
Date: | 2020-08-19 10:31:49 |
Message-ID: | 16585-9d8c340d23ade8c1@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 16585
Logged by: Paul Sivash
Email address: pavelsivash(at)gmail(dot)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!
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2020-08-19 11:34:19 | Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS |
Previous Message | Jiří Fejfar | 2020-08-19 06:08:42 | Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails |