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

From: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
To: Pg Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Improper const-evaluation of HAVING with grouping sets and subquery pullup
Date: 2017-10-12 11:59:12
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-bugs

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

Attachment Content-Type Size
0001-Fix-subquery-pullup-into-a-query-containing-GROUPING.patch text/x-patch 4.8 KB


Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Dunstan 2017-10-12 12:38:25 Re: BUG #14849: jsonb_build_object doesn't like VARIADIC calls very much
Previous Message KES 2017-10-12 10:01:06 Re: BUG #14850: Implement optinal additinal parameter for 'justify' date/time function