-
10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18
Add support for the ROW data type variables in stored routines according to this SQL Standard syntax:
<row type> ::= ROW <row type body>
|
|
<row type body> ::= <left paren> <field definition> [ { <comma> <field definition> }... ] <right paren>
|
|
<field definition> ::= <field name> <data type>
|
|
<data type> ::= <predefined type>
|
and add support for a new expression type, a ROW field reference, as follows:
<field reference> ::= <row variable> <period> <field name>
|
where <row variable> is a stored routine variable declared using <row type>.
Example:
DELIMITER $$
|
CREATE PROCEDURE p1()
|
BEGIN
|
DECLARE a ROW (c1 INT, c2 VARCHAR(10));
|
SET a.c1= 10;
|
SET a.c2= 'test';
|
INSERT INTO t1 VALUES (a.c1, a.c2);
|
END;
|
$$
|
DELIMITER ;
|
CALL p1();
|
This task is needed to create infrastructure for MDEV-10593
This task is also needed as a prerequisite for MDEV-10581, where this statement:
will automatically declare an index variable rec of the ROW data type, according to the cursor structure.
Row data type features:
- Declaration of a ROW type stored routine variable (both local variables and parameters)
- Declaration of a ROW type stored procedure OUT parameter
- Default values in a ROW type variable declaration (e.g. DEFAULT ROW(1,2))
- Assignment of a ROW type variable from another ROW type variable (using the SET command and the := operator in sql_mode=ORACLE)
- Assignment of a ROW type variable from a ROW() function result (using the SET command and the := operator in sql_mode=ORACLE)
- Passing a ROW type variable and a ROW() function result to stored routines
- Comparison of a ROW type variable to another ROW type variable
- Comparison of a ROW type variable to ROW() function
Row field features:
ROW fields (members) will act as normal variables, and will be able to appear in all query parts where an SP variable is allowed:
- assignment (using the SET command)
SET a.x= 10, a.y=20, a.z= b.z;
|
- assignment (sql_mode=ORACLE specific syntax)
- passing to functions and operators
SELECT f1(rec.a), rec.a<10;
|
- clauses: select list, WHERE, HAVING, LIMIT, etc
SELECT var.a, t1.b FROM t1 WHERE t1.b=var.b LIMIT var.c;
|
- INSERT values
INSERT INTO t1 VALUES (rec.a, rec.b, rec.c);
|
- SELECT .. INTO targets
SELECT a,b INTO rec.a, rec.b FROM t1 WHERE t1.id=10;
|
- Dynamic SQL out parameters (EXECUTE and EXECUTE IMMEDIATE)
EXECUTE IMMEDIATE 'CALL proc_with_out_param(?)' USING rec.a;
|
Features not supported:
The following features are out of scope of this task and will be implemented separately:
- Returning a ROW type expression from a stored function (see
MDEV-12252). This will need some grammar change to support field names after parentheses:
- Returning a ROW type expression from a built-in hybrid type function, such as CASE, IF, etc.
- ROW of ROWs
{"report":{"fcp":939,"ttfb":158.5,"pageVisibility":"visible","entityId":58267,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"6c038460-ff6f-4106-ada2-46bfc7c686ba","navigationType":0,"readyForUser":1017,"redirectCount":0,"resourceLoadedEnd":629.7999999523163,"resourceLoadedStart":163.89999997615814,"resourceTiming":[{"duration":304,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":163.89999997615814,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":163.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":467.89999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":303.7000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":164.29999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":164.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":468,"responseStart":0,"secureConnectionStart":0},{"duration":312.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":164.39999997615814,"connectEnd":164.39999997615814,"connectStart":164.39999997615814,"domainLookupEnd":164.39999997615814,"domainLookupStart":164.39999997615814,"fetchStart":164.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":164.39999997615814,"responseEnd":476.89999997615814,"responseStart":476.89999997615814,"secureConnectionStart":164.39999997615814},{"duration":336.89999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":164.5,"connectEnd":164.5,"connectStart":164.5,"domainLookupEnd":164.5,"domainLookupStart":164.5,"fetchStart":164.5,"redirectEnd":0,"redirectStart":0,"requestStart":164.5,"responseEnd":501.39999997615814,"responseStart":501.39999997615814,"secureConnectionStart":164.5},{"duration":340.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":164.60000002384186,"connectEnd":164.60000002384186,"connectStart":164.60000002384186,"domainLookupEnd":164.60000002384186,"domainLookupStart":164.60000002384186,"fetchStart":164.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":164.60000002384186,"responseEnd":504.89999997615814,"responseStart":504.89999997615814,"secureConnectionStart":164.60000002384186},{"duration":341,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":164.69999992847443,"connectEnd":164.69999992847443,"connectStart":164.69999992847443,"domainLookupEnd":164.69999992847443,"domainLookupStart":164.69999992847443,"fetchStart":164.69999992847443,"redirectEnd":0,"redirectStart":0,"requestStart":164.69999992847443,"responseEnd":505.6999999284744,"responseStart":505.6999999284744,"secureConnectionStart":164.69999992847443},{"duration":340.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":164.69999992847443,"connectEnd":164.69999992847443,"connectStart":164.69999992847443,"domainLookupEnd":164.69999992847443,"domainLookupStart":164.69999992847443,"fetchStart":164.69999992847443,"redirectEnd":0,"redirectStart":0,"requestStart":164.69999992847443,"responseEnd":505.39999997615814,"responseStart":505.39999997615814,"secureConnectionStart":164.69999992847443},{"duration":341.8000000715256,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":164.79999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":164.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":506.60000002384186,"responseStart":0,"secureConnectionStart":0},{"duration":341.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":164.89999997615814,"connectEnd":164.89999997615814,"connectStart":164.89999997615814,"domainLookupEnd":164.89999997615814,"domainLookupStart":164.89999997615814,"fetchStart":164.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":164.89999997615814,"responseEnd":506.10000002384186,"responseStart":506.10000002384186,"secureConnectionStart":164.89999997615814},{"duration":341.6999999284744,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":165.10000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":165.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":506.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":341.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":165.10000002384186,"connectEnd":165.10000002384186,"connectStart":165.10000002384186,"domainLookupEnd":165.10000002384186,"domainLookupStart":165.10000002384186,"fetchStart":165.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":165.10000002384186,"responseEnd":506.60000002384186,"responseStart":506.60000002384186,"secureConnectionStart":165.10000002384186},{"duration":400.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":171.60000002384186,"connectEnd":171.60000002384186,"connectStart":171.60000002384186,"domainLookupEnd":171.60000002384186,"domainLookupStart":171.60000002384186,"fetchStart":171.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":171.60000002384186,"responseEnd":571.8999999761581,"responseStart":571.8999999761581,"secureConnectionStart":171.60000002384186},{"duration":458,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":171.79999995231628,"connectEnd":171.79999995231628,"connectStart":171.79999995231628,"domainLookupEnd":171.79999995231628,"domainLookupStart":171.79999995231628,"fetchStart":171.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":171.79999995231628,"responseEnd":629.7999999523163,"responseStart":629.7999999523163,"secureConnectionStart":171.79999995231628}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":7,"responseStart":158,"responseEnd":167,"domLoading":162,"domInteractive":1084,"domContentLoadedEventStart":1084,"domContentLoadedEventEnd":1129,"domComplete":2025,"loadEventStart":2025,"loadEventEnd":2026,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1065.1999999284744},{"name":"bigPipe.sidebar-id.end","time":1066},{"name":"bigPipe.activity-panel-pipe-id.start","time":1066.1999999284744},{"name":"bigPipe.activity-panel-pipe-id.end","time":1068.5},{"name":"activityTabFullyLoaded","time":1144.2999999523163}],"measures":[],"correlationId":"c524b9e933e419","effectiveType":"4g","downlink":9.5,"rtt":0,"serverDuration":90,"dbReadsTimeInMs":18,"dbConnsTimeInMs":25,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}