On 14 January 2016 at 09:19, wrb <wrb@autistici.org> wrote:
> No, I'm pretty sure this is incorrect result, because views are supposed =
to provide logical data independence and running the same query through nes=
ted select, CTE and view should give the same result, right? If not, I'm pr=
etty sure this is the first case where it doesn't work like this.
>
Yes, there appears to be a real bug here, but actually the underlying
cause is more to do with the way HAVING and GROUPING SETS interact.
When the clause is pushed down into the view it is turned from a WHERE
clause into a HAVING clause, because the view query has grouping. That
part is OK. However, the query planner then decides that since the new
HAVING clause doesn't contain any aggregates or volatile expressions,
it is safe to turn it back into a WHERE clause in the inner query.
That would be OK for normal grouping, because in that case the clause
would evaluate the same for all the rows in a group. However, that
doesn't work for grouping sets, since the output of the grouping sets
aggregation may contains nulls not present before grouping.
Here is a simpler example:
create table foo(a int, b int);
insert into foo values (1,1), (2,2);
select a, count(*) from foo group by cube(a);
a | count
---+-------
1 | 1
2 | 1
| 2
(3 rows)
select a, count(*) from foo group by cube(a) having a is null;
a | count
---+-------
| 0
(1 row)
select a, count(*) from foo group by cube(a) having a is distinct from 1;
a | count
---+-------
2 | 1
| 1
(2 rows)
The results from these last 2 queries are incorrect -- they're not the
same as evaluating the HAVING clause after grouping.
This bug appears to have been introduced by commit
61444bfb809d3a088a270a59f383af3d4cd157b0, which was discussed here:
http://www.postgresql.org/message-id/20150726151456.GD12755@awork2.anarazel=
.de
Probably that change should be undone and the comment expanded to
explain why it isn't safe when the query has grouping sets.
Regards,
Dean