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

ROW data type for built-in function return values

    XMLWordPrintable

Details

    Description

      This task is related to MDEV-12252.

      Under terms of this task we'll make it possible to return ROW data values from built-in hybrid functions:

      • IF
      • CASE

      Other hybrid functions (COALESCE, IFNULL, NULLIF, LEAST, GREATEST) are out of scope of this task, because they involve NULL tests or comparison, which are not well defined for the ROW data type.

      This SQL script:

      SET sql_mode=DEFAULT;
      		 
      DROP PROCEDURE p1;
      		 
      DELIMITER $$
      		 
      CREATE PROCEDURE p1()
      		 
      BEGIN
      		 
        DECLARE rec0 ROW (a INT, b VARCHAR(10)) DEFAULT ROW(12,'b2');
      		 
        DECLARE rec1 ROW (a INT, b VARCHAR(10)) DEFAULT ROW(10,'b0');
      		 
        DECLARE rec2 ROW (a INT, b VARCHAR(10)) DEFAULT ROW(11,'b1');
      		 
        rec0 := CASE WHEN TRUE THEN rec1 ELSE rec2 END;
      		 
        SELECT rec0.a, rec0.b;
      		 
        rec0 := CASE WHEN FALSE THEN rec1 ELSE rec2 END;
      		 
        SELECT rec0.a, rec0.b;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      CALL p1();

      will return

      +--------+--------+
      		 
      | rec0.a | rec0.b |
      		 
      +--------+--------+
      		 
      |     10 | b0     |
      		 
      +--------+--------+
      		 
      +--------+--------+
      		 
      | rec0.a | rec0.b |
      		 
      +--------+--------+
      		 
      |     12 | b2     |
      		 
      +--------+--------+

      So will this script:

      SET sql_mode=ORACLE;
      		 
      DROP PROCEDURE p1;
      		 
      DELIMITER $$
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
        rec0 ROW (a INT, b VARCHAR(10)) := ROW(12,'b2');
      		 
        rec1 ROW (a INT, b VARCHAR(10)) := ROW(10,'b0');
      		 
        rec2 ROW (a INT, b VARCHAR(10)) := ROW(11,'b1');
      		 
      BEGIN
      		 
        rec0 := CASE WHEN TRUE THEN rec1 ELSE rec2 END;
      		 
        SELECT rec0.a, rec0.b;
      		 
        rec0 := CASE WHEN FALSE THEN rec1 ELSE rec2 END;
      		 
        SELECT rec0.a, rec0.b;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      CALL p1();

      An equivalent script in Oracle looks like:

      SET SERVEROUTPUT ON;
      		 
      DROP PROCEDURE p1;
      		 
      CREATE PROCEDURE p1
      		 
      AS
      		 
        TYPE rec_t IS RECORD (a INT := 10, b VARCHAR(10) :='b0');
      		 
        rec0 rec_t;
      		 
        rec1 rec_t;
      		 
        rec2 rec_t;
      		 
      BEGIN
      		 
        rec2.a:= 12;
      		 
        rec2.b:= 'b2';
      		 
        rec0 := CASE WHEN TRUE THEN rec1 ELSE rec2 END;
      		 
        DBMS_OUTPUT.PUT_LINE(rec0.a || ' ' || rec0.b);
      		 
        rec0 := CASE WHEN FALSE THEN rec1 ELSE rec2 END;
      		 
        DBMS_OUTPUT.PUT_LINE(rec0.a || ' ' || rec0.b);
      		 
      END;
      		 
      /
      		 
      CALL p1();

      Attachments

        Issue Links

          blocks

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

          • Major - Major loss of function.
          • Open
          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
          relates to

          Technical task - A technical task. MDEV-12252 ROW data type for stored function return values

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

          Activity

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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