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

Allow prepared statements in stored functions in assignment right hand

    XMLWordPrintable

Details

    Description

      Currently prepared statements are not allowed in stored functions. Prepared statements are caught on the parser level and an error is raised:

      ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

      SP variable assignment is a special case where prepared statements can be safely allowed.

      SET spvar= stored_function_with_prepared_statements();

      The above call is equivalent to a stored procedure call with an OUT parameter:

      CALL stored_procedure_with_prepared_statements(spvar);

      So allowing prepared statements in functions which are used on the right hand of the assignment operator should be safe.

      After this change the following example will work:

      DELIMITER $$
      		 
      CREATE OR REPLACE FUNCTION f1() RETURNS INT DETERMINISTIC
      		 
      BEGIN
      		 
        DECLARE c0 SYS_REFCURSOR;
      		 
        DECLARE v0 INT;
      		 
        PREPARE stmt FROM 'SELECT 10';
      		 
        OPEN c0 FOR PREPARE stmt;
      		 
        FETCH c0 INTO v0;
      		 
        CLOSE c0;
      		 
        DEALLOCATE PREPARE stmt;
      		 
        RETURN v0;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
       
      		 
      DELIMITER $$
      		 
      CREATE OR REPLACE PROCEDURE p1()
      		 
      BEGIN
      		 
        DECLARE n INT;
      		 
        SET n= f1();
      		 
        SELECT n;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
       
      		 
      CALL p1;


      +------+
      		 
      | n    |
      		 
      +------+
      		 
      |   10 |
      		 
      +------+

      Limitations

      • Queries which cause explicit or implicit commit (e.g. DDL) in the query won't work in functions

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40224 SIGSEGV in THD::is_error | Item::save_in_field | Item_trigger_field::set_value

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40225 Assertion `!thd->in_sub_stmt && !(thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' failed in int Locked_tables_list::unlock_locked_tables(THD *)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40226 Assertion `inited == NONE || table->open_by_handler' failed in int handler::ha_external_lock(THD *, int)

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40227 Replication breaks when a function performs multiple INSERT statements with binlog_format=MIXED or STATEMENT

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40240 Dynamic SQL must be rejected with ERROR 1336 when using non-safe context

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40285 Assertion DBUG_ASSERT(thd->lock == 0) failed in lock_tables()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40288 PACKAGE BODY initialization section that contains dynamic SQL fails with ER_NO_SUCH_TABLE for a table that exists

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40315 Executing dynamic SQL (via PS)  bypasses ERROR ER_SP_NO_RETSET

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40318 MIXED binlog format does not switch an unsafe-for-SBR dynamic SQL (via PS) to ROW logging, causing replica divergence

          • Critical - Crashes, loss of data, severe memory leak.
          • Open
          is blocked by

          Task - A task that needs to be done. MDEV-39022 Add `LOCAL spvar` syntax for prepared statements and SYS_REFCURSORs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-39748 Package function calls are not replicated well in statement and mixed modes

          • Critical - Crashes, loss of data, severe memory leak.
          • Open
          relates to

          Technical task - A technical task. MDEV-12034 Dynamic SQL in stored functions

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-40322 HANDLER OPEN not rejected in stored routine when executed via PS

          • Major - Major loss of function.
          • Open
          has action item

          DOCS-6275 Loading...

          Activity

            People

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

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 7d Original Estimate - 7d
                  7d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 13d 2.5h
                  13d 2.5h

                  Git Integration

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