[MDEV-12252] ROW data type for stored function return values - Jira

Details

Type: Technical task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 11.7.1
Component/s: Parser, Stored routines
Labels:
- Preview_11.7

Description

The task ~~MDEV-10914~~ implements the ROW data type for stored routine local variables and parameters.

This task will additionally allow to use ROW in stored function return data type.

DROP FUNCTION IF EXISTS f1;

DELIMITER $$

CREATE FUNCTION f1(a INT, b VARCHAR(32)) RETURNS ROW(a INT, b VARCHAR(32))

BEGIN

  RETURN ROW(a,b);

END;

$$

DELIMITER ;

SELECT f1(1,'b1') = ROW(1,'b1');

Anchored row data types should also be supported:

DROP FUNCTION IF EXISTS f1;

DELIMITER $$

CREATE FUNCTION f1(a INT, b VARCHAR(32)) RETURNS ROW TYPE OF t1

BEGIN

  RETURN ROW(a,b);

END;

$$

DELIMITER ;

SELECT f1(1,'b1') = ROW(1,'b1');

Attachments

Issue Links

blocks

MDEV-12308 Accessing ROW type function fields

Open

MDEV-32380 Array data type for stored routnes

Stalled

causes

MDEV-35003 Server crashes when reading routines table after creating a function with "returns type of"

Closed

MDEV-36322 Comparison ROW(stored_func(),1)=ROW(1,1) calls the function twice per row

Closed

is blocked by

MDEV-4912 Data type plugin API version 1

Closed

MDEV-10914 ROW data type for stored routine variables

Closed

relates to

MDEV-11210 TYPE OF and %TYPE in stored function RETURN data type

Open

MDEV-12307 ROW data type for built-in function return values

Stalled

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

Closed

MDEV-23353 Qualified data types in SP

Open

MDEV-31250 ROW variables do not get assigned from subselects

Closed

MDEV-32380 Array data type for stored routnes

Stalled

(1 is blocked by, 6 relates to)

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2023-12-04 06:15

Hi serg,
Can you please review:
https://github.com/MariaDB/server/commit/388b72578cdee7afbd0403e94077ee90ed648008
?
Thanks.

Alexander Barkov added a comment - 2023-12-04 06:15 Hi serg , Can you please review: https://github.com/MariaDB/server/commit/388b72578cdee7afbd0403e94077ee90ed648008 ? Thanks.

Brad Lanier added a comment - 2024-02-17 22:58 - edited

Actually, the key word TYPE OF should be allowed in a function return for ALL data types, including ROW. The strength of TYPE OF is in Stored Routine maintenance. When a function returns a value that is directly used in a SQL table query, that value should be allowed to be TYPED in relation to the field that it is to be used with.

Therefore, I strongly urge the DEVO, when implementing ROW for function returns, to go a little bit further and implement TYPE OF for ROW as well as ALL OTHER data types.

Brad Lanier added a comment - 2024-02-17 22:58 - edited Actually, the key word TYPE OF should be allowed in a function return for ALL data types, including ROW. The strength of TYPE OF is in Stored Routine maintenance. When a function returns a value that is directly used in a SQL table query, that value should be allowed to be TYPED in relation to the field that it is to be used with. Therefore, I strongly urge the DEVO, when implementing ROW for function returns, to go a little bit further and implement TYPE OF for ROW as well as ALL OTHER data types.

Alexander Barkov added a comment - 2024-02-20 10:05

serg,

Please see a new patch version here:

https://github.com/MariaDB/server/commit/c46e7c5cb64940c8929a6d8e09a308cb690b5fdc

Rebased to the latest codebase.

Alexander Barkov added a comment - 2024-02-20 10:05 serg , Please see a new patch version here: https://github.com/MariaDB/server/commit/c46e7c5cb64940c8929a6d8e09a308cb690b5fdc Rebased to the latest codebase.

Alexander Barkov added a comment - 2024-02-20 10:14 - edited

bradlanier, this patch extends the RETURNS clause to support both:

explicit ROW
anchored data types (TYPE OF and ROW TYPE OF) - MDEV-11210

We should rephrase the task description slightly.

Alexander Barkov added a comment - 2024-02-20 10:14 - edited bradlanier , this patch extends the RETURNS clause to support both: explicit ROW anchored data types (TYPE OF and ROW TYPE OF) - MDEV-11210 We should rephrase the task description slightly.

Alexander Barkov added a comment - 2024-09-05 11:19

Hello Sergei,

Please find a new patch version here:

https://github.com/MariaDB/server/commit/715dbfa6757d319b1a18004467ed0b5b26fc31a3

Alexander Barkov added a comment - 2024-09-05 11:19 Hello Sergei, Please find a new patch version here: https://github.com/MariaDB/server/commit/715dbfa6757d319b1a18004467ed0b5b26fc31a3

Sergei Golubchik added a comment - 2024-09-06 19:15

715dbfa6757 is ok to push

Sergei Golubchik added a comment - 2024-09-06 19:15 715dbfa6757 is ok to push

Alexander Barkov added a comment - 2024-09-09 14:24

Hello elenst,

Please find the patch for testing in this branch:
https://github.com/MariaDB/server/tree/bb-11.7-bar-MDEV-12252-sp-row

This is the patch itself:

https://github.com/MariaDB/server/commit/cb8496d989a5db6356ddb188aa30d008161284ba

Thanks

Alexander Barkov added a comment - 2024-09-09 14:24 Hello elenst , Please find the patch for testing in this branch: https://github.com/MariaDB/server/tree/bb-11.7-bar-MDEV-12252-sp-row This is the patch itself: https://github.com/MariaDB/server/commit/cb8496d989a5db6356ddb188aa30d008161284ba Thanks

Ramesh Sivaraman added a comment - 2024-10-15 07:13

okay to push

Ramesh Sivaraman added a comment - 2024-10-15 07:13 okay to push

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 4 Vote for this issue

Watchers:: 10 Start watching this issue

Dates

Created:: 2017-03-14 04:05

Updated:: 1 week ago 14:26

Resolved:: 2024-10-21 13:51

Git Integration

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

MariaDB Server