Re: Removing Functionally Dependent GROUP BY Columns

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> On 12/02/2016 12:01 am, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Um, AFAICS, you *do* need to check again in the second loop, else you'll
>> be accessing a surplusvars[] entry that might not exist at all, and in
>> any case might falsely tell you that you can exclude the outer var from
>> the new GROUP BY list.

> I can't quite understand what you're seeing here.

The second loop is iterating through the original GROUP BY list, so it
will see again any outer Vars that were excluded by the first loop.
It needs to exclude them exactly the same, because they are outside
the scope of its data structures. Consider something like (admittedly
pretty silly, but legal SQL)

create table up (u1 int, u2 int, u3 int);
create table down (f1 int primary key, f2 int);

select * from othertable, up
where u1 in (select f2 from down group by f1, f2, up.u3);

up.u3 would have varlevelsup = 1, varno = 2, varattno = 3.
If you don't skip it then the surplusvars[var->varno] access
will be trying to fetch off the end of the surplusvars[] array,
because there is only one RTE in the subquery's rangetable
though there are two in the outer query's rangetable.

When I trace through this example, it manages not to crash because
in point of fact the outer Var has already been replaced by a Param.
But I don't think this code should be assuming that; it's an artifact
of the detailed order of processing in subquery_planner(), and could
easily change in the future, for example if we tried to move the
whole affair to the jointree preprocessing stage as we discussed earlier.

regards, tom lane