[BUGS] 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.manley@tapad.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 dawherea.created_by = du.idand 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!


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs