Re: ORDER/GROUP BY expression not found in targetlist

Andres Freund <andres@anarazel.de> writes:
> trying to reproduce a performance problem I just found:

> =# CREATE TABLE twocol(col01 int, col02 int);
> =# SELECT DISTINCT col01, col02, col01 FROM twocol ;
> ERROR:  XX000: ORDER/GROUP BY expression not found in targetlist
> LOCATION:  get_sortgroupref_tle, tlist.c:341

> which appears to be a 9.6 regression, presumable fallout from the path
> restructuring.

Huh.  The problem is that createplan.c is trying to apply the
physical-tlist optimization to the seqscan underneath the aggregate
node.  That means that the output from the seqscan is just
"col01, col02", which means that col01 can only be decorated with
a single ressortgroupref ... but there are two ressortgrouprefs
for it as far as the groupClause is concerned.  Only one gets applied
to the seqscan's tlist, and then later we fail because we don't find
the other one there.  Conclusions:

* we need to back off the physical-tlist optimization in this case

* the code that transfers sortgroupref labels onto a tlist probably
ought to notice and complain if it's asked to put inconsistent labels
onto the same column.

I'm a little surprised that it's not discarding the third grouping
item as redundant ... but that's probably not something to mess with
right now.  Prior versions don't appear to do that either.
        regards, tom lane