Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10764 PL/SQL parser - Phase 2
  3. MDEV-12252

ROW data type for stored function return values

Details

    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

          Technical task - A technical task. MDEV-12308 Accessing ROW type function fields

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-32380 Array data type for stored routnes

          • Major - Major loss of function.
          • Stalled
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35003 Server crashes when reading routines table after creating a function with "returns type of"

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36322 Comparison ROW(stored_func(),1)=ROW(1,1) calls the function twice per row

          • Major - Major loss of function.
          • Closed
          is blocked by

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Technical task - A technical task. MDEV-10914 ROW data type for stored routine variables

          • Major - Major loss of function.
          • Closed
          relates to

          Technical task - A technical task. MDEV-11210 TYPE OF and %TYPE in stored function RETURN data type

          • Major - Major loss of function.
          • Open

          Technical task - A technical task. MDEV-12307 ROW data type for built-in function return values

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          Task - A task that needs to be done. MDEV-12461 TYPE OF and ROW TYPE OF anchored data types for stored routine variables

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-23353 Qualified data types in SP

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31250 ROW variables do not get assigned from subselects

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-32380 Array data type for stored routnes

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            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.

            {code:sql}
            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');
            SELECT f1(1,'b1').a;
            {code}

            The last query will probably need serious grammar change to support field names after parentheses, e.g. {{f1(1,'b1').a}}.
            		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.

            {code:sql}
            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');
            SELECT f1(1,'b1').a;
            {code}

            The last query will probably need serious grammar change to support field names after parentheses, e.g. {{f1(1,'b1').a}}.
            bar Alexander Barkov made changes -
            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.

            {code:sql}
            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');
            SELECT f1(1,'b1').a;
            {code}

            The last query will probably need serious grammar change to support field names after parentheses, e.g. {{f1(1,'b1').a}}.
            		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.

            {code:sql}
            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');
            SELECT f1(1,'b1').a;
            {code}

            The last query will probably need serious grammar changes to support field names after parentheses, e.g. {{f1(1,'b1').a}}.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Assignee Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79959 ] MariaDB v4 [ 140248 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ]
            Status Open [ 1 ] Needs Feedback [ 10501 ]
            bar Alexander Barkov made changes -
            Fix Version/s N/A [ 14700 ]
            Resolution Incomplete [ 4 ]
            Status Needs Feedback [ 10501 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Resolution Incomplete [ 4 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Fix Version/s 11.4 [ 29301 ]
            Fix Version/s N/A [ 14700 ]
            bar Alexander Barkov added a comment -

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

            bar Alexander Barkov added a comment - Hi serg , Can you please review: https://github.com/MariaDB/server/commit/388b72578cdee7afbd0403e94077ee90ed648008 ? Thanks.
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            bar Alexander Barkov made changes -
            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.

            {code:sql}
            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');
            SELECT f1(1,'b1').a;
            {code}

            The last query will probably need serious grammar changes to support field names after parentheses, e.g. {{f1(1,'b1').a}}.
            		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.

            {code:sql}
            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');
            {code}
            serg Sergei Golubchik made changes -
            Fix Version/s 11.5 [ 29506 ]
            Fix Version/s 11.4 [ 29301 ]
            bradlanier Brad Lanier made changes -
            Comment [ Please also allow all stored routines to accept the FULL ROW type, not just the individual row fields.
            Example:
            DROP FUNCTION IF EXISTS f2;
            DELIMITER $$
            CREATE FUNCTION f2(rin ROW(a INT, b VARCHAR(32)) RETURNS ROW(a INT, b VARCHAR(32))
               BEGIN
                  RETURN ROW(rin.a, rin.b);
               END;
            $$
            DELIMITER ;

            from within another stored routine:
            DECLARE rtest ROW(a INT, b VARCHAR(32));
            rtest.a := 1;
            rtest.b := 'b1';
            SELECT f2(rtest) = ROW(1,'b1');

            This will allow functions/procedures intended to be called only by other functions/procedures to accept one row parameter instead of many individual parameters.
            ]
            bradlanier Brad Lanier added a comment - - 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.

            bradlanier Brad Lanier added a comment - - 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.
            bar Alexander Barkov added a comment -

            serg,

            Please see a new patch version here:

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

            Rebased to the latest codebase.

            bar Alexander Barkov added a comment - serg , Please see a new patch version here: https://github.com/MariaDB/server/commit/c46e7c5cb64940c8929a6d8e09a308cb690b5fdc Rebased to the latest codebase.
            bar Alexander Barkov made changes -
            bar Alexander Barkov added a comment - - 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.

            bar Alexander Barkov added a comment - - 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.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            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.

            {code:sql}
            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');
            {code}
            		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.

            {code:sql}
            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');
            {code}

            Anchored row data types should also be supported:
            {code:sql}
            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');
            {code}
            serg Sergei Golubchik made changes -
            Fix Version/s 11.6 [ 29515 ]
            Fix Version/s 11.5 [ 29506 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 11.7 [ 29815 ]
            Fix Version/s 11.6 [ 29515 ]
            Priority Critical [ 2 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov added a comment -

            Hello Sergei,

            Please find a new patch version here:

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

            bar Alexander Barkov added a comment - Hello Sergei, Please find a new patch version here: https://github.com/MariaDB/server/commit/715dbfa6757d319b1a18004467ed0b5b26fc31a3
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            serg Sergei Golubchik added a comment -

            715dbfa6757 is ok to push

            serg Sergei Golubchik added a comment - 715dbfa6757 is ok to push
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov added a comment -

            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

            bar Alexander Barkov added a comment - 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
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Elena Stepanova [ elenst ]
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Testing [ 10301 ]
            elenst Elena Stepanova made changes -
            Assignee Elena Stepanova [ elenst ] Ramesh Sivaraman [ JIRAUSER48189 ]
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_11.7
            ramesh Ramesh Sivaraman made changes -
            ramesh Ramesh Sivaraman added a comment -

            okay to push

            ramesh Ramesh Sivaraman added a comment - okay to push
            ramesh Ramesh Sivaraman made changes -
            Assignee Ramesh Sivaraman [ JIRAUSER48189 ] Alexander Barkov [ bar ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Fix Version/s 11.7.1 [ 29913 ]
            Fix Version/s 11.7 [ 29815 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            bar Alexander Barkov made changes -

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              4 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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