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

MariaDB Server giving wrong error while executing select query from procedure

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

          alice Alice Sherepa added a comment - - edited

          set sql_mode='Oracle';
           
          CREATE or replace TABLE t1 (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) , C2 varchar(30) , TIME datetime , AMT decimal(12,2) , DT datetime NOT NULL, pk int(11) NOT NULL, KEY Indx1 (JOBN));
           
          DELIMITER $$
           CREATE OR REPLACE PACKAGE xyz IS 
              PROCEDURE p1(ctr IN VARCHAR2, Jn IN VARCHAR2, R OUT VARCHAR2);
          END;
          $$
           
          CREATE OR REPLACE PACKAGE BODY xyz IS
              PROCEDURE p1(ctr IN VARCHAR2, Jn IN VARCHAR2, R OUT VARCHAR2) AS        
                  lS NUMBER(10) :=0;
                  CURSOR c IS SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT FROM t1 WHERE JOBN=Jn;
              BEGIN
                  FOR lbpd IN c
                  LOOP lS:=lS+1; 
                  END LOOP; 
              EXCEPTION WHEN OTHERS THEN BEGIN SELECT SQLERRM; END;
              END;
              END $$
          DELIMITER ;
           
          call xyz.p1(17,18,@R);
          

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

          alice Alice Sherepa added a comment - - edited set sql_mode= 'Oracle' ;   CREATE or replace TABLE t1 (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) , C2 varchar (30) , TIME datetime , AMT decimal (12,2) , DT datetime NOT NULL , pk int (11) NOT NULL , KEY Indx1 (JOBN));   DELIMITER $$ CREATE OR REPLACE PACKAGE xyz IS PROCEDURE p1(ctr IN VARCHAR2, Jn IN VARCHAR2, R OUT VARCHAR2); END ; $$   CREATE OR REPLACE PACKAGE BODY xyz IS PROCEDURE p1(ctr IN VARCHAR2, Jn IN VARCHAR2, R OUT VARCHAR2) AS lS NUMBER(10) :=0; CURSOR c IS SELECT CTR, COR, DATE , CHAN, CNO, C1, C2, TIME , AMT FROM t1 WHERE JOBN=Jn; BEGIN FOR lbpd IN c LOOP lS:=lS+1; END LOOP; EXCEPTION WHEN OTHERS THEN BEGIN SELECT SQLERRM; END ; END ; END $$ DELIMITER ; call xyz.p1(17,18,@R); MariaDB [test]> call xyz.p1(17,18,@R); +------------------------------------------------------------------------------------------------------------------------------------------------+ | SQLERRM | +------------------------------------------------------------------------------------------------------------------------------------------------+ | Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete | +------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

          A smaller script demonstrating the same problem:

          SET sql_mode=ORACLE;
           
          CREATE OR REPLACE TABLE t1 (
            JOBN varchar(18) NOT NULL,
            pk int(11) NOT NULL,
            PRIMARY KEY (pk),
            KEY (JOBN));
           
          INSERT INTO t1 VALUES();
           
           
          DELIMITER $$
          CREATE OR REPLACE PACKAGE xyz is
            PROCEDURE p1;
          end;
          $$
          DELIMITER ;
           
          DELIMITER $$
          CREATE OR REPLACE PACKAGE BODY xyz
          IS
            PROCEDURE p1
            AS
              lS NUMBER(10) :=0;
              CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x';
              -- CURSOR cBPD IS SELECT * FROM t1 IGNORE KEY(JOBN) WHERE JOBN='x';
            BEGIN
              NULL; 
              FOR lbpd IN cBPD LOOP
                lS:=lS+1;
              END LOOP;
            EXCEPTION
              WHEN OTHERS THEN
                BEGIN
                  SELECT SQLERRM;
                END;
            END;
          END $$
          DELIMITER ;
           
          CALL xyz.p1();
          

          SQLERRM
          Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete
          

          Note, if I comment the cursor declaration and uncomment an alternative cursor declaration (with IGNORE INDEX clause added), the warning goes away.

          bar Alexander Barkov added a comment - A smaller script demonstrating the same problem: SET sql_mode=ORACLE;   CREATE OR REPLACE TABLE t1 ( JOBN varchar (18) NOT NULL , pk int (11) NOT NULL , PRIMARY KEY (pk), KEY (JOBN));   INSERT INTO t1 VALUES ();     DELIMITER $$ CREATE OR REPLACE PACKAGE xyz is PROCEDURE p1; end ; $$ DELIMITER ;   DELIMITER $$ CREATE OR REPLACE PACKAGE BODY xyz IS PROCEDURE p1 AS lS NUMBER(10) :=0; CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN= 'x' ; -- CURSOR cBPD IS SELECT * FROM t1 IGNORE KEY(JOBN) WHERE JOBN='x'; BEGIN NULL ; FOR lbpd IN cBPD LOOP lS:=lS+1; END LOOP; EXCEPTION WHEN OTHERS THEN BEGIN SELECT SQLERRM; END ; END ; END $$ DELIMITER ;   CALL xyz.p1(); SQLERRM Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete Note, if I comment the cursor declaration and uncomment an alternative cursor declaration (with IGNORE INDEX clause added), the warning goes away.

          The same problem is repeatable with a standalone procedure:

          SET sql_mode=ORACLE;
           
          CREATE OR REPLACE TABLE t1 (
            JOBN varchar(18) NOT NULL,
            pk int(11) NOT NULL,
            PRIMARY KEY (pk),
            KEY (JOBN));
           
           
          DELIMITER $$
          CREATE OR REPLACE PROCEDURE p1
          AS
            lS NUMBER(10) :=0;
            CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x';
            -- CURSOR cBPD IS SELECT * FROM t1 IGNORE KEY(JOBN) WHERE JOBN='x';
          BEGIN
            FOR lbpd IN cBPD LOOP
              lS:=lS+1;
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
              BEGIN
                SELECT SQLERRM;
              END;
          END;
          $$
          DELIMITER ;
           
          CALL p1();
          

          bar Alexander Barkov added a comment - The same problem is repeatable with a standalone procedure: SET sql_mode=ORACLE;   CREATE OR REPLACE TABLE t1 ( JOBN varchar (18) NOT NULL , pk int (11) NOT NULL , PRIMARY KEY (pk), KEY (JOBN));     DELIMITER $$ CREATE OR REPLACE PROCEDURE p1 AS lS NUMBER(10) :=0; CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN= 'x' ; -- CURSOR cBPD IS SELECT * FROM t1 IGNORE KEY(JOBN) WHERE JOBN='x'; BEGIN FOR lbpd IN cBPD LOOP lS:=lS+1; END LOOP; EXCEPTION WHEN OTHERS THEN BEGIN SELECT SQLERRM; END ; END ; $$ DELIMITER ;   CALL p1();

          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.