Indexed update or delete can clobber a CONNECT table

Firstly, the issue is for CONNECT that must handle updates or deletes sorted by the position of implied rows in the file.

For the table:

CREATE TABLE t1 (

id INT(4) KEY NOT NULL,

msg VARCHAR(16))

ENGINE=CONNECT TABLE_TYPE=DOS;

INSERT INTO t1 VALUES(4, 'four'),(7,'seven'),(10,'ten'),(40,'forty'),(60,'sixty'),(81,'eighty one'),(72,'seventy two'),(11,'eleven'),(1,'one'),(35,'thirty five'),(8,'eight');

When I do:

UPDATE t1 SET msg = 'updated' WHERE id IN (8,35,60,72);

I get 4 calls to indx_read each followed by update_row. Knowing it's an indexed command, I can store the needed data, sort it by the file position and do the updates as required.

The problems occur when the index is multiple and partially used:

CREATE TABLE t1 (

id INT(4) NOT NULL,

msg VARCHAR(16) NOT NULL,

INDEX IDM(id,msg))

ENGINE=CONNECT TABLE_TYPE=DOS;

INSERT INTO t1 VALUES(1,'un'),(1,'one'),(4,'quatre'),(4, 'four'),(7,'sept'),(7,'seven'),(8,'huit'),(8,'eight'),(10,'dix'),(10,'ten');

INSERT INTO t1 VALUES(11,'onze'),(35,'trente cinq'),(40,'quarante'),(60,'soixante'),(72,'soixante douze'),(81,'quatrevingt un');

INSERT INTO t1 VALUES(11,'eleven'),(35,'thirty five'),(40,'forty'),(60,'sixty'),(72,'seventy two'),(81,'eighty one');

Now if I do:

UPDATE t1 SET msg='septante deux' WHERE id = 72;

The calls to indx_read and indx_next_same are followed by a call to position. Then MariaDB reverts to not indexed rnd_pos reads followed by calls to update_row. It is as if MariaDB used MRR even the optimizer_switch.mrr variable is off. It would be all right if the positions had been sorted (as does MRR) but the problem is that they are not and the calls to update_row are done in the wrong order, CONNECT not being aware to sort it because the reading is no more indexed.

Note that the same occurs whether or not the optimizer_switch mrr variable is set on or off.

The current version of CONNECT returns a clobbered table in that case because intermediate rows are moved or copied several times.