Postgres_fdw join pushdown - INNER - FULL OUTER join combination generating wrong result - Mailing list pgsql-hackers

From Rajkumar Raghuwanshi
Subject Postgres_fdw join pushdown - INNER - FULL OUTER join combination generating wrong result
Date
Msg-id CAKcux6nyKFXUkb3B-C-yW_tfdH5wqFc9e=G1G_bidrudE+EL3w@mail.gmail.com
Whole thread Raw
Responses Re: Postgres_fdw join pushdown - INNER - FULL OUTER join combination generating wrong result
List pgsql-hackers
<div dir="ltr"><span style="font-size:small">Hi,</span><font face="arial, helvetica, sans-serif"><br /><span
style="font-size:small"><br/>I am testing postgres_fdw join pushdown feature for PostgreSQL 9.6 DB, and I observed
belowissue.</span></font><u><br /><br />Observation:</u> Inner join and full outer join combination on a table
generatingwrong result.<br /><div class="gmail_extra"><div class="gmail_quote"><div
style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px"><br/>SELECT * FROM lt;<br /> c1 <br />----<br />  1<br
/> 2<br />(2 rows)<br /><br />SELECT * FROM ft;<br /> c1 <br />----<br />  1<br />  2<br />(2 rows)<br /><br />\d+
ft<br/>                             Foreign table "public.ft"<br /> Column |  Type   | Modifiers | FDW Options |
Storage| Stats target | Description <br
/>--------+---------+-----------+-------------+---------+--------------+-------------<br/> c1     | integer |          
|            | plain   |              | <br />Server: link_server<br />FDW Options: (table_name 'lt')<br /><br
/>--innerjoin and full outer join on local tables<br />SELECT t1.c1,t2.c1,t3.c1 FROM lt t1 INNER JOIN lt t2 ON (t1.c1 =
t2.c1)FULL JOIN lt t3 ON (t2.c1 = t3.c1);<br /> c1 | c1 | c1 <br />----+----+----<br />  1 |  1 |  1<br />  2 |  2 | 
2<br/>(2 rows)<br /><br />--inner join and full outer join on corresponding foreign tables<br />SELECT
t1.c1,t2.c1,t3.c1FROM ft t1 INNER JOIN ft t2 ON (t1.c1 = t2.c1) FULL JOIN ft t3 ON (t2.c1 = t3.c1);<br /> c1 | c1 | c1
<br/>----+----+----<br />  1 |  1 |  1<br />  1 |  2 |   <br />  2 |  1 |   <br />  2 |  2 |  2<br />(4 rows)<br
/><spanstyle="font-size:13px"><br /></span><span style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px">Thanks
&Regards,</span><br />Rajkumar Raghuwanshi<br />QMG, EnterpriseDB Corporation<br /></div><blockquote
class="gmail_quote"style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div
dir="ltr"><divclass="gmail_quote"><div dir="ltr"><div class="gmail_quote"><div
dir="ltr"></div></div></div></div></div></blockquote></div><br/></div></div> 

pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: [PATCH] Phrase search ported to 9.6
Next
From: Amit Langote
Date:
Subject: Re: Support for N synchronous standby servers - take 2