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

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

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

            rucha174 Rucha Deodhar added a comment -

            Not repeatable anymore after: d552e092c9f3e20da078d1b62b976f629f73d3a4

            rucha174 Rucha Deodhar added a comment - Not repeatable anymore after: d552e092c9f3e20da078d1b62b976f629f73d3a4

            It isn't reproducible only because now the default value of ERROR_INDEX is 1, which happens to coincide with the expected value in the test cases from the description (you created another JIRA item to get rid of this confusion). But the complaint remains the same, the value isn't passed from inside a procedure. Try this, for example:

            delimiter $
            create or replace procedure sp(a int, b 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),(b);
            end $
            delimiter ;
             
            create or replace table t1 (pk int primary key);
            set @num=null, @msg=null;
            call sp(1,2);
            --error ER_DUP_ENTRY
            call sp(3,1);
            get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg;
            

            10.7 d552e092c9f - Result

            MariaDB [test]> call sp(3,1);
            +------+---------------------------------------+
            | n    | m                                     |
            +------+---------------------------------------+
            |    2 | Duplicate entry '1' for key 'PRIMARY' |
            +------+---------------------------------------+
            1 row in set (0.030 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                                  |
            +------+---------------------------------------+
            |    1 | Duplicate entry '1' for key 'PRIMARY' |
            +------+---------------------------------------+
            1 row in set (0.000 sec)
            

            The first output is from inside the procedure, and it shows the correct ERROR_INDEX value 2. But outside, it is 1.

            elenst Elena Stepanova added a comment - It isn't reproducible only because now the default value of ERROR_INDEX is 1, which happens to coincide with the expected value in the test cases from the description (you created another JIRA item to get rid of this confusion). But the complaint remains the same, the value isn't passed from inside a procedure. Try this, for example: delimiter $ create or replace procedure sp(a int , b 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),(b); end $ delimiter ; create or replace table t1 (pk int primary key ); set @num= null , @msg= null ; call sp(1,2); --error ER_DUP_ENTRY call sp(3,1); get diagnostics condition 1 @num = ERROR_INDEX, @msg = MESSAGE_TEXT; select @num, @msg; 10.7 d552e092c9f - Result MariaDB [test]> call sp(3,1); + ------+---------------------------------------+ | n | m | + ------+---------------------------------------+ | 2 | Duplicate entry '1' for key 'PRIMARY' | + ------+---------------------------------------+ 1 row in set (0.030 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 | + ------+---------------------------------------+ | 1 | Duplicate entry '1' for key 'PRIMARY' | + ------+---------------------------------------+ 1 row in set (0.000 sec) The first output is from inside the procedure, and it shows the correct ERROR_INDEX value 2 . But outside, it is 1 .
            rucha174 Rucha Deodhar added a comment - - edited Patch: https://github.com/MariaDB/server/commit/4156071ebe24fce28bfe8709923b72288928674e
            serg Sergei Golubchik added a comment - - edited

            4156071ebe24 is ok to push.

            Just fix it to use ROW_NUMBER, and don't forget to do it in the commit comment too (to match the new MDEV title)

            serg Sergei Golubchik added a comment - - edited 4156071ebe24 is ok to push. Just fix it to use ROW_NUMBER, and don't forget to do it in the commit comment too (to match the new MDEV title)

            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.