Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
-
10.1.22
Description
Affects earlier versions as well.
The following creates and replaces a view pointing to different tables within prepared statements inside a cursor. While still in the cursor a SELECT from the replaced view yields results from the table referenced previously, the view doesn't seem to be updated. SHOW CREATE VIEW gives correct results, though.
Test case:
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; |
Attachments
Issue Links
- is blocked by
-
MDEV-5816 MySQL WL#4179 - Stored programs: validation of stored program statements
- Closed