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

MariaDB Server giving wrong error while executing select query from procedure

    XMLWordPrintable

Details

    Description

      While running procedure from package, it's giving an exception

      MariaDB [test]> call xyz.xyz123(17,18,@p);
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | SQLERRM                                                                                                                                        |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 
      

      Where even there is no record in the table which is accessed from the procedure.
      Please find below test case.

      MariaDB [test]> CREATE TABLE NIL_TEMP (
           CTR varchar(2) NOT NULL,
           COR varchar(3) NOT NULL,
           DATE datetime NOT NULL,
           CHAN varchar(4) NOT NULL,
           CNO varchar(20) NOT NULL,
           JOBN varchar(18) NOT NULL,
           C1 varchar(30) DEFAULT NULL,
           C2 varchar(30) DEFAULT NULL,
           TIME datetime DEFAULT NULL,
           AMT decimal(12,2) DEFAULT NULL,
           DT datetime NOT NULL,
           pk int(11) NOT NULL,
           PRIMARY KEY (pk),
           KEY Indx1 (JOBN)
           );
      Query OK, 0 rows affected (0.05 sec)
       
      DELIMITER $$
       
      CREATE OR REPLACE PACKAGE xyz is PROCEDURE xyz123(
      ctr IN VARCHAR2,
      Jn IN VARCHAR2,
      R OUT VARCHAR2
      ); end; $$
       
      CREATE OR REPLACE PACKAGE BODY xyz IS
       
      PROCEDURE xyz123(
      ctr IN VARCHAR2,
      Jn IN VARCHAR2,
      R OUT VARCHAR2
      )
      AS
      lS NUMBER(10) :=0;
       
      CURSOR cBPD IS
      SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT FROM
      NIL_TEMP WHERE JOBN=Jn;
       
      BEGIN
      FOR lbpd IN cBPD
      LOOP
      lS:=lS+1;
      END LOOP;
       
      EXCEPTION
      WHEN OTHERS THEN
      BEGIN
      SELECT SQLERRM;
      END;
       
      END;
       
      END $$
      DELIMITER ;
       
      call xyz.xyz123(17,18,@R);
      

      Affected Versions: MariaDB 10.2.4 build (21251) and MariaDB 10.3

      Attachments

        Activity

          People

            bar Alexander Barkov
            niljoshi Nilnandan Joshi
            Votes:
            1 Vote for this issue
            Watchers:
            5 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.