[MDEV-15941] Explicit cursor FOR loop does not close the cursor Created: 2018-04-20  Updated: 2018-06-20  Resolved: 2018-06-20

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.3
Fix Version/s: 10.3.8

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description   

I run this script:

SET sql_mode=ORACLE;
 
DELIMITER $$
 
DECLARE
 
  CURSOR cur IS SELECT 1 AS a FROM DUAL;
 
  v INT;
 
BEGIN
 
  FOR rec IN cur
 
  LOOP
 
    NULL;
 
  END LOOP;
 
  FETCH cur INTO v;
 
  CLOSE cur;
 
END;
 
$$
 
DELIMITER ;

It ends successfully. This is wrong. The FOR loop for an explicit cursor should close the cursor. In fact it does not seem to, so FETCH and CLOSE are still possible with this cursor after the loop.

In Oracle, the cursor is in closed state after the loop. So attempts to use it with FETCH (or CLOSE) after the loop return an error:

DECLARE
 
  CURSOR cur IS SELECT 1 AS a FROM DUAL;
 
  v INT;
 
BEGIN
 
  FOR rec IN cur
 
  LOOP
 
    NULL;
 
  END LOOP;
 
  FETCH cur INTO v;
 
END;
 
/


ERROR at line 1:
 
ORA-01001: invalid cursor
 
ORA-06512: at line 9


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