[MDEV-17387] MariaDB Server giving wrong error while executing select query from procedure - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.8
Fix Version/s: 10.3.11
Component/s: Data Definition - Procedure
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

Attachments

Activity

Ascending order - Click to sort in descending order

Nilnandan Joshi created issue - 2018-10-08 02:26

Nilnandan Joshi made changes - 2018-10-08 06:47

Field	Original Value	New Value
Description	While running procedure from package, it's giving an exception {code} 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]> {code} Where even there is no record in the table which is accessed from the procedure. Please find below test case. {code} 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); {code}	While running procedure from package, it's giving an exception {code} 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]> {code} Where even there is no record in the table which is accessed from the procedure. Please find below test case. {code} 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); {code} Affected Versions: MariaDB 10.2.4 build (21251) and MariaDB 10.3

Alice Sherepa made changes - 2018-10-08 08:29

Description

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

{code}
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]>
{code}

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

{code}
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);
{code}

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

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

{code}
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]>
{code}

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

{code}
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);
{code}

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

Elena Stepanova made changes - 2018-10-08 11:13

Labels

need_feedback

Alice Sherepa added a comment - 2018-10-08 12:53 - 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 Sherepa added a comment - 2018-10-08 12:53 - 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 Sherepa made changes - 2018-10-08 12:54

Assignee

Alexander Barkov [ bar ]

Alice Sherepa made changes - 2018-10-08 12:54

Labels

need_feedback

Elena Stepanova made changes - 2018-10-08 13:02

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2018-10-08 13:02

Fix Version/s

10.3 [ 22126 ]

Elena Stepanova made changes - 2018-10-08 13:02

Affects Version/s

10.2.14 [ 22911 ]

Elena Stepanova made changes - 2018-10-08 13:03

Environment

compatibility 10.2.14 build 21251

Alexander Barkov added a comment - 2018-11-08 12:27

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.

Alexander Barkov added a comment - 2018-11-08 12:27 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.

Alexander Barkov added a comment - 2018-11-08 12:29

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

Alexander Barkov added a comment - 2018-11-08 12:29 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();

Alexander Barkov made changes - 2018-11-09 06:00

issue.field.resolutiondate

2018-11-09 06:00:50.0

2018-11-09 06:00:50.006

Alexander Barkov made changes - 2018-11-09 06:00

Fix Version/s		10.3.11 [ 23141 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 89943 ]

MariaDB v4 [ 155027 ]

Jira Automation (IT) made changes - 2024-07-04 07:22

Zendesk Related Tickets

119025

People

Assignee:: Alexander Barkov

Reporter:: Nilnandan Joshi

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-10-08 02:26

Updated:: 2024-07-07 23:38

Resolved:: 2018-11-09 06:00

Git Integration

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