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

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.