innodb_version: 10.3.34

version: 10.3.34-MariaDB-0ubuntu0.20.04.1

Simple query:

DELETE `anode`, `adevice`
FROM `adevice`
JOIN `adevice_node`
    ON `adevice_node`.`device` = `adevice`.`id`
JOIN `anode`
    ON `anode`.`id` = `adevice_node`.`node`
WHERE `adevice`.`id` = 1

Structure: (adevice <- adevice_node -> anode)

CREATE TABLE `adevice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `adevice` VALUE (1);

CREATE TABLE `anode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `anode` VALUES (1), (2);

CREATE TABLE `adevice_node` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device` int(11) NOT NULL,
  `node` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_adevice_node_1_idx` (`device`),
  KEY `fk_adevice_node_2_idx` (`node`),
  CONSTRAINT `fk_adevice_node_1` FOREIGN KEY (`device`) REFERENCES `adevice` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_adevice_node_2` FOREIGN KEY (`node`) REFERENCES `anode` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `adevice_node` VALUES (1, 1, 1), (2, 1, 2);

I suppose, it must delete all rows from adevice with id = 1, all children from adevice_node (CASCADE) and all their children from anode, but it does not: only one row from anode deleted (and all needed rows from another tables). Why? What is the right way to do this deletion IN ONE QUERY?

P.S: If we complicate the query (add several JOINs after adevice_node and third table in deletion list), then all the necessary records are deleted...

Update: It might be this old bug... bugs.mysql.com/bug.php?id=44207