Message-ID: <1f2cbcbd-25fa-9da0-ae0d-a4783aad966a@joeconway.com>
Date: Mon, 30 Jan 2023 15:04:58 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Content-Language: en-US
To: Pete Storer <Pete.Storer@sas.com>, Christophe Pettus <xof@thebuild.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <bruce@momjian.us>,
 "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
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>
 <2961799.1674749201@sss.pgh.pa.us> <Y9fjfNpP7HZh1YX9@momjian.us>
 <f0c0bfec-0571-8711-deee-f6e679846c18@joeconway.com>
 <490923.1675096173@sss.pgh.pa.us>
 <SN6PR05MB66377701BE8410F7D869CBCEF3D39@SN6PR05MB6637.namprd05.prod.outlook.com>
 <B29929A8-448D-49FD-A041-D70D1BBE455A@thebuild.com>
 <SN6PR05MB6637045A3875380FFE2399D3F3D39@SN6PR05MB6637.namprd05.prod.outlook.com>
From: Joe Conway <mail@joeconway.com>
Subject: Re: Query execution failure
In-Reply-To: 
 <SN6PR05MB6637045A3875380FFE2399D3F3D39@SN6PR05MB6637.namprd05.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/1f2cbcbd-25fa-9da0-ae0d-a4783aad966a%40joeconway.com>
Precedence: bulk

On 1/30/23 13:24, Pete Storer wrote:
> Makes sense - but I'm using the PG substring function here.
> 
> LEFT OUTER JOIN main.ship_event se ON (se.ship_event_nm = substring(dp.shipevent from 6 for 5))
> 
> In this case, the dp.shipevent is in the fdw-accessed MySql table. Shouldn't that force the sort to be local?

Looking at the mysql-fdw source (which looks pretty much the same as 
postgres-fdw at first glance):
8<-------------
/* We don't support cases where there are any SRFs in the targetlist */
if (parse->hasTargetSRFs)
	return;
8<-------------

Based on that, perhaps a kluge (and completely untested) workaround is to:
1/ create an SRF that returns one column, one row constant
2/ add the SRF to your targetlist

The SRF likely needs to be plpgsql to avoid inlining, maybe something like:
8<-------------
CREATE OR REPLACE FUNCTION theanswer() RETURNS setof int as $$
BEGIN
  RETURN NEXT 42;
END;
$$ LANGUAGE plpgsql;
8<-------------

HTH,
-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com