public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ashutosh Bapat <[email protected]>
To: David Rowley <[email protected]>
Cc: Michał Kłeczek <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY
Date: Thu, 7 Mar 2024 17:24:40 +0530
Message-ID: <CAExHW5taz6hX05P55_S1cbx+P5CcNN21rD7oLY5iXJcShrAbaQ@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvpfyTA3Pnf_P6Kx8s5rNSUBxPfsZxiFz6DpUyMiwqHERw@mail.gmail.com>
References: <[email protected]>
<CAApHDvpfyTA3Pnf_P6Kx8s5rNSUBxPfsZxiFz6DpUyMiwqHERw@mail.gmail.com>
On Thu, Mar 7, 2024 at 4:39 PM David Rowley <[email protected]> wrote:
> On Thu, 7 Mar 2024 at 19:09, Michał Kłeczek <[email protected]> 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.
>
deparseSortGroupClause() calls deparseConst() with showtype = 1.
appendOrderByClause() may want to do something similar for consistency. Or
remove it from deparseSortGroupClause() as well?
>
> Something like the attached patch I think should work.
>
> I wonder if we need a test...
>
Yes.
--
Best Wishes,
Ashutosh Bapat
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY
In-Reply-To: <CAExHW5taz6hX05P55_S1cbx+P5CcNN21rD7oLY5iXJcShrAbaQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox