Incorrect UPDATE and DELETE on tables where primary key has multiple columns

[DuncanSands]

Postgres 9.6; mysql_fdw REL-2_2_0

If you have a MySQL with a primary key consisting of multiple columns, eg x and y, and you update or delete specifying a value for x and y, then all rows with that value for x are updated/deleted: the value of y is ignored.

To reproduce:

In MySQL:

CREATE TABLE test(x int, y int, z int, PRIMARY KEY (x, y)); GRANT SELECT,DELETE,UPDATE,INSERT ON test TO 'postgres'@'localhost'; INSERT INTO test VALUES (1, 1, 1), (1, 2, 2), (1, 3, 3);

In PostGres:

CREATE FOREIGN TABLE test(x int not null, y int not null, z int) SERVER mysql_database OPTIONS (dbname 'the_db', table_name 'test'); SELECT * FROM test; x | y | z ---+---+--- 1 | 1 | 1 1 | 2 | 2 1 | 3 | 3 (3 rows)

UPDATE test SET z=1 WHERE x=1 AND y=1; SELECT * FROM test; x | y | z ---+---+--- 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 (3 rows)

DELETE FROM test WHERE x=1 AND y=1; SELECT * FROM test; x | y | z ---+---+--- (0 rows)

[jmealo]

Is this related to #130?

[DuncanSands]

This still doesn't work correctly with postgres 15.1 and mysql fdw 2.8.0: the update continues to update all rows, not just matching rows, and the delete continues to delete all rows, not just matching rows.

[DuncanSands]

MySQL log for the postgres UPDATE. Note how the condition "y=1" has been lost in the MySQL UPDATE:

2023-02-28T09:53:16.388449Z        16 Query     SET sql_mode = 'ANSI_QUOTES'
2023-02-28T09:53:16.388990Z        16 Query     EXPLAIN tst.test
2023-02-28T09:53:16.398087Z        16 Query     SET sql_mode = 'ANSI_QUOTES'
2023-02-28T09:53:16.398846Z        16 Prepare   SELECT `x`, `y`, `z` FROM `tst`.`test` WHERE ((`x` = 1)) AND ((`y` = 1)) FOR UPDATE
2023-02-28T09:53:16.399429Z        16 Prepare   UPDATE `tst`.`test` SET `z` = ? WHERE x = ?
2023-02-28T09:53:16.399772Z        16 Execute   SELECT `x`, `y`, `z` FROM `tst`.`test` WHERE ((`x` = 1)) AND ((`y` = 1)) FOR UPDATE
2023-02-28T09:53:16.400657Z        16 Execute   UPDATE `tst`.`test` SET `z` = 1 WHERE x = 1
2023-02-28T09:53:16.403643Z        16 Close stmt        
2023-02-28T09:53:16.403723Z        16 Close stmt        

MySQL log for the postgres DELETE. Note how the condition y=1 has been lost in the MySQL DELETE:

2023-02-28T09:53:50.936915Z        16 Query     SET sql_mode = 'ANSI_QUOTES'
2023-02-28T09:53:50.937437Z        16 Query     EXPLAIN tst.test
2023-02-28T09:53:50.946612Z        16 Query     SET sql_mode = 'ANSI_QUOTES'
2023-02-28T09:53:50.947294Z        16 Prepare   SELECT `x` FROM `tst`.`test` WHERE ((`x` = 1)) AND ((`y` = 1)) FOR UPDATE
2023-02-28T09:53:50.948036Z        16 Prepare   DELETE FROM `tst`.`test` WHERE x = ?
2023-02-28T09:53:50.948412Z        16 Execute   SELECT `x` FROM `tst`.`test` WHERE ((`x` = 1)) AND ((`y` = 1)) FOR UPDATE
2023-02-28T09:53:50.949479Z        16 Execute   DELETE FROM `tst`.`test` WHERE x = 1
2023-02-28T09:53:50.952624Z        16 Close stmt        
2023-02-28T09:53:50.952715Z        16 Close stmt