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

Heikki Linnakangas <hlinnaka@iki.fi>

From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Pg Bugs <pgsql-bugs@postgresql.org>
Date: 2017-10-12T11:59:12Z
Lists: pgsql-bugs

Attachments

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

Commits

  1. Fix incorrect handling of subquery pullup in the presence of grouping sets.

  2. Make setrefs.c match by ressortgroupref even for plain Vars.

  3. Make the upper part of the planner work by generating and comparing Paths.