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

>>>>> "Ashutosh" == Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:

 Andrew> It seems obviously wrong that a constant pathkey with no actual
 Andrew> reference to the foreign table should be being pushed down, so
 Andrew> so far I suspect that get_useful_pathkeys_for_relation isn't
 Andrew> being selective enough about what is "useful". In this context
 Andrew> I find it suspicious that find_em_expr_for_rel will return an
 Andrew> expr with no vars as being "for" every rel, since it's just
 Andrew> looking for a subset.

 Ashutosh> Sorry for replying late. I am not able to understand why it's
 Ashutosh> wrong to push a constant or for the matter any shippable
 Ashutosh> expression which doesn't refer to the foreign table/s (for
 Ashutosh> that matter any tables) under consideration down to the
 Ashutosh> foreign server.

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.

 Ashutosh> The context in the original mail doesn't help. I haven't
 Ashutosh> checked the original thread on bugs mailing list. I agree
 Ashutosh> that ordering by such an expression is useless, but if we are
 Ashutosh> getting that done from a foreign server, what's the harm? But
 Ashutosh> by not doing it we might be loosing some optimization since
 Ashutosh> postgres_fdw pushes all or none of pathkeys.

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.

-- 
Andrew (irc:RhodiumToad)