Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS - Mailing list pgsql-bugs

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