From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Heikki Linnakangas <hlinnaka(at)iki(dot)fi> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pg Bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Improper const-evaluation of HAVING with grouping sets and subquery pullup |
Date: | 2017-10-17 09:16:58 |
Message-ID: | 87tvyyjg2s.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>>>>> "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)
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2017-10-17 09:50:24 | Re: Improper const-evaluation of HAVING with grouping sets and subquery pullup |
Previous Message | Heikki Linnakangas | 2017-10-17 07:44:23 | Re: Improper const-evaluation of HAVING with grouping sets and subquery pullup |