Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS

On Wed, 19 Aug 2020 at 23:12, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> 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.

hmm yeah, certainly a bug. On a very quick look, it looks like the
CTE inlining code is to blame as it works ok if the fin CTE is
materialized (as it would have been before 608b167f9). i.e:

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 MATERIALIZED (
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;

I see with the materialized version the CTE has a qual. This is the
qual that appears to go missing in the non-materialized version:

CTE Scan on fin (cost=0.28..0.39 rows=1 width=12)
Filter: (location_id = 1)

David