Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12461

TYPE OF and ROW TYPE OF anchored data types for stored routine variables

Details

    Description

      This task will port a few tasks that were previously done for sql_mode=ORACLE, now for sql_mode=DEFAULT. See the list of "is blocked by" tasks for the list.

      Under terms of this task we'll add a new syntax in SP variable declarations:

        DECLARE tmp TYPE OF t1.a;  -- Get the data type from the column {{a}} in the table {{t1}}
      		 
        DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
      		 
        DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}

      This non-standard syntax will provide the functionality available in some other databases, often referenced to as anchored data type:

      • Oracle 

        DECLARE va t1.a%TYPE;
        		 
        DECLARE rec1 t1%ROWTYPE;
        		 
        DECLARE rec2 cur1%ROWTYPE;

      • IBM: 

        DECLARE va ANCHOR DATA TYPE TO t1.a;
        		 
        DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
        		 
        DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;

      • Firebird: 

        DECLARE va TYPE OF COLUMN t1.a;

      Example: using TYPE OF to in combination with MAX() and MIN()

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10);
      		 
      INSERT INTO t1 VALUES (20);
      		 
       
      		 
      DROP FUNCTION IF EXISTS f1;
      		 
      DELIMITER $$
      		 
      CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
      		 
      BEGIN
      		 
        DECLARE tmp TYPE OF t1.a;
      		 
        SELECT MAX(a) INTO tmp FROM t1;
      		 
        RETURN tmp = prm;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;

      Example: Using TYPE OF variables with a cursor

      SET sql_mode=DEFAULT;
      		 
      DROP TABLE IF EXISTS t1, t2;
      		 
      DROP PROCEDURE IF EXISTS p1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      CREATE TABLE t2 LIKE t1;
      		 
      INSERT INTO t1 VALUES (10),(20);
      		 
      DELIMITER $$
      		 
      CREATE PROCEDURE p1()
      		 
      BEGIN
      		 
        DECLARE done INT DEFAULT 0;
      		 
        DECLARE va TYPE OF t1.a;
      		 
        DECLARE cur CURSOR FOR SELECT a FROM t1;
      		 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
      		 
        OPEN cur;
      		 
        read_loop: LOOP
      		 
          FETCH cur INTO va;
      		 
          IF done THEN
      		 
            LEAVE read_loop;
      		 
          END IF;
      		 
          INSERT INTO t2 VALUES (va);
      		 
        END LOOP;
      		 
        CLOSE cur;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      CALL p1();
      		 
      SELECT * FROM t2;

      Example: using ROW TYPE OF for a cursor

      DROP TABLE IF EXISTS t1,t2;
      		 
      DROP PROCEDURE IF EXISTS p1;
      		 
      CREATE TABLE t1 (a INT, b VARCHAR(32));
      		 
      CREATE TABLE t2 LIKE t1;
      		 
      INSERT INTO t1 VALUES (10,'b10');
      		 
      INSERT INTO t1 VALUES (20,'b20');
      		 
      INSERT INTO t1 VALUES (30,'b30');
      		 
      DELIMITER $$
      		 
      CREATE PROCEDURE p1()
      		 
      BEGIN
      		 
        DECLARE c CURSOR FOR SELECT a,b FROM t1;
      		 
        BEGIN
      		 
          DECLARE done INT DEFAULT FALSE;
      		 
          DECLARE rec ROW TYPE OF c;
      		 
          DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
      		 
          OPEN c;
      		 
          read_loop: LOOP
      		 
            FETCH c INTO rec;
      		 
            IF done THEN
      		 
              LEAVE read_loop;
      		 
            END IF;
      		 
            INSERT INTO t2 VALUES (rec.a, rec.b);
      		 
          END LOOP;
      		 
          CLOSE c;
      		 
        END;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      CALL p1();
      		 
      SELECT * FROM t2;


      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      |   10 | b10  |
      		 
      |   20 | b20  |
      		 
      |   30 | b30  |
      		 
      +------+------+

      Using ROW TYPE OF for a table

      DROP TABLE IF EXISTS t1,t2;
      		 
      DROP PROCEDURE IF EXISTS p1;
      		 
      CREATE TABLE t1 (a INT, b VARCHAR(32));
      		 
      CREATE TABLE t2 LIKE t1;
      		 
      INSERT INTO t1 VALUES (10,'b10');
      		 
      INSERT INTO t1 VALUES (20,'b20');
      		 
      INSERT INTO t1 VALUES (30,'b30');
      		 
      DELIMITER $$
      		 
      CREATE PROCEDURE p1()
      		 
      BEGIN
      		 
        DECLARE c CURSOR FOR SELECT * FROM t1;
      		 
        BEGIN
      		 
          DECLARE done INT DEFAULT FALSE;
      		 
          DECLARE rec ROW TYPE OF t1;
      		 
          DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
      		 
          OPEN c;
      		 
          read_loop: LOOP
      		 
            FETCH c INTO rec;
      		 
            IF done THEN
      		 
              LEAVE read_loop;
      		 
            END IF;
      		 
            INSERT INTO t2 VALUES (rec.a, rec.b);
      		 
          END LOOP;
      		 
          CLOSE c;
      		 
        END;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;
      		 
      CALL p1();
      		 
      SELECT * FROM t2;


      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      |   10 | b10  |
      		 
      |   20 | b20  |
      		 
      |   30 | b30  |
      		 
      +------+------+

      Scope of this task

      This task will implement anchored types only for local variables and parameters. Using TYPE OF in a stored function RETURNS clause will be done separately (similar to MDEV-10577).

      Variables declared with ROW TYPE OF will have the same features with implicit ROW variables (see MDEV-10914). It will not be however possible to use ROW TYPE OF variables in LIMIT clause.

      Real data type instantiation

      The real data type of TYPE OF and ROW TYPE OF table_name will become known at the very beginning of the stored routine call, namely during the sp_rcontext::create() call. ALTER TABLE or DROP TABLE done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a ALTER TABLE or DROP TABLE statement.

      The real data type of a ROW TYPE OF cursor_name variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only one time. In a cursor ROW TYPE OF variable is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.

      Referenced tables existence

      The tables referenced in TYPE OF and ROW TYPE OF declarations will be checked for existence at the beginning of the stored routine call. CREATE PROCEDURE or CREATE FUNCTION will not check the referenced tables for existence.

      Attachments

        Issue Links

          is blocked by

          Technical task - A technical task. MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-12007 Allow ROW variables as a cursor FETCH target

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable declarations

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-12291 Allow ROW variables as SELECT INTO targets

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

          Task - A task that needs to be done. MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters

          • Major - Major loss of function.
          • Closed

          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

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This syntax will provide the functionality available in some other databases, often referenced to as {{anchored type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}


            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}


            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}


            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            /
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURN INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            AS
              tmp TYPE OF t1.a;
            BEGIN
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Summary TYPE OF anchored data type TYPE OF and ROW TYPE OF anchored data types
            bar Alexander Barkov made changes -
            Description Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a;
            {code}

            The above statement declares a variable that has the same data type with the column {{a}} in the table {{t1}}.


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately.


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            Example#1:
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            Example#2:
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}



            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).


            h1. Real data type instantiation
            The real data type will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).

            Variables declared with {{ROW TYPE OF}} will have the same features with implicit {{ROW}} variables (see {{MDEV-10914}}). It will not be however possible to use {{ROW TYPE OF}} in {{LIMIT}} clause.

            h1. Real data type instantiation
            The real data type of {{TYPE OF}} and {{ROW TYPE OF table_name}} will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).

            Variables declared with {{ROW TYPE OF}} will have the same features with implicit {{ROW}} variables (see {{MDEV-10914}}). It will not be however possible to use {{ROW TYPE OF}} in {{LIMIT}} clause.

            h1. Real data type instantiation
            The real data type of {{TYPE OF}} and {{ROW TYPE OF table_name}} will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).

            Variables declared with {{ROW TYPE OF}} will have the same features with implicit {{ROW}} variables (see {{MDEV-10914}}). It will not be however possible to use {{ROW TYPE OF}} variables in {{LIMIT}} clause.

            h1. Real data type instantiation
            The real data type of {{TYPE OF}} and {{ROW TYPE OF table_name}} will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            The real data type of a {{ROW TYPE OF cursor_name}} variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only one time. In a cursor {{ROW TYPE OF}} variable is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).

            Variables declared with {{ROW TYPE OF}} will have the same features with implicit {{ROW}} variables (see {{MDEV-10914}}). It will not be however possible to use {{ROW TYPE OF}} variables in {{LIMIT}} clause.

            h1. Real data type instantiation
            The real data type of {{TYPE OF}} and {{ROW TYPE OF table_name}} will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            The real data type of a {{ROW TYPE OF cursor_name}} variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only one time. In a cursor {{ROW TYPE OF}} variable is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.

            h1. Referenced tables existence
            The referenced tables will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.
            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).

            Variables declared with {{ROW TYPE OF}} will have the same features with implicit {{ROW}} variables (see {{MDEV-10914}}). It will not be however possible to use {{ROW TYPE OF}} variables in {{LIMIT}} clause.

            h1. Real data type instantiation
            The real data type of {{TYPE OF}} and {{ROW TYPE OF table_name}} will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            The real data type of a {{ROW TYPE OF cursor_name}} variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only one time. In a cursor {{ROW TYPE OF}} variable is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.

            h1. Referenced tables existence
            The tables referenced in {{TYPE OF}} and {{ROW TYPE OF}} declarations will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.

            bar Alexander Barkov made changes -
            Description This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (as it was done in MDEV-10577).

            Variables declared with {{ROW TYPE OF}} will have the same features with implicit {{ROW}} variables (see {{MDEV-10914}}). It will not be however possible to use {{ROW TYPE OF}} variables in {{LIMIT}} clause.

            h1. Real data type instantiation
            The real data type of {{TYPE OF}} and {{ROW TYPE OF table_name}} will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            The real data type of a {{ROW TYPE OF cursor_name}} variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only one time. In a cursor {{ROW TYPE OF}} variable is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.

            h1. Referenced tables existence
            The tables referenced in {{TYPE OF}} and {{ROW TYPE OF}} declarations will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.

            		This task will port a few tasks that were previously done for {{sql_mode=ORACLE}}, now for {{sql_mode=DEFAULT}}. See the list of "is blocked by" tasks for the list.

            Under terms of this task we'll add a new syntax in SP variable declarations:

            {code:sql}
              DECLARE tmp TYPE OF t1.a; -- Get the data type from the column {{a}} in the table {{t1}}
              DECLARE rec1 ROW TYPE OF t1; -- Get the row data type from the table {{t1}}
              DECLARE rec2 ROW TYPE OF cur1; -- Get the row data type from the cursor {{cur1}}
            {code}


            This non-standard syntax will provide the functionality available in some other databases, often referenced to as {{anchored data type}}:
            - Oracle
            {code:sql}
            DECLARE va t1.a%TYPE;
            DECLARE rec1 t1%ROWTYPE;
            DECLARE rec2 cur1%ROWTYPE;
            {code}
            - IBM:
            {code:sql}
            DECLARE va ANCHOR DATA TYPE TO t1.a;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW t1;
            DECLARE rec1 ANCHOR DATA TYPE TO ROW cur1;
            {code}
            - Firebird:
            {code:sql}
            DECLARE va TYPE OF COLUMN t1.a;
            {code}


            h2. Example: using {{TYPE OF}} to in combination with {{MAX()}} and {{MIN()}}
            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (10);
            INSERT INTO t1 VALUES (20);

            DROP FUNCTION IF EXISTS f1;
            DELIMITER $$
            CREATE FUNCTION f1(prm TYPE OF t1.a) RETURNS INT
            BEGIN
              DECLARE tmp TYPE OF t1.a;
              SELECT MAX(a) INTO tmp FROM t1;
              RETURN tmp = prm;
            END;
            $$
            DELIMITER ;
            SELECT f1((SELECT MIN(a) FROM t1)) FROM DUAL;
            {code}

            h2. Example: Using {{TYPE OF}} variables with a cursor
            {code:sql}
            SET sql_mode=DEFAULT;
            DROP TABLE IF EXISTS t1, t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10),(20);
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE done INT DEFAULT 0;
              DECLARE va TYPE OF t1.a;
              DECLARE cur CURSOR FOR SELECT a FROM t1;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
              OPEN cur;
              read_loop: LOOP
                FETCH cur INTO va;
                IF done THEN
                  LEAVE read_loop;
                END IF;
                INSERT INTO t2 VALUES (va);
              END LOOP;
              CLOSE cur;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}

            h2. Example: using {{ROW TYPE OF}} for a cursor
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT a,b FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF c;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h2. Using {{ROW TYPE OF}} for a table
            {code:sql}
            DROP TABLE IF EXISTS t1,t2;
            DROP PROCEDURE IF EXISTS p1;
            CREATE TABLE t1 (a INT, b VARCHAR(32));
            CREATE TABLE t2 LIKE t1;
            INSERT INTO t1 VALUES (10,'b10');
            INSERT INTO t1 VALUES (20,'b20');
            INSERT INTO t1 VALUES (30,'b30');
            DELIMITER $$
            CREATE PROCEDURE p1()
            BEGIN
              DECLARE c CURSOR FOR SELECT * FROM t1;
              BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE rec ROW TYPE OF t1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
                OPEN c;
                read_loop: LOOP
                  FETCH c INTO rec;
                  IF done THEN
                    LEAVE read_loop;
                  END IF;
                  INSERT INTO t2 VALUES (rec.a, rec.b);
                END LOOP;
                CLOSE c;
              END;
            END;
            $$
            DELIMITER ;
            CALL p1();
            SELECT * FROM t2;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 10 | b10 |
            | 20 | b20 |
            | 30 | b30 |
            +------+------+
            {noformat}


            h1. Scope of this task
            This task will implement anchored types only for local variables and parameters. Using {{TYPE OF}} in a stored function {{RETURNS}} clause will be done separately (similar to MDEV-10577).

            Variables declared with {{ROW TYPE OF}} will have the same features with implicit {{ROW}} variables (see {{MDEV-10914}}). It will not be however possible to use {{ROW TYPE OF}} variables in {{LIMIT}} clause.

            h1. Real data type instantiation
            The real data type of {{TYPE OF}} and {{ROW TYPE OF table_name}} will become known at the very beginning of the stored routine call, namely during the {{sp_rcontext::create()}} call. {{ALTER TABLE}} or {{DROP TABLE}} done inside the current routine on the tables that appear in anchors won't affect the data type of the anchored variables, even if the variable is declare after a {{ALTER TABLE}} or {{DROP TABLE}} statement.

            The real data type of a {{ROW TYPE OF cursor_name}} variable will become known when execution enters into the block where the variable is declared. Data type instantiation will happen only one time. In a cursor {{ROW TYPE OF}} variable is declared inside a loop, its data type will become known on the very first iteration and won't change on further loop iterations.

            h1. Referenced tables existence
            The tables referenced in {{TYPE OF}} and {{ROW TYPE OF}} declarations will be checked for existence at the beginning of the stored routine call. {{CREATE PROCEDURE}} or {{CREATE FUNCTION}} will not check the referenced tables for existence.

            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2017-04-07 10:27:39.0 2017-04-07 10:27:39.863
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.0 [ 22127 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov added a comment -

            Pushed to bb-10.2-ext and 10.3.

            bar Alexander Barkov added a comment - Pushed to bb-10.2-ext and 10.3.
            bar Alexander Barkov made changes -
            Labels Compatibility
            bar Alexander Barkov made changes -
            Summary TYPE OF and ROW TYPE OF anchored data types TYPE OF and ROW TYPE OF anchored data types for stored routine variables
            bar Alexander Barkov made changes -
            Labels Compatibility Compatibility datatype
            bar Alexander Barkov made changes -
            Component/s Parser [ 10201 ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80264 ] MariaDB v4 [ 133201 ]
            bar Alexander Barkov made changes -

            People

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