[MDEV-17387] MariaDB Server giving wrong error while executing select query from procedure Created: 2018-10-08  Updated: 2020-08-25  Resolved: 2018-11-09

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Procedure
Affects Version/s: 10.3.8
Fix Version/s: 10.3.11

Type: Bug Priority: Major
Reporter: Nilnandan Joshi Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: None
Environment:

compatibility 10.2.14 build 21251


 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

 Comments   
Comment by Alice Sherepa [ 2018-10-08 ] 

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)

Comment by Alexander Barkov [ 2018-11-08 ]

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.

Comment by Alexander Barkov [ 2018-11-08 ]

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();

Generated at Thu Feb 08 08:36:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.