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

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

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.