DROP PROCEDURE IF EXISTS test.sp_testcase;

DROP TABLE IF EXISTS test.tmp_list;

DROP TABLE IF EXISTS test.a;

DROP TABLE IF EXISTS test.b;

CREATE TABLE test.tmp_list AS

SELECT 'a' AS col

UNION

SELECT 'b' AS col

;

CREATE TABLE test.a AS

SELECT 'AAA';

CREATE TABLE test.b AS

SELECT 'BBB';

DELIMITER #

CREATE PROCEDURE test.sp_testcase ()

BEGIN

  DECLARE done BOOLEAN DEFAULT FALSE;

  DECLARE var VARCHAR(255);

  DECLARE cur CURSOR FOR

  SELECT col FROM test.tmp_list;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  testLoop: LOOP

    FETCH cur INTO var;

    IF done THEN

      LEAVE testLoop;

    END IF;

    SELECT var;

    SET @stmt = CONCAT('CREATE OR REPLACE VIEW test.v AS SELECT * FROM ', var);

    SELECT @stmt;

    PREPARE _sql FROM @stmt;

    EXECUTE _sql;

    DROP PREPARE _sql;

    -- should return 'AAA' on the first iteration and 'BBB' on the second

    -- returns 'AAA' on both iterations

    SELECT * FROM test.v;

    SHOW CREATE VIEW test.v;

  END LOOP testLoop;

  CLOSE cur;

  -- returns 'BBB' as expected

  SELECT * FROM test.v;

  SHOW CREATE VIEW test.v;

END#

DELIMITER ;

CALL test.sp_testcase();

DROP PROCEDURE test.sp_testcase;

DROP TABLE test.tmp_list;

DROP TABLE test.a;

DROP TABLE test.b;