From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Qu, Mischa, Majorel China" <mischa.qu@majorel.cn>
cc: "pgsql-bugs@postgresql.org" <pgsql-bugs@postgresql.org>
Subject: Re: exceptional result of postres_fdw external table joining local
 table
In-reply-to: 
 <BJSPR01MB0532EC69D0B373A7C23F85369CDA9@BJSPR01MB0532.CHNPR01.prod.partner.outlook.cn>
References: 
 <BJSPR01MB0532EC69D0B373A7C23F85369CDA9@BJSPR01MB0532.CHNPR01.prod.partner.outlook.cn>
Comments: In-reply-to "Qu, Mischa, Majorel China" <mischa.qu@majorel.cn>
	message dated "Mon, 06 Feb 2023 10:30:31 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2665265.1675697664.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 06 Feb 2023 10:34:24 -0500
Message-ID: <2665266.1675697664@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2665266.1675697664%40sss.pgh.pa.us>
Precedence: bulk

"Qu, Mischa, Majorel China" <mischa.qu@majorel.cn> writes:
> But when I run some full table scans and found a problems. the results o=
f the following sql were different.

> Method 1: directly join external table and local table
> select count(distinct user_id),count(distinct member_code)
> from a_fdw t1
> join b as t6 on t1.user_id=3Dt6.tags ->> '10'::varchar(50)
> where coalesce(user_id,'') <> '';
> -- result: 50739 50845

What sort of plan is that query using?  If it's a merge join, I'd
bet that the problem is related to local and remote servers not
having the same default collation.

			regards, tom lane