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

            Activity

              People

              Assignee:
              rucha174 Rucha Deodhar
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration