From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pete Storer <Pete.Storer@sas.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: FW: Query execution failure
In-reply-to: 
 <BL0PR05MB66282D22D96AB745FBBD521FF3CF9@BL0PR05MB6628.namprd05.prod.outlook.com>
References: 
 <BL0PR05MB66282A9070EE621D031C27C7F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
 <BL0PR05MB66283C57D72E321591AE4EB1F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com>
 <2957101.1674746628@sss.pgh.pa.us>
 <BL0PR05MB66282D22D96AB745FBBD521FF3CF9@BL0PR05MB6628.namprd05.prod.outlook.com>
Comments: In-reply-to Pete Storer <Pete.Storer@sas.com>
	message dated "Thu, 26 Jan 2023 15:43:37 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2961798.1674749201.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 26 Jan 2023 11:06:41 -0500
Message-ID: <2961799.1674749201@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2961799.1674749201%40sss.pgh.pa.us>
Precedence: bulk

Pete Storer <Pete.Storer@sas.com> writes:
> I'm not sure how to identify which index may be corrupt, if that is the =
cause. Regarding a different sort order between tables, if that were the c=
ase, wouldn't this query fail in Production as it does in our Development =
environment? They are identical with respect to indices and sort order, an=
d they both point to the same foreign table, which resides on a MySQL data=
base.

Meh.  You've just got two merge joins in that plan, and three of the
four inputs to them are being explicitly sorted, so one would certainly
hope that those sorts are consistent.  But the fourth input is being
sourced directly from the foreign server, and so this query is
fundamentally assuming that the foreign server has the same idea of
sort ordering as the local one.  Evidently that assumption is wrong.

I'd check locale settings on your two databases to understand why
it doesn't fail on prod.  But the long-term solution if you want this
to work reliably is probably to force use of C locale on all three
databases; that's about the only locale that you can fully trust
to be portable.

I don't know anything about the mysql FDW, but another possible route
to fixing things is to get it to not believe that the remote's sort
ordering matches the local one.  If the plan were relying on a local
sort instead of a remote sort then everything would be fine.

			regards, tom lane