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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Pg Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: [BUGS] Improper const-evaluation of HAVING with grouping sets and subquery pullup
Date: 2018-01-12 17:45:01
Message-ID: 635.1515779101@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-bugs

I wrote:
> I think the attached is probably ready to go, though I've not checked yet
> whether it will work pre-9.6. Anyone want to do more review?

I pushed this back through 9.5. Oddly, the test case Andrew proposed,

select (x+y)*1, sum(z)
from (select 1 as x, 2 as y, 3 as z) s
group by grouping sets (x+y, x);

passes in unmodified 9.5. I haven't bothered to try to figure out
exactly why the difference in behavior; I have a feeling it's related
to the upper planner pathification changes in 9.6.

However ... we're not out of the woods yet. Shortly after pushing
it occurred to me that the basic problem, const-simplification of
expressions whose subexpressions should match grouping set expressions,
can happen without any subquery at all. For instance:

regression=# select q1=q2, not(q1=q2), q1 from int8_tbl
group by grouping sets(q1=q2, q1);
?column? | ?column? | q1
----------+----------+------------------
f | |
t | |
| t | 123
| t | 4567890123456789
(4 rows)

Surely that's wrong.

So, IMO, basically what this shows is that we have to identify and replace
subexpressions that should match grouping set expressions before the
planner starts to do expression processing, rather than just leaving it to
accidentally happen in setrefs.c. I still like the idea of representing
such subexpressions as Vars referencing a dummy RTE, but that seems
unlikely to lead to a back-patchable fix. However, I'm not sure that any
back-patchable fix would be practical anyway, so maybe we should just
accept that this is going to stay broken in the back branches. At least
it doesn't arise for spec-compliant queries.

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-01-12 21:55:17 Re: [BUGS] BUG #14890: Error grouping by same column twice using FDW
Previous Message Laurent Martelli 2018-01-12 13:12:47 Re: BUG #15008: Need a pause