Strange behavior on VIEWs consisting from multiple foreign tables

[and0x000]

I currently experience quite a strange behaviour on views, consisting from foreign tables, which differs significantly from what how the view should behave (at least from my point of view (pun intented)).

So I have this VIEW which performs data integration on 2 foreign tables. A simplified example can be found here: http://sqlfiddle.com/#!15/4fcfc/2

When I do a SELECT * WHERE id = 1, I get exactly 1 result.

id | stuff
---+----
 1 | foo

If you replace the regular tables with foreign tables from mysql_fdw, I get 2 results from the table.

id | stuff
---+----
 1 | foo
 3 | bar

I think this is due to the fact of not having something like PostgreSQLs OID in MySQL. Is there anything which can be done in mysql_fdw to resolve this? Or is there a known workaround?

[ahsanhadi]

Can you please share a complete test case that we can use for reproducing the issue?

[ahsanhadi]

I have tried the simple test case http://sqlfiddle.com/#!15/4fcfc/2 with mysql_fdw foreign tables and it is giving me correct results.

[and0x000]

Sorry about the late reply, I was busy with my bachelor thesis.

Yes, with the setup from you fiddle, it gives me

mergedatabase=# select id from myView where id = 2;
 id
-----
   2
 102

I am using two 10.1.22 mariadb docker containers. Both with very elementary setup, the users table looks like this

create table user1 (
    id INT,
    dummy_data VARCHAR(50),
    UNIQUE(id)
);
insert into user1 (id, dummy_data) values (1, 'Russia');
insert into user1 (id, dummy_data) values (2, 'Japan');

Replace "user1" with "user2" and drop it in the second container.

I extended the posgresql docker image (version 9.6.2) with mysql_fdw https://pastebin.com/SLcYXs6r (Dockerfile) Connect the mariadb containers on startup and paste the postgresql setup: https://pastebin.com/BsjTSK6N