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

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> 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;

> 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.

Ouch.

> 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.

Seems like the point is that grouping sets can inject null values of
columns, in more or less the same way that outer joins can.  So it
seems like using PlaceHolderVars, which were invented to account
for that property of outer joins, is a reasonable approach to a fix.
I don't have time to review the patch in detail right now though;
do you want to put it in the CF queue?

One thing I'm wondering is why only the HAVING clause would be subject
to the problem.  I'm a bit surprised that the "x" in the targetlist
didn't become a constant as well.  This may be pointing to some
klugery in the GROUPING SETS patch that we could clean up if we
use placeholders for this.
        regards, tom lane


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