[MDEV-12007] Allow ROW variables as a cursor FETCH target - Jira

Details

Type: Technical task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.3.0
Component/s: Parser, Stored routines
Labels:
- Compatibility

Sprint:
10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

Description

We'll allow ROW type variables as FETCH targets:

FETCH cur INTO rec;

where cur is a CURSOR and rec is a ROW type SP variable.
Note, currently an attempt to use FETCH for a ROW type variable returns this error:

ERROR 1328 (HY000): Incorrect number of FETCH variables

FETCH from a cursor cur into a ROW variable rec will work as follows:

The number of fields in cur must match the number of fields in rec. Otherwise, an error is reported.
Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
Field names in rec are not important and can differ from field names in cur.

A complete example for sql_mode=ORACLE:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT, b VARCHAR(32));

INSERT INTO t1 VALUES (10,'b10');

INSERT INTO t1 VALUES (20,'b20');

INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1 AS

  rec ROW(a INT, b VARCHAR(32));

  CURSOR c IS SELECT a,b FROM t1;

BEGIN

  OPEN c;

  LOOP

    FETCH c INTO rec;

    EXIT WHEN c%NOTFOUND;

    SELECT ('rec=(' || rec.a ||','|| rec.b||')');

  END LOOP;

  CLOSE c;

END;

$$

DELIMITER ;

CALL p1();

A complete example for sql_mode=DEFAULT:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT, b VARCHAR(32));

INSERT INTO t1 VALUES (10,'b10');

INSERT INTO t1 VALUES (20,'b20');

INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE rec ROW(a INT, b VARCHAR(32));

  DECLARE c CURSOR FOR SELECT a,b FROM t1;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN c;

read_loop:

  LOOP

    FETCH c INTO rec;

    IF done THEN

      LEAVE read_loop;

    END IF;

    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');

  END LOOP;

  CLOSE c;

END;

$$

DELIMITER ;

CALL p1();

Attachments

Issue Links

blocks

MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP

Closed

MDEV-12461 TYPE OF and ROW TYPE OF anchored data types for stored routine variables

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-02-07 04:55

Alexander Barkov made changes - 2017-02-07 04:55

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-10581~~ [ ~~MDEV-10581~~ ]

Alexander Barkov made changes - 2017-02-07 04:57

Description

We'll allow {{ROW}} type variables as {{FETCH}} targets:

{code:sql}
FETCH cur INTO rec;
{code}
where {{cur}} is a {{CURSOR}} and {{rec}} is a {{ROW}} type SP variable.

A complete example for {{sql_mode=ORACLE}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

A complete example for {{sql_mode=DEFAULT}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rec ROW(a INT, b VARCHAR(32));
  DECLARE c CURSOR FOR SELECT a,b FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;
read_loop:
  LOOP
    FETCH c INTO rec;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

We'll allow {{ROW}} type variables as {{FETCH}} targets:

{code:sql}
FETCH cur INTO rec;
{code}
where {{cur}} is a {{CURSOR}} and {{rec}} is a {{ROW}} type SP variable.
Note, currently an attempt to use {{FETCH}} for a {{ROW}} type variable returns this error:
{noformat}
ERROR 1328 (HY000): Incorrect number of FETCH variables
{noformat}

A complete example for {{sql_mode=ORACLE}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

A complete example for {{sql_mode=DEFAULT}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rec ROW(a INT, b VARCHAR(32));
  DECLARE c CURSOR FOR SELECT a,b FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;
read_loop:
  LOOP
    FETCH c INTO rec;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

Alexander Barkov made changes - 2017-02-07 05:00

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2017-02-07 05:20

Description

We'll allow {{ROW}} type variables as {{FETCH}} targets:

{code:sql}
FETCH cur INTO rec;
{code}
where {{cur}} is a {{CURSOR}} and {{rec}} is a {{ROW}} type SP variable.
Note, currently an attempt to use {{FETCH}} for a {{ROW}} type variable returns this error:
{noformat}
ERROR 1328 (HY000): Incorrect number of FETCH variables
{noformat}

{{FETCH}} from a cursor {{cur}} into a {{ROW}} variable {{rec}} will work as follows:
- The number of fields in {{cur}} must match the number of fields in {{rec}}. Otherwise, an error is reported.
- Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
- Field names in {{rec} are not important and may not match field names in {{cur}}.

A complete example for {{sql_mode=ORACLE}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

A complete example for {{sql_mode=DEFAULT}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rec ROW(a INT, b VARCHAR(32));
  DECLARE c CURSOR FOR SELECT a,b FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;
read_loop:
  LOOP
    FETCH c INTO rec;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

Alexander Barkov made changes - 2017-02-07 05:20

Description

We'll allow {{ROW}} type variables as {{FETCH}} targets:

{code:sql}
FETCH cur INTO rec;
{code}
where {{cur}} is a {{CURSOR}} and {{rec}} is a {{ROW}} type SP variable.
Note, currently an attempt to use {{FETCH}} for a {{ROW}} type variable returns this error:
{noformat}
ERROR 1328 (HY000): Incorrect number of FETCH variables
{noformat}

{{FETCH}} from a cursor {{cur}} into a {{ROW}} variable {{rec}} will work as follows:
- The number of fields in {{cur}} must match the number of fields in {{rec}}. Otherwise, an error is reported.
- Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
- Field names in {{rec} are not important and may not match field names in {{cur}}.

A complete example for {{sql_mode=ORACLE}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

A complete example for {{sql_mode=DEFAULT}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rec ROW(a INT, b VARCHAR(32));
  DECLARE c CURSOR FOR SELECT a,b FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;
read_loop:
  LOOP
    FETCH c INTO rec;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

We'll allow {{ROW}} type variables as {{FETCH}} targets:

{code:sql}
FETCH cur INTO rec;
{code}
where {{cur}} is a {{CURSOR}} and {{rec}} is a {{ROW}} type SP variable.
Note, currently an attempt to use {{FETCH}} for a {{ROW}} type variable returns this error:
{noformat}
ERROR 1328 (HY000): Incorrect number of FETCH variables
{noformat}

{{FETCH}} from a cursor {{cur}} into a {{ROW}} variable {{rec}} will work as follows:
- The number of fields in {{cur}} must match the number of fields in {{rec}}. Otherwise, an error is reported.
- Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
- Field names in {{rec}} are not important and may not match field names in {{cur}}.

A complete example for {{sql_mode=ORACLE}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

A complete example for {{sql_mode=DEFAULT}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rec ROW(a INT, b VARCHAR(32));
  DECLARE c CURSOR FOR SELECT a,b FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;
read_loop:
  LOOP
    FETCH c INTO rec;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

Alexander Barkov made changes - 2017-02-07 05:21

Description

We'll allow {{ROW}} type variables as {{FETCH}} targets:

{code:sql}
FETCH cur INTO rec;
{code}
where {{cur}} is a {{CURSOR}} and {{rec}} is a {{ROW}} type SP variable.
Note, currently an attempt to use {{FETCH}} for a {{ROW}} type variable returns this error:
{noformat}
ERROR 1328 (HY000): Incorrect number of FETCH variables
{noformat}

{{FETCH}} from a cursor {{cur}} into a {{ROW}} variable {{rec}} will work as follows:
- The number of fields in {{cur}} must match the number of fields in {{rec}}. Otherwise, an error is reported.
- Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
- Field names in {{rec}} are not important and may not match field names in {{cur}}.

A complete example for {{sql_mode=ORACLE}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

A complete example for {{sql_mode=DEFAULT}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rec ROW(a INT, b VARCHAR(32));
  DECLARE c CURSOR FOR SELECT a,b FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;
read_loop:
  LOOP
    FETCH c INTO rec;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

We'll allow {{ROW}} type variables as {{FETCH}} targets:

{code:sql}
FETCH cur INTO rec;
{code}
where {{cur}} is a {{CURSOR}} and {{rec}} is a {{ROW}} type SP variable.
Note, currently an attempt to use {{FETCH}} for a {{ROW}} type variable returns this error:
{noformat}
ERROR 1328 (HY000): Incorrect number of FETCH variables
{noformat}

{{FETCH}} from a cursor {{cur}} into a {{ROW}} variable {{rec}} will work as follows:
- The number of fields in {{cur}} must match the number of fields in {{rec}}. Otherwise, an error is reported.
- Assignment is done from left to right. The first cursor field is assigned to the first variable field, the second cursor field is assigned to the second variable field, etc.
- Field names in {{rec}} are not important and can differ from field names in {{cur}}.

A complete example for {{sql_mode=ORACLE}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=oracle;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

A complete example for {{sql_mode=DEFAULT}}:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');

SET sql_mode=DEFAULT;
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE rec ROW(a INT, b VARCHAR(32));
  DECLARE c CURSOR FOR SELECT a,b FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN c;
read_loop:
  LOOP
    FETCH c INTO rec;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT CONCAT('rec=(',rec.a,',',rec.b,')');
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}

Alexander Barkov made changes - 2017-02-07 09:33

issue.field.resolutiondate

2017-02-07 09:33:56.0

2017-02-07 09:33:56.102

Alexander Barkov made changes - 2017-02-07 09:33

Fix Version/s		10.3.0 [ 22127 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Alexander Barkov made changes - 2017-03-10 11:57

Labels

Compatibility need_review

Alexander Barkov made changes - 2017-04-07 09:44

Link

This issue blocks ~~MDEV-12291~~ [ ~~MDEV-12291~~ ]

Alexander Barkov made changes - 2017-04-07 09:46

Link

This issue blocks ~~MDEV-12291~~ [ ~~MDEV-12291~~ ]

Alexander Barkov made changes - 2017-04-07 09:47

Link

This issue blocks ~~MDEV-12461~~ [ ~~MDEV-12461~~ ]

Alexander Barkov made changes - 2017-04-07 09:48

Summary

Allow ROW variables as a cursor FETCH target

sql_mode=ORACLE: Allow ROW variables as a cursor FETCH target

Alexander Barkov made changes - 2017-04-07 10:31

Summary

sql_mode=ORACLE: Allow ROW variables as a cursor FETCH target

Allow ROW variables as a cursor FETCH target

Alexander Barkov made changes - 2017-04-09 02:55

Component/s

Parser [ 10201 ]

Sergei Golubchik made changes - 2020-08-27 07:12

Labels

Compatibility need_review

Compatibility

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

Workflow

MariaDB v3 [ 79536 ]

MariaDB v4 [ 151673 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-02-07 04:55

Updated:: 2020-08-27 07:12

Resolved:: 2017-02-07 09:33

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration