>>>>> "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)