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

From Tom Lane
Subject Re: [BUGS] Improper const-evaluation of HAVING with grouping sets and subquery pullup
Date
Msg-id 635.1515779101@sss.pgh.pa.us
Whole thread Raw
In response to Re: [BUGS] Improper const-evaluation of HAVING with grouping sets and subquery pullup  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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


pgsql-bugs by date:

Previous
From: Laurent Martelli
Date:
Subject: Re: BUG #15008: Need a pause
Next
From: Tom Lane
Date:
Subject: Re: [BUGS] BUG #14890: Error grouping by same column twice using FDW