Details

    • Type: Technical task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3
    • Fix Version/s: None
    • Component/s: Data types
    • Labels:

      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

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: