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

USE '<db>' is allowed inside a stored routine when executed via the dynamic-SQL path

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      USE '<db>' is allowed inside a stored routine when executed via the dynamic-SQL path.
      MTR Test

      CREATE DATABASE db_one;
      DELIMITER $$
      CREATE FUNCTION f() RETURNS INT BEGIN EXECUTE IMMEDIATE 'USE db_one'; RETURN 1; END$$
      CREATE PROCEDURE p() BEGIN DECLARE v INT DEFAULT f(); END$$
      DELIMITER ;
      SELECT DATABASE() AS db_before;  
      CALL p();
      SELECT DATABASE() AS db_after; 
      

      CLI Output

      13.1.0-opt>CREATE DATABASE db_one;
      Query OK, 1 row affected (0.001 sec)
       
      13.1.0-opt>DELIMITER $$
      13.1.0-opt>CREATE FUNCTION f() RETURNS INT BEGIN EXECUTE IMMEDIATE 'USE db_one'; RETURN 1; END$$
      Query OK, 0 rows affected (0.003 sec)
       
      13.1.0-opt>CREATE PROCEDURE p() BEGIN DECLARE v INT DEFAULT f(); END$$
      Query OK, 0 rows affected (0.004 sec)
       
      13.1.0-opt>DELIMITER ;
      13.1.0-opt>SELECT DATABASE() AS db_before;
      +-----------+
      | db_before |
      +-----------+
      | test      |
      +-----------+
      1 row in set (0.000 sec)
       
      13.1.0-opt>CALL p();
      Query OK, 0 rows affected (0.001 sec)
       
      13.1.0-opt>SELECT DATABASE() AS db_after;
      +----------+
      | db_after |
      +----------+
      | db_one   |
      +----------+
      1 row in set (0.000 sec)
       
      13.1.0-opt>
       
      Expected Error
      -----------------
      13.1.0-opt>CREATE FUNCTION f() RETURNS INT BEGIN USE db_one; RETURN 1; END$$
      ERROR 1314 (0A000): USE is not allowed in stored procedures
      13.1.0-opt>
      
      

      Attachments

        Activity

          People

            bar Alexander Barkov
            ramesh Ramesh Sivaraman
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - 3.7h Original Estimate - 3.7h
                3.7h
                Remaining:
                Time Spent - 1.5h Remaining Estimate - 3.7h
                3.7h
                Logged:
                Time Spent - 1.5h Remaining Estimate - 3.7h
                1.5h

                Git Integration

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