Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.5.12, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
-
None
-
reproduced on both Windows 10 / Suse Linux Enterprise Server 15
Description
When executing a Delete request with a CTE, the request fails with "No database selected" error.
Below a script that reproduce the issue:
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` |
);
|
Attachments
Issue Links
- relates to
-
MDEV-26470 "No database selected" when using CTE in a subquery of DELETE statement
- Closed