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

ROW_NUMBER is not available within compound statement blocks

    XMLWordPrintable

    Details

      Description

      This is in a way complementary to MDEV-26606, with some luck it has the same root cause, although I'm not sure about it

      create or replace table t1 (pk int primary key, a char(3));
      insert ignore into t1 values (1,'foo'),(1,'bar'),(2,'foobar');
       
      --delimiter $
      begin not atomic
        declare i int default 0;
        declare rnum int default -1;
        declare msg varchar(1024) default '';
        declare err int default -1;
        while i < @@warning_count
        do
          set i = i + 1;
          get diagnostics condition i rnum = error_index, msg = message_text, err = mysql_errno;
          select i, rnum, msg, err;
        end while;
      end $
      --delimiter ;
       
      get diagnostics condition 1 @rnum = error_index, @msg = message_text, @err = mysql_errno;
      select @rnum, @msg, @err;
      get diagnostics condition 2 @rnum = error_index, @msg = message_text, @err = mysql_errno;
      select @rnum, @msg, @err;
      

      (To run via the client, remember to change --delimiter lines).

      10.7 d552e092c9 Within the compound block

      +------+------+---------------------------------------+------+
      | i    | rnum | msg                                   | err  |
      +------+------+---------------------------------------+------+
      |    1 |    1 | Duplicate entry '1' for key 'PRIMARY' | 1062 |
      +------+------+---------------------------------------+------+
      1 row in set (0.001 sec)
       
      +------+------+----------------------------------------+------+
      | i    | rnum | msg                                    | err  |
      +------+------+----------------------------------------+------+
      |    2 |    1 | Data truncated for column 'a' at row 3 | 1265 |
      +------+------+----------------------------------------+------+
      1 row in set (0.001 sec)
      

      Outside the compound block

      MariaDB [test]> get diagnostics condition 1 @rnum = error_index, @msg = message_text, @err = mysql_errno;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> select @rnum, @msg, @err;
      +-------+---------------------------------------+------+
      | @rnum | @msg                                  | @err |
      +-------+---------------------------------------+------+
      |     2 | Duplicate entry '1' for key 'PRIMARY' | 1062 |
      +-------+---------------------------------------+------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> get diagnostics condition 2 @rnum = error_index, @msg = message_text, @err = mysql_errno;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> select @rnum, @msg, @err;
      +-------+----------------------------------------+------+
      | @rnum | @msg                                   | @err |
      +-------+----------------------------------------+------+
      |     3 | Data truncated for column 'a' at row 3 | 1265 |
      +-------+----------------------------------------+------+
      1 row in set (0.000 sec)
      

      So, other diagnostics are equally available within the block and outside, but ERROR_INDEX is not.

      Same applies to traditional stored procedures, naturally.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration