search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
521 stars 160 forks source link

bytes after ` `(0x02 0x01 0x00) get truncated in column with type `text` #299

Open n0099 opened 2 months ago

n0099 commented 2 months ago

mysql side

CREATE TABLE test(t text);
INSERT INTO test(t) VALUES (CONCAT(0x020100, '1234'));
SELECT hex(t) FROM test;
HEX(t)
02010031323334

pgsql side

CREATE FOREIGN TABLE IF NOT EXISTS test(t text)
    SERVER mysql
    OPTIONS (dbname 'db', table_name 'test');
SELECT encode(t::bytea, 'hex') FROM test;
encode
0201

all bytes after 0x00 and itself are removed, but it will be complete fetched when using bytea as field type:

CREATE FOREIGN TABLE IF NOT EXISTS test(t bytea)
    SERVER mysql
    OPTIONS (dbname 'db', table_name 'test');
SELECT encode(t::bytea, 'hex') FROM test;
encode
02010031323334

It's seems related to https://github.com/confluentinc/kafka-connect-jdbc/issues/1216 as postgresql doesn't support storing 0x00 in text unlike mysql due to c-style NUL-terminated string https://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0 https://postgrespro.com/list/thread-id/1897280