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

On Thu, Aug 30, 2018 at 2:14 PM, Andrew Gierth
<andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
> [removing the OP from CC list]
>
>>>>>> "Ashutosh" == Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
>
> >> Well, it's certainly pointless.
> >>
> >> But the failure in this case is specifically about pushing down an
> >> _integer_ constant, because the deparse code for pushing down an
> >> ORDER BY does not understand that integer literals in ORDER BY
> >> clauses are a special case.

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

>
> Ashutosh> Deparser needs to be fixed then, irrespective of whether or
> Ashutosh> not we fix the costant pathkey problem.
>
> Since we have no business sending sort expressions to the remote that do
> not include remote vars, this seems superfluous. Any such expression is
> either mutable (and hence not pushable anyway) or known locally to be
> constant (in which case we never legitimately see it in a pathkey).
> (Maybe Asserting it or throwing an error might be appropriate.)
>
> >> I'm pretty sure that constant (hence redundant) clauses have been
> >> removed from pathkeys before postgres_fdw will see them. The problem
> >> only occurs because postgres_fdw tries inventing _new_ pathkeys for
> >> possible orderings from eclasses (in order to try for mergejoin
> >> opportunities) in addition to using the requested pathkeys, and it's
> >> clearly pointless to do that with constants.
>
> Ashutosh> Yes, I forgot about that. But even in that case, we should
> Ashutosh> consider the case when the constant pathkey is just one in
> Ashutosh> the bunch and we are trying to push the whole bunch.
>
> How do you think that could happen, given that redundant pathkeys are
> already removed?

I don't have exact answer. But deparseSortGroupClause() has code to
deparse constants in GROUP BY indicates that we do encounter such
pathkeys somewhere. I am thinking about ORDER BY being pushed down for
GROUP BY.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company