[MDEV-12133] sql_mode=ORACLE: table%ROWTYPE in variable declarations - 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

This task will implement Oracle-stype table%ROWTYPE declarations, for sql_mode=ORACLE.

Example:

rec t1%ROWTYPE;

The record variable rec can store the entire row of the data fetched from the table t1. There is no a need to specify column names and data types. They're automatically copied from t1.

Under scope of this task, we'll implement table%ROWTYPE for routine variables. Using table%ROWTYPE for routine parameters and function return values will be done separately.

A complete working example:

SET sql_mode=ORACLE;

DROP TABLE t1;

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

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

DROP PROCEDURE p1;

DELIMITER $$

CREATE PROCEDURE p1 AS

  rec t1%ROWTYPE;

  CURSOR c IS SELECT * FROM t1;

BEGIN

  OPEN c;

  LOOP

    FETCH c INTO rec;

    EXIT WHEN c%NOTFOUND;

    SELECT 'rec=(' || rec.a ||','|| rec.b||')' AS c FROM dual;

  END LOOP;

  CLOSE c;

END;

$$

DELIMITER ;

CALL p1();

rec=(10,b10)

Data types will be resolved at the very beginning of a routine execution, in sp_rcontext::create(). If the tables referenced in %ROWTYPE declarations are altered inside the routine, this will not affect structures of the referencing %ROWTYPE variables. In the below example the variable rec will have only two fields a and b, it will not have the field c. The fact that rec is declared after the ALTER statement does not matter. This implementations will be close to Oracle, who determines all data types at CREATE PROCEDURE time.

table%ROWTYPE and implicit ROW variables will be mutually assignable if they have the same number of fields. Note, Oracle has stricter rules, it also checks field names, but in a very strange way. See "Oracle implementation details". We won't check field names. Assignment will be done from left to right (the N'th source field is assigned to the N'th destination field).

In the below example all three variables rec0, rec1, rec2 will be mutually assignable, because they have the same number of fields:

SET sql_mode=ORACLE;

DROP TABLE IF EXISTS t1, t2;

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

CREATE TABLE t2 (c INT, d VARCHAR(32));

DROP PROCEDURE p1;

DELIMITER $$

CREATE PROCEDURE p1 AS

  rec0 ROW(x INT,y INT);

  rec1 t1%ROWTYPE;

  rec2 t2%ROWTYPE;

BEGIN

  rec0:=rec1;

  rec0:=rec2;

  rec1:=rec0;

  rec1:=rec2;

  rec2:=rec0;

  rec2:=rec1;

END;

$$

DELIMITER ;

CALL p1();

It will be possible to pass a table%ROWTYPE variable into a routine with a compatible explicit ROW argument:

SET sql_mode=ORACLE;

DROP TABLE IF EXISTS t1;

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

DROP PROCEDURE IF EXISTS p1;

DROP PROCEDURE IF EXISTS p2;

DELIMITER $$

CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(20)))

AS

BEGIN

  SELECT a.a, a.b;

END;

$$

CREATE PROCEDURE p2 AS

  rec1 t1%ROWTYPE:=ROW(10,'bb');

BEGIN

  CALL p1(rec1);

END;

$$

DELIMITER ;

CALL p2();

+------+------+

| a.a  | a.b  |

+------+------+

| 10   | bb   |

+------+------+

Attachments

Issue Links

blocks

MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable declarations

Closed

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

Closed

is blocked by

MDEV-10914 ROW data type for stored routine variables

Closed

relates to

MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-02-27 06:52

Alexander Barkov added a comment - 2017-02-27 06:52 - edited

Oracle implementation details

FETCH assigns fields of table%ROWTYPE variables from left to right. Field names are not important.

SET SERVEROUTPUT ON;

DROP TABLE t1;

DROP TABLE t2;

CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10));

INSERT INTO t1 VALUES ('A','B');

CREATE TABLE t2 (x VARCHAR(10), y VARCHAR(10));

DROP PROCEDURE p1;

CREATE PROCEDURE p1 AS

  rec1 t1%ROWTYPE;

  rec2 t2%ROWTYPE;

  CURSOR cur IS SELECT * FROM t1;

BEGIN

  OPEN cur;

  FETCH cur INTO rec1;

  CLOSE cur;

  DBMS_OUTPUT.PUT_LINE(rec1.a||' '||rec1.b);

  OPEN cur;

  FETCH cur INTO rec2;

  CLOSE cur;

  DBMS_OUTPUT.PUT_LINE(rec2.x||' '||rec2.y);

END;

CALL p1();

A B

A B

Two table%ROWTYPE variables are mutually assignable if they have the same set of equally named fields, but the order of the fields is not important. Strangely, fields are assigned by their ordinal position, from left to right:

SET SERVEROUTPUT ON;

DROP TABLE t1;

DROP TABLE t2;

CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10));

INSERT INTO t1 VALUES ('A','B');

CREATE TABLE t2 (b VARCHAR(10), a VARCHAR(10));

DROP PROCEDURE p1;

CREATE PROCEDURE p1 AS

  rec1 t1%ROWTYPE;

  rec2 t2%ROWTYPE;

  CURSOR cur IS SELECT * FROM t1;

BEGIN

  OPEN cur;

  FETCH cur INTO rec1;

  CLOSE cur;

  DBMS_OUTPUT.PUT_LINE(rec1.a||' '||rec1.b);

  rec2:=rec1;

  DBMS_OUTPUT.PUT_LINE(rec2.a||' '||rec2.b);

END;

CALL p1();

A B

B A

Alexander Barkov added a comment - 2017-02-27 06:52 - edited Oracle implementation details FETCH assigns fields of table%ROWTYPE variables from left to right. Field names are not important. SET SERVEROUTPUT ON; DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10)); INSERT INTO t1 VALUES ('A','B'); CREATE TABLE t2 (x VARCHAR(10), y VARCHAR(10)); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS rec1 t1%ROWTYPE; rec2 t2%ROWTYPE; CURSOR cur IS SELECT * FROM t1; BEGIN OPEN cur; FETCH cur INTO rec1; CLOSE cur; DBMS_OUTPUT.PUT_LINE(rec1.a||' '||rec1.b); OPEN cur; FETCH cur INTO rec2; CLOSE cur; DBMS_OUTPUT.PUT_LINE(rec2.x||' '||rec2.y); END; / CALL p1(); A B A B Two table%ROWTYPE variables are mutually assignable if they have the same set of equally named fields, but the order of the fields is not important. Strangely, fields are assigned by their ordinal position, from left to right: SET SERVEROUTPUT ON ; DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1 (a VARCHAR (10), b VARCHAR (10)); INSERT INTO t1 VALUES ( 'A' , 'B' ); CREATE TABLE t2 (b VARCHAR (10), a VARCHAR (10)); DROP PROCEDURE p1; CREATE PROCEDURE p1 AS rec1 t1%ROWTYPE; rec2 t2%ROWTYPE; CURSOR cur IS SELECT * FROM t1; BEGIN OPEN cur; FETCH cur INTO rec1; CLOSE cur; DBMS_OUTPUT.PUT_LINE(rec1.a|| ' ' ||rec1.b); rec2:=rec1; DBMS_OUTPUT.PUT_LINE(rec2.a|| ' ' ||rec2.b); END ; / CALL p1(); A B B A

Alexander Barkov made changes - 2017-03-01 09:32

Field	Original Value	New Value
Description	This task will implement Oracle-stype table%ROWTYPE declarations, for sql_mode=ORACLE. Example: {code:sql} rec t1%ROWTYPE; {code} The record variable {{rec}} can store the entire row of the data fetched from the table t1. There is no a need to specify column names and data types. They're automatically copied from {{t1}}. A complete working example: {code:sql} SET sql_mode=ORACLE; DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(32)); INSERT INTO t1 VALUES (10,'b10'); DROP PROCEDURE p1; DELIMITER $$ CREATE PROCEDURE p1 AS rec t1%ROWTYPE; CURSOR c IS SELECT * FROM t1; BEGIN OPEN c; LOOP FETCH c INTO rec; EXIT WHEN c%NOTFOUND; SELECT 'rec=(' \|\| rec.a \|\|','\|\| rec.b\|\|')' AS c FROM dual; END LOOP; CLOSE c; END; $$ DELIMITER ; CALL p1(); {code} {noformat} rec=(10,b10) {noformat}	This task will implement Oracle-stype table%ROWTYPE declarations, for sql_mode=ORACLE. Example: {code:sql} rec t1%ROWTYPE; {code} The record variable {{rec}} can store the entire row of the data fetched from the table t1. There is no a need to specify column names and data types. They're automatically copied from {{t1}}. Under scope of this task, we'll implement table%ROWTYPE for routine variables. Using table%ROWTYPE for routine parameters and function return values will be done separately. A complete working example: {code:sql} SET sql_mode=ORACLE; DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(32)); INSERT INTO t1 VALUES (10,'b10'); DROP PROCEDURE p1; DELIMITER $$ CREATE PROCEDURE p1 AS rec t1%ROWTYPE; CURSOR c IS SELECT * FROM t1; BEGIN OPEN c; LOOP FETCH c INTO rec; EXIT WHEN c%NOTFOUND; SELECT 'rec=(' \|\| rec.a \|\|','\|\| rec.b\|\|')' AS c FROM dual; END LOOP; CLOSE c; END; $$ DELIMITER ; CALL p1(); {code} {noformat} rec=(10,b10) {noformat} Data types will be resolved at the very beginning of a routine execution, in {{sp_rcontext::create()}}. If the tables referenced in %ROWTYPE declarations are altered inside the routine, this will not affect structures of the referencing %ROWTYPE variables. In the below example the variable {{rec}} will have only two fields {{a}} and {{b}}, it will not have the field {{c}}. The fact that {{rec}} is declared after the {{ALTER}} statement does not matter. This implementations will be close to Oracle, who determines all data types at {{CREATE PROCEDURE}} time. {{table%ROWTYPE}} and implicit {{ROW}} variables will be mutually assignable if they have the same number of fields. Note, Oracle has stricter rules, it also checks field names, but in a very strange way. See "Oracle implementation details". We won't check field names. Assignment will be done from left to right (the N'th source field is assigned to the N'th destination field). In the below example all three variables {{rec0}}, {{rec1}}, {{rec2}} will be mutually assignable, because they have the same number of fields: {code:sql} SET sql_mode=ORACLE; DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a INT, b VARCHAR(32)); CREATE TABLE t2 (c INT, d VARCHAR(32)); DROP PROCEDURE p1; DELIMITER $$ CREATE PROCEDURE p1 AS rec0 ROW(x INT,y INT); rec1 t1%ROWTYPE; rec2 t2%ROWTYPE; BEGIN rec0:=rec1; rec0:=rec2; rec1:=rec0; rec1:=rec2; rec2:=rec0; rec2:=rec1; END; $$ DELIMITER ; CALL p1(); {code}

Alexander Barkov made changes - 2017-03-01 09:40

Description

This task will implement Oracle-stype table%ROWTYPE declarations, for sql_mode=ORACLE.

Example:

{code:sql}
rec t1%ROWTYPE;
{code}

The record variable {{rec}} can store the entire row of the data fetched from the table t1. There is no a need to specify column names and data types. They're automatically copied from {{t1}}.

Under scope of this task, we'll implement table%ROWTYPE for routine variables. Using table%ROWTYPE for routine parameters and function return values will be done separately.

A complete working example:
{code:sql}
SET sql_mode=ORACLE;
DROP TABLE t1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
DROP PROCEDURE p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec t1%ROWTYPE;
  CURSOR c IS SELECT * FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT 'rec=(' || rec.a ||','|| rec.b||')' AS c FROM dual;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;
CALL p1();
{code}
{noformat}
rec=(10,b10)
{noformat}

Data types will be resolved at the very beginning of a routine execution, in {{sp_rcontext::create()}}. If the tables referenced in %ROWTYPE declarations are altered inside the routine, this will not affect structures of the referencing %ROWTYPE variables. In the below example the variable {{rec}} will have only two fields {{a}} and {{b}}, it will not have the field {{c}}. The fact that {{rec}} is declared after the {{ALTER}} statement does not matter. This implementations will be close to Oracle, who determines all data types at {{CREATE PROCEDURE}} time.

{{table%ROWTYPE}} and implicit {{ROW}} variables will be mutually assignable if they have the same number of fields. Note, Oracle has stricter rules, it also checks field names, but in a very strange way. See "Oracle implementation details". We won't check field names. Assignment will be done from left to right (the N'th source field is assigned to the N'th destination field).

In the below example all three variables {{rec0}}, {{rec1}}, {{rec2}} will be mutually assignable, because they have the same number of fields:
{code:sql}
SET sql_mode=ORACLE;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a INT, b VARCHAR(32));
CREATE TABLE t2 (c INT, d VARCHAR(32));
DROP PROCEDURE p1;
DELIMITER $$
CREATE PROCEDURE p1 AS
  rec0 ROW(x INT,y INT);
  rec1 t1%ROWTYPE;
  rec2 t2%ROWTYPE;
BEGIN
  rec0:=rec1;
  rec0:=rec2;
  rec1:=rec0;
  rec1:=rec2;
  rec2:=rec0;
  rec2:=rec1;
END;
$$
DELIMITER ;
CALL p1();
{code}

Alexander Barkov made changes - 2017-03-02 03:58

Link

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

Alexander Barkov made changes - 2017-03-02 03:58

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2017-03-10 10:20

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Alexander Barkov added a comment - 2017-03-10 10:21

Pushed to bb-10.2-compatibility

Alexander Barkov added a comment - 2017-03-10 10:21 Pushed to bb-10.2-compatibility

Alexander Barkov made changes - 2017-03-10 10:21

issue.field.resolutiondate

2017-03-10 10:21:17.0

2017-03-10 10:21:17.859

Alexander Barkov made changes - 2017-03-10 10:21

Component/s		Stored routines [ 13905 ]
Fix Version/s		10.3.0 [ 22127 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

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

Labels

Compatibility

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

Labels

Compatibility

Compatibility need_review

Michael Widenius added a comment - 2017-03-11 08:48

Review not done

Michael Widenius added a comment - 2017-03-11 08:48 Review not done

Michael Widenius made changes - 2017-03-11 08:48

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Michael Widenius made changes - 2017-03-11 08:48

Assignee

Alexander Barkov [ bar ]

Michael Widenius [ monty ]

Michael Widenius made changes - 2017-03-11 08:48

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Michael Widenius added a comment - 2017-03-11 08:49

Review done, some minor changes needed + some function comments

Michael Widenius added a comment - 2017-03-11 08:49 Review done, some minor changes needed + some function comments

Michael Widenius made changes - 2017-03-11 08:49

Assignee

Michael Widenius [ monty ]

Alexander Barkov [ bar ]

Alexander Barkov added a comment - 2017-04-04 06:38

Addressed Monty's review suggestions.
Pushed a cleanup patch to bb-10.2-compatibility.

Alexander Barkov added a comment - 2017-04-04 06:38 Addressed Monty's review suggestions. Pushed a cleanup patch to bb-10.2-compatibility.

Alexander Barkov made changes - 2017-04-04 06:38

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

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 11:41

Labels

Compatibility need_review

Compatibility

Alexander Barkov made changes - 2017-08-18 13:24

Link

This issue relates to ~~MDEV-13581~~ [ ~~MDEV-13581~~ ]

Alexander Barkov made changes - 2017-09-23 06:50

Link

This issue is blocked by ~~MDEV-10914~~ [ ~~MDEV-10914~~ ]

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

Workflow

MariaDB v3 [ 79758 ]

MariaDB v4 [ 151753 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2017-02-27 06:52

Updated:: 2018-08-31 17:08

Resolved:: 2017-04-04 06:38

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

Oracle implementation details

People

Dates

Git Integration