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

ROW_NUMBER is not available within compound statement blocks

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

          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-26606 ROW_NUMBER property value isn't passed from inside a stored procedure

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

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            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_

            {code:sql}
            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;
            {code}

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

            {code:sql|title=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)
            {code}

            {code:sql|title=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)
            {code}

            So, other diagnostics are equally available within the block and outside, but ERROR_INDEX is not.             		_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_

            {code:sql}
            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;
            {code}

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

            {code:sql|title=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)
            {code}

            {code:sql|title=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)
            {code}

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

            Same applies to traditional stored procedures, naturally.
            rucha174 Rucha Deodhar made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            rucha174 Rucha Deodhar added a comment - - edited

            Patch (only includes test case):
            https://github.com/MariaDB/server/commit/882fcade515a1a381c27a1bbee056f4223530428
            https://github.com/MariaDB/server/commit/5a99eb4878b6b3bb00bef0cc552f707d32b91574

            Fixed after MDEV-26606 . Same root cause

            rucha174 Rucha Deodhar added a comment - - edited Patch (only includes test case): https://github.com/MariaDB/server/commit/882fcade515a1a381c27a1bbee056f4223530428 https://github.com/MariaDB/server/commit/5a99eb4878b6b3bb00bef0cc552f707d32b91574 Fixed after MDEV-26606 . Same root cause
            rucha174 Rucha Deodhar made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            rucha174 Rucha Deodhar made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            rucha174 Rucha Deodhar made changes -
            Assignee Rucha Deodhar [ rucha174 ] Sergei Golubchik [ serg ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik added a comment -

            this is ok

            serg Sergei Golubchik added a comment - this is ok
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Rucha Deodhar [ rucha174 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            elenst Elena Stepanova made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            rucha174 Rucha Deodhar made changes -
            Summary ERROR_INDEX is not available within compound statement blocks ROW_NUMBER is not available within compound statement blocks
            rucha174 Rucha Deodhar made changes -
            Fix Version/s 10.7.1 [ 26120 ]
            Fix Version/s 10.7 [ 24805 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 125519 ] MariaDB v4 [ 159711 ]

            People

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