Re: [BUGS] Improper const-evaluation of HAVING with grouping sets and subquery pullup - Mailing list pgsql-bugs

>>>>> "Heikki" == Heikki Linnakangas <hlinnaka@iki.fi> writes:
Heikki> Here's another interesting case, without any subqueries:Heikki> postgres=# SELECT g as newalias1, g as
newalias3Heikki>FROM generate_series(1,3) gHeikki> GROUP BY newalias1, ROLLUP(newalias3);Heikki>  newalias1 |
newalias3Heikki>-----------+-----------Heikki>          1 |         1Heikki>          3 |         3Heikki>          2 |
       2Heikki>          2 |         2Heikki>          3 |         3Heikki>          1 |         1Heikki> (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 expectedHeikki> 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)


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs