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

ROW_NUMBER property value isn't passed from inside a stored procedure

    XMLWordPrintable

Details

    Description

      ERROR_INDEX value implemented in the scope of MDEV-10075 isn't passed outside the stored procedure, either as a part of an uncaught condition, or via resignal.

      Simple form

      		 
      create or replace procedure sp(a int) insert into t1 values (a);
      		 
      create or replace table t1 (pk int primary key);
      		 
      set @num=null, @msg=null;
      		 
      call sp(1);
      		 
      call sp(1);
      		 
      get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg;

      preview-10.7-MDEV-10075-insert-error-index 755475798d

      		 
      MariaDB [test]> call sp(1);
      		 
      ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
      		 
      MariaDB [test]> get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg;
      		 
      Query OK, 0 rows affected (0.000 sec)
      		 
       
      		 
      +------+---------------------------------------+
      		 
      | @num | @msg                                  |
      		 
      +------+---------------------------------------+
      		 
      |    0 | Duplicate entry '1' for key 'PRIMARY' |
      		 
      +------+---------------------------------------+
      		 
      1 row in set (0.000 sec)

      In the test cases below, remember to replace delimiter with the MTR syntax if you run it via MTR

      Resignal form

      		 
      delimiter $
      		 
      create or replace procedure sp(a int)
      		 
      begin
      		 
        declare n int;
      		 
        declare m varchar(255);
      		 
        declare exit handler for 1062
      		 
        begin
      		 
          get diagnostics condition 1 n = ERROR_INDEX, m = MESSAGE_TEXT;
      		 
          select n, m;
      		 
          resignal;
      		 
        end;
      		 
        insert into t1 values (a);
      		 
      end $
      		 
      delimiter ;
      		 
       
      		 
      create or replace table t1 (pk int primary key);
      		 
      set @num=null, @msg=null;
      		 
      call sp(1);
      		 
      call sp(1);
      		 
      get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg;


      MariaDB [test]> call sp(1);
      		 
      +------+---------------------------------------+
      		 
      | n    | m                                     |
      		 
      +------+---------------------------------------+
      		 
      |    1 | Duplicate entry '1' for key 'PRIMARY' |
      		 
      +------+---------------------------------------+
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
      		 
      MariaDB [test]> get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg;
      		 
      Query OK, 0 rows affected (0.000 sec)
      		 
       
      		 
      +------+---------------------------------------+
      		 
      | @num | @msg                                  |
      		 
      +------+---------------------------------------+
      		 
      |    0 | Duplicate entry '1' for key 'PRIMARY' |
      		 
      +------+---------------------------------------+
      		 
      1 row in set (0.000 sec)

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-10075 Provide index of error causing error in array INSERT

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26681 ROW_NUMBER is not available within compound statement blocks

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

          Activity

            People

              rucha174 Rucha Deodhar
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.