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:

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)