Re: BUG #15352: postgresql FDW error "ERROR: ORDER BY position 0 is not in select list"

>>>>> "Ashutosh" == Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:

Ashutosh> Looking at deparseSortGroupClause() this issue looks to be
Ashutosh> fixed in HEAD. Either the version where bug was found doesn't
Ashutosh> have this fix or somehow the fix isn't working.

You're misreading the code. The OP's example clearly demonstrates the
bug when tested with HEAD prior to bf2d0462c or with bf2d0462c reverted.

As the code stands, it _cannot_ use deparseSortGroupClause for
generating EXPLAINs for pathkeys that are chosen by postgres_fdw,
because deparseSortGroupClause wants a tlist, not a plain list of
expressions.

(Perhaps you haven't realized that without the additional check, when
remote estimates are enabled we end up sending EXPLAIN commands to the
remote for paths that cannot possibly be of any use in the query?
Round-trips to the remote are not free.)

>> How do you think that could happen, given that redundant pathkeys
>> are already removed?

Ashutosh> I don't have exact answer.

Then you should find out.

Ashutosh> But deparseSortGroupClause() has code to deparse constants in
Ashutosh> GROUP BY indicates that we do encounter such pathkeys
Ashutosh> somewhere.

GROUP BY isn't based on pathkeys (for one thing, grouping columns might
not be sortable).

Ashutosh> I am thinking about ORDER BY being pushed down for GROUP BY.

Perhaps you should take a look at how the pathkeys for that case are
generated.

--
Andrew (irc:RhodiumToad)