Re: Improper const-evaluation of HAVING with grouping sets and subquery pullup

>>>>> "Heikki" == Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:

Heikki> Here's another interesting case, without any subqueries:
Heikki> postgres=# SELECT g as newalias1, g as newalias3
Heikki> FROM generate_series(1,3) g
Heikki> GROUP BY newalias1, ROLLUP(newalias3);
Heikki> newalias1 | newalias3
Heikki> -----------+-----------
Heikki> 1 | 1
Heikki> 3 | 3
Heikki> 2 | 2
Heikki> 2 | 2
Heikki> 3 | 3
Heikki> 1 | 1
Heikki> (6 rows)

Heikki> Why are there no "summary" rows with NULLs, despite the ROLLUP?

To my knowledge this is the correct result. (Though neither version of
the query is legal per the SQL spec; allowing expressions and aliases in
GROUP BY are nonstandard extensions.)

Here's why it happens: after substituting for the aliases, you have

GROUP BY g, rollup(g)

which is equivalent to

GROUP BY GROUPING SETS ((g,g), (g))

which is equivalent to

GROUP BY GROUPING SETS ((g), (g))

because duplicate terms within a single grouping set are redundant just
as they are in GROUP BY.

Heikki> If you replace one of the g's with (g+0), you get the expected
Heikki> result:

Well, in this case the terms in the grouping set are no longer
duplicate; the expansion becomes

GROUP BY GROUPING SETS ((g,(g+0)), (g))

and therefore the (g+0) expression becomes null for one of the resulting
sets.

--
Andrew (irc:RhodiumToad)