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@tao11.riddles.org.uk> wrote:
> [removing the OP from CC list]
>
>>>>>> "Ashutosh" == Ashutosh Bapat <ashutosh.bapat@enterprisedb.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