[MDEV-26606] ROW_NUMBER property value isn't passed from inside a stored procedure Created: 2021-09-14  Updated: 2021-10-06  Resolved: 2021-10-06

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.7.0
Fix Version/s: 10.7.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Rucha Deodhar
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-10075 Provide index of error causing error ... Closed
Relates
relates to MDEV-26681 ROW_NUMBER is not available within co... Closed

 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)



 Comments   
Comment by Rucha Deodhar [ 2021-09-17 ]

Not repeatable anymore after: d552e092c9f3e20da078d1b62b976f629f73d3a4

Comment by Elena Stepanova [ 2021-09-24 ]

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.

Comment by Rucha Deodhar [ 2021-09-25 ]

Patch: https://github.com/MariaDB/server/commit/4156071ebe24fce28bfe8709923b72288928674e

Comment by Sergei Golubchik [ 2021-10-06 ]

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)

Generated at Thu Feb 08 09:46:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.