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
betweentables, if that were the case, wouldn't this query fail in Production as it does in our Development environment?
Theyare identical with respect to indices and sort order, and they both point to the same foreign table, which resides
ona MySQL database.
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