Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26469

"No database selected" error reported even if a database is selected

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              vinceroy Vincent Roy
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration