Asymmetric results matching binary values in foreign tables vs local

[sir-galahad]

I've found the following issue regarding comparing binary/BYTEA values. The instructions below work to explain the problem. This assumes mysql_fdw is already installed and set up as a foreign server.

On mysql: CREATE DATABASE binarytest; USE binarytest; CREATE TABLE testtable (name VARCHAR(20), binary_value VARBINARY(20)); INSERT INTO testtable (name,binary_value) VALUES ('Aaron','hello'), ('Bob',X'0102030405');

On PostgreSQL: CREATE SCHEMA binarytest CREATE TABLE binarytest.testtable_local (name VARCHAR(20),binary_value BYTEA); INSERT INTO binarytest.testtable_local (name,binary_value) VALUES ('Aaron','hello'), ('Bob','\x0102030405'); CREATE FOREIGN TABLE binarytest.testtable_mysql (name VARCHAR(20),binary_value BYTEA) SERVER mysql_server OPTIONS (dbname 'binarytest', table_name 'testtable');

---

With that set up done we can see a difference in results when using a foreign binary column in a where clause:

testdb=# SELECT * FROM binarytest.testtable_local; name | binary_value -------+-------------- Aaron | \x68656c6c6f Bob | \x0102030405 (2 rows)

testdb=# SELECT * FROM binarytest.testtable_mysql; name | binary_value -------+-------------- Aaron | \x68656c6c6f Bob | \x0102030405 (2 rows)

testdb=# SELECT * FROM binarytest.testtable_local WHERE binary_value = 'hello'; name | binary_value -------+-------------- Aaron | \x68656c6c6f (1 row)

testdb=# SELECT * FROM binarytest.testtable_mysql WHERE binary_value = 'hello'; name | binary_value ------+-------------- (0 rows)

testdb=# SELECT * FROM binarytest.testtable_local WHERE binary_value = '\x0102030405'; name | binary_value ------+-------------- Bob | \x0102030405 (1 row)

testdb=# SELECT * FROM binarytest.testtable_mysql WHERE binary_value = '\x0102030405'; name | binary_value ------+-------------- (0 rows)

---

Long story short both the foreign and local table show the same values, but matching in the where clause doesn't give the same results. The reason for this is that mysql_fdw is pushing down the binary data in postgres hex format '\x...', but for the test to pass mysql_fdw needs to send X'...'

[cbandy]

Was this fixed by #142?

[ibrarahmad]

Yes it is fixed.

[ibrarahmad]

postgres=# SELECT * FROM binarytest.testtable_local; name | binary_value -------+-------------- Aaron | \x68656c6c6f Bob | \x0102030405 (2 rows)

postgres=# SELECT * FROM binarytest.testtable_mysql; name | binary_value -------+-------------- Aaron | \x68656c6c6f Bob | \x0102030405 (2 rows)

postgres=# SELECT * FROM binarytest.testtable_local WHERE binary_value = 'hello'; name | binary_value -------+-------------- Aaron | \x68656c6c6f (1 row)

postgres=# SELECT * FROM binarytest.testtable_mysql WHERE binary_value = 'hello'; name | binary_value -------+-------------- Aaron | \x68656c6c6f (1 row)

postgres=# SELECT * FROM binarytest.testtable_mysql WHERE binary_value = 'hello'; name | binary_value -------+-------------- Aaron | \x68656c6c6f (1 row)

postgres=# SELECT * FROM binarytest.testtable_local WHERE binary_value = '\x0102030405'; name | binary_value ------+-------------- Bob | \x0102030405 (1 row)

postgres=# SELECT * FROM binarytest.testtable_mysql WHERE binary_value = '\x0102030405'; name | binary_value ------+-------------- Bob | \x0102030405 (1 row)