Re: ORDER/GROUP BY expression not found in targetlist

Hi,

I got same error by TPC-H: Q1,4,8,12 and 17.
I've attached results of the queries.

TPC-H (thanks to Tomas Vondra)
  https://github.com/tvondra/pg_tpch

  Datasize
     Scale Factor: 1

PG96beta1
  commit: f721e94b5f360391fc3ffe183bf697a0441e9184

Regards,
Tatsuro Yamada
NTT OSS Center

On 2016/05/27 2:22, Tom Lane wrote:
> 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
>
>