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

Inconsistent behavior of ROW_NUMBER upon resignalling from function

Details

    • Bug
    • Status: In Review (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.7(EOL)
    • 10.11
    • Stored routines
    • None

    Description

      Notes:

      • The test case uses explicit HANDLER / RESIGNAL for more obvious demonstration, but it is really the same without any handler, when the error remains uncaught and gets passed over to the upper level.
      • If you run the test case via MTR, remember to adjust delimiters.

      create or replace function f(a int) returns int return a;
      delimiter $
      create or replace function f2() returns int
      begin
        declare i int default -1;
        declare exit handler for 1318
        begin
          get diagnostics condition 1 @row_number_in_f2= row_number, @msg_in_f2= message_text;
          resignal;
          return -2;
        end;
        select f() into i;
        return i;
      end $
      delimiter ;
       
      --error ER_SP_WRONG_NO_OF_ARGS
      select f2();
       
      get diagnostics condition 1 @row_number_outside= row_number, @msg_outside= message_text;
      select @row_number_in_f2, @msg_in_f2;
      select @row_number_outside, @msg_outside;
       
      # Cleanup
      drop function f2;
      drop function f;
      

      bb-10.7-row_number cb9002be

      MariaDB [test]> select @row_number_in_f2, @msg_in_f2;
      +-------------------+----------------------------------------------------------------------+
      | @row_number_in_f2 | @msg_in_f2                                                           |
      +-------------------+----------------------------------------------------------------------+
      |                 1 | Incorrect number of arguments for FUNCTION test.f; expected 1, got 0 |
      +-------------------+----------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select @row_number_outside, @msg_outside;
      +---------------------+----------------------------------------------------------------------+
      | @row_number_outside | @msg_outside                                                         |
      +---------------------+----------------------------------------------------------------------+
      |                   0 | Incorrect number of arguments for FUNCTION test.f; expected 1, got 0 |
      +---------------------+----------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      So, while the error message is passed over to the caller, the ROW_NUMBER isn't.
      There must be plenty other examples like this.

      Attachments

        Issue Links

          Activity

            rucha174 Rucha Deodhar added a comment - - edited Patch: https://github.com/MariaDB/server/commit/5e04903f592bb425d52f9fa18388e1e64b67497f

            It's questionable, whether row_number should be preserved. Consider, say

            create table t1 (a int primary key)$
            create function f(i int) returns int
            begin
              insert t1 values (i),(i+1),(i+2);
              return i+3;
            end $
            create table t2 (b int)$
            insert t2 values (f(1)), (f(10)), (f(9))$
            

            (this is for demonstrative purposes, I didn't run it). the error will happen on the third row of t2, but on the second row of t1. Also, the invoker may or may not be able to see the body of f and may or may not be able to see that t1 exists.

            serg Sergei Golubchik added a comment - It's questionable, whether row_number should be preserved. Consider, say create table t1 (a int primary key )$ create function f(i int ) returns int begin insert t1 values (i),(i+1),(i+2); return i+3; end $ create table t2 (b int )$ insert t2 values (f(1)), (f(10)), (f(9))$ (this is for demonstrative purposes, I didn't run it). the error will happen on the third row of t2 , but on the second row of t1 . Also, the invoker may or may not be able to see the body of f and may or may not be able to see that t1 exists.

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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