[MDEV-26681] ROW_NUMBER is not available within compound statement blocks - Jira

XML

Word

Printable

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.

is caused by

MDEV-10075 Provide index of error causing error in array INSERT

relates to

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

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.