BUG #14709: inconsistent answers with foreign data wrappers to mysql

The following bug has been logged on the website:

Bug reference: 14709
Logged by: Mark Manley
Email address: mark(dot)manley(at)tapad(dot)com
PostgreSQL version: 9.6.2
Operating system: CentOS 7.3
Description:

Greetings.

When using a foreign data wrapper from our 9.6.2 database to a MySQL 5.7
database, we get inconsistent results. We're joining two tables from
another server:

dsp_users
dsp_accounts

This table is local in our Postgres server:

audiences

This query returns only the first record, which is using simple joins:

select audiences.* FROM audiences
INNER JOIN dsp_users ON dsp_users.id = audiences.created_by
INNER JOIN dsp_accounts ON dsp_accounts.id = audiences.account_id
WHERE audiences.name like '%Test%'

and I get only the first hit.

When I run the composite queries under it, removing the like clause from the
predicate, I get all the results I would expect:

1 |23842583561190673 |XXXXXXXX |sg1 |Test audience
| |1012 |269 |2017-05-03
15:04:57 |
2 |23842583562050673 |XXXXXXXX |sg1 |Test audience
| |1012 |269 |2017-05-03
15:25:59 |
10 |23842688223720004 |XXXXXXXX |sg1 |Audience DTAC
Test 1 | |1013 |307 |2017-06-09
16:40:54 |

with the clause:

1 |23842583561190673 |XXXXXXXX |sg1 |Test audience
| |1012 |269 |2017-05-03
15:04:57 |

If I rewrite the query to turn the foreign MySQL tables into a subselect,
then I get the correct results:

select a.* from audiences a
where exists (select 1 from audiences a, dsp_users du, dsp_accounts da
where
a.created_by = du.id
and a.account_id = da.id)

1 |23842583561190673 |XXXXXXXX |sg1 |Test audience
| |1012 |269 |2017-05-03
15:04:57 |
2 |23842583562050673 |XXXXXXXX |sg1 |Test audience
| |1012 |269 |2017-05-03
15:25:59 |
10 |23842688223720004 |XXXXXXXX |sg1 |Audience DTAC
Test 1 | |1013 |307 |2017-06-09
16:40:54 |

As you can see, I get inconsistent results if I use a normal join versus a
subselect. It's as though the like filter in the predicate is breaking.
This behaviour does not happen with using likes joining local tables only.

Anyway, wanted you guys to know.

Thanks!