title: MySQL DELETE from two tables does not delete all records
body: <p>innodb_version: 10.3.34</p>
  <p>version: 10.3.34-MariaDB-0ubuntu0.20.04.1</p>
  <p>Simple query:</p>
  <pre><code>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
  </code></pre>
  <p>Structure:
  (adevice &lt;- adevice_node -&gt; anode)</p>
  <pre><code>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);
  </code></pre>
  <p>I suppose, it must delete all rows from <code>adevice</code> with <code>id</code> = 1, all children from <code>adevice_node</code> (CASCADE) and all their children from <code>anode</code>, but it does not: only one row from <code>anode</code> deleted (and all needed rows from another tables).
  Why? What is the right way to do this deletion IN ONE QUERY?</p>
  <p>P.S: If we complicate the query (add several JOINs after <code>adevice_node</code> and third table in deletion list), then all the necessary records are deleted...</p>
  <p>Update: It might be this old bug...
  <a href="https://bugs.mysql.com/bug.php?id=44207" rel="nofollow noreferrer">bugs.mysql.com/bug.php?id=44207</a></p>