DROP DATABASE IF EXISTS DB_Test;

CREATE DATABASE DB_Test;

CREATE TABLE IF NOT EXISTS `DB_Test`.`item_tree` (

  `item_id` INT(11) UNSIGNED NOT NULL,

  `child_id` INT(11) UNSIGNED NOT NULL,

  INDEX `fk_item_idx` (`item_id` ASC),

  INDEX `fk_child_id` (`child_id` ASC),

  PRIMARY KEY (`item_id`, `child_id`))

ENGINE = InnoDB;	

INSERT INTO `DB_Test`.`item_tree`(`item_id`, `child_id`)

VALUES 

	(1, 2),

    (1, 3),

    (1, 4),

    (2, 5),

    (2, 9),

    (5, 6),

    (3, 7),

    (3, 8);

CREATE TABLE IF NOT EXISTS `DB_Test`.`refs` (

  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,

  `item_id` INT(11) UNSIGNED NOT NULL,

  `val` INT(11) UNSIGNED NOT NULL,

  INDEX `fk_refs_item_id_idx` (`item_id` ASC),

  PRIMARY KEY (`id`)

)

ENGINE = InnoDB;

INSERT INTO `DB_Test`.`refs`(`id`, `item_id`, `val`)

VALUES

	(1, 2, 1),

    (2, 3, 1),

    (3, 4, 1),

    (4, 5, 1),

    (5, 6, 1),

    (6, 7, 1),

    (7, 8, 1),

    (8, 9, 1),

	(9, 2, 2),

    (10, 3, 2),

    (11, 4, 2),

    (12, 5, 2),

    (13, 1, 2),

    (14, 1, 3);

--

-- Failing query

--

SET @OLDitem = 2;

SET @parentItemId = 1;

USE `DB_Test`;

DELETE FROM `refs`

WHERE EXISTS (

	WITH RECURSIVE `subfolders` AS (

		SELECT @OLDitem AS `id`

		UNION ALL

		SELECT `tree`.`child_id` AS `id`

		FROM `subfolders`

		INNER JOIN `item_tree` as `tree` ON `subfolders`.`id`=`tree`.`item_id`

	)

	SELECT * -- null

	FROM `subfolders`, `refs` as `fbl`

	WHERE 

		`fbl`.`item_id`=@parentItemId

		AND `refs`.`item_id`=`subfolders`.`id`

		AND `refs`.`val`=`fbl`.`val`

);