Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

On Thu, 7 Mar 2024 at 19:09, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
>
> The following query:
>
> SELECT * FROM (
> SELECT 2023 AS year, * FROM remote_table_1
> UNION ALL
> SELECT 2022 AS year, * FROM remote_table_2
> )
> ORDER BY year DESC;
>
> yields the following remote query:
>
> SELECT [columns] FROM remote_table_1 ORDER BY 2023 DESC
>
> and subsequently fails remote execution.
>
>
> Not really sure where the problem is - the planner or postgres_fdw.
> I guess it is postgres_fdw not filtering out ordering keys.

Interesting. I've attached a self-contained recreator for the casual passerby.

I think the fix should go in appendOrderByClause(). It's at that
point we look for the EquivalenceMember for the relation and can
easily discover if the em_expr is a Const. I think we can safely just
skip doing any ORDER BY <const> stuff and not worry about if the
literal format of the const will appear as a reference to an ordinal
column position in the ORDER BY clause.

Something like the attached patch I think should work.

I wonder if we need a test...

David