This query produces an incorrect result:
regression=# select four, x
from (select four, ten, 'foo'::text as x from tenk1 ) as t
group by grouping sets(four, x) having x = 'foo' order by four;
four | x
------+-----
0 |
1 |
2 |
3 |
| foo
(5 rows)
The "having x = 'foo'" clause should've filtered out the rows where x is
NULL, leaving only the last row as the result. Even though x is a
constant 'foo' in the subquery, HAVING clause is supposed to be
evaluated after grouping. What happens is that subquery pullup replaces
x with the constant, and the "'foo' = 'foo'" qual is later
const-evaluated to true.
I propose the attached patch to fix that. It forces the use of
PlaceHolderVars in subquery pullup, if the parent query has grouping
sets and HAVING. I'm not 100% sure that's the right approach or a misuse
of the placeholder system, so comments welcome. At first, I tried to set
wrap_non_vars=true only when processing the havingQual, so that
placeholders would only be there. But that didn't work out, I think
because grouping sets planning would then put both the Const, and the
PlaceHolderVar for the Const, in the Agg's targetlist, but only one of
them would be set to NULL when doing the grouping.
Another thing is that the check could be made much tighter, so that
PlaceHolderVars were only used for expressions actually used in the
HAVING. But it didn't seem worth the trouble to me.
- Heikki
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs