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

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

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)
    • 10.2.42
    • 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

          Activity

            alice Alice Sherepa added a comment -

            Thank you for the report!
            This is probably the same as MDEV-26470, but here recursive cte is used, so I'd rather keep it open to be checked if the patch fixed this case also.

            alice Alice Sherepa added a comment - Thank you for the report! This is probably the same as MDEV-26470 , but here recursive cte is used, so I'd rather keep it open to be checked if the patch fixed this case also.
            danblack Daniel Black added a comment -

            tested this on 10.5.25 and the failing query is resulting in "Query OK, 2 rows affected (0.000 sec)"

            The resulting refs table is (removed rows 9 and 12):

            MariaDB [DB_Test]> select * from refs;
            +----+---------+-----+
            | id | item_id | val |
            +----+---------+-----+
            |  1 |       2 |   1 |
            |  2 |       3 |   1 |
            |  3 |       4 |   1 |
            |  4 |       5 |   1 |
            |  5 |       6 |   1 |
            |  6 |       7 |   1 |
            |  7 |       8 |   1 |
            |  8 |       9 |   1 |
            | 10 |       3 |   2 |
            | 11 |       4 |   2 |
            | 13 |       1 |   2 |
            | 14 |       1 |   3 |
            +----+---------+-----+
            12 rows in set (0.000 sec)
            

            going to assume a duplicate. Thanks for the bug report.

            danblack Daniel Black added a comment - tested this on 10.5.25 and the failing query is resulting in "Query OK, 2 rows affected (0.000 sec)" The resulting refs table is (removed rows 9 and 12): MariaDB [DB_Test]> select * from refs; +----+---------+-----+ | id | item_id | val | +----+---------+-----+ | 1 | 2 | 1 | | 2 | 3 | 1 | | 3 | 4 | 1 | | 4 | 5 | 1 | | 5 | 6 | 1 | | 6 | 7 | 1 | | 7 | 8 | 1 | | 8 | 9 | 1 | | 10 | 3 | 2 | | 11 | 4 | 2 | | 13 | 1 | 2 | | 14 | 1 | 3 | +----+---------+-----+ 12 rows in set (0.000 sec) going to assume a duplicate. Thanks for the bug report.

            People

              igor Igor Babaev (Inactive)
              vinceroy Vincent Roy
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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