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

>>>>> "David" == David Rowley <dgrowleyml@gmail.com> writes:

 David> hmm yeah, certainly a bug. On a very quick look, it looks like
 David> the CTE inlining code

Nope. You can tell it's not that because rewriting it with no CTEs at
all does not eliminate the bug (and this way, it reproduces right back
to 9.5, oops):

select *
  from (select coalesce(country_id, city_id) AS location_id,
               total
          from (select 1 as country_id,
                       city_id,
                       sum(cnt) as total
                  from (values (2,5),(2,1),(3,2),(3,7)) as table1(city_id,cnt)
                 group by grouping sets (1,2)) base) fin
 where location_id=1;
 location_id | total 
-------------+-------
           1 |    15
           2 |     6
           3 |     9
(3 rows)

The problem here is that something is assuming that the country_id is
still constant 1 despite its participation in grouping sets rendering it
sometimes null.

Using a materialized CTE avoids the bug (at least partially) by hiding
the constant projection from the optimizer.

Most likely, that constant column needs to either be treated as not
constant, or something should be replacing it with a PHV - I'd have to
dig into the code a bit to see what's actually going wrong.

-- 
Andrew (irc:RhodiumToad)