Multi-table DELETE with LEFT JOIN, ORDER BY and LIMIT skips a selected right-table row

CREATE DATABASE delete_test;

USE delete_test;

CREATE TABLE a (

  id INT PRIMARY KEY

) ENGINE=InnoDB;

CREATE TABLE b (

  id INT PRIMARY KEY,

  aid INT,

  sort_value INT

) ENGINE=InnoDB;

INSERT INTO a VALUES (1),(2),(3);

INSERT INTO b VALUES (11,1,30),(12,1,10),(21,2,20);

SET SESSION sql_buffer_result=1;

EXPLAIN

DELETE a, b

FROM a LEFT JOIN b ON a.id=b.aid

ORDER BY COALESCE(b.sort_value,-1),a.id,b.id

LIMIT 2;

SELECT a.id,b.id

FROM a LEFT JOIN b ON a.id=b.aid

ORDER BY COALESCE(b.sort_value,-1),a.id,b.id

LIMIT 2;

PREPARE del_stmt FROM

'DELETE a, b

 FROM a LEFT JOIN b ON a.id=b.aid

 ORDER BY COALESCE(b.sort_value,-1),a.id,b.id

 LIMIT 2';

SET SESSION tmp_table_size=16777216;

SET SESSION max_heap_table_size=16777216;

START TRANSACTION;

EXECUTE del_stmt;

SELECT 'affected_mode_0',ROW_COUNT();

SELECT 'a_mode_0',GROUP_CONCAT(id ORDER BY id) FROM a;

SELECT 'b_mode_0',GROUP_CONCAT(id ORDER BY id) FROM b;

ROLLBACK;

SET SESSION tmp_table_size=1024;

SET SESSION max_heap_table_size=16384;

START TRANSACTION;

EXECUTE del_stmt;

SELECT 'affected_mode_1',ROW_COUNT();

SELECT 'a_mode_1',GROUP_CONCAT(id ORDER BY id) FROM a;

SELECT 'b_mode_1',GROUP_CONCAT(id ORDER BY id) FROM b;

ROLLBACK;

DEALLOCATE PREPARE del_stmt;

DROP DATABASE delete_test;

id  id

3   NULL

1   12

affected_mode_0  2

a_mode_0         2

b_mode_0         11,12,21

affected_mode_1  2

a_mode_1         2

b_mode_1         11,12,21