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!