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
